[mysql] How to make MySQL table primary key auto increment with some prefix

I have table like this

table
id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name CHAR(30) NOT NULL,

I want to increment my id field like 'LHPL001','LHPL002','LHPL003'... etc. What should I have to do for that? Please let me know any possible way.

Create a table with a normal numeric auto_increment ID, but either define it with ZEROFILL, or use LPAD to add zeroes when selecting. Then CONCAT the values to get your intended behavior. Example #1:

create table so (
 id int(3) unsigned zerofill not null auto_increment primary key,
 name varchar(30) not null
);

insert into so set name = 'John';
insert into so set name = 'Mark';

select concat('LHPL', id) as id, name from so;
+---------+------+
| id      | name |
+---------+------+
| LHPL001 | John |
| LHPL002 | Mark |
+---------+------+

Example #2:

create table so (
 id int unsigned not null auto_increment primary key,
 name varchar(30) not null
);

insert into so set name = 'John';
insert into so set name = 'Mark';

select concat('LHPL', LPAD(id, 3, 0)) as id, name from so;
+---------+------+
| id      | name |
+---------+------+
| LHPL001 | John |
| LHPL002 | Mark |
+---------+------+

Here is PostgreSQL example without trigger if someone need it on PostgreSQL:

CREATE SEQUENCE messages_seq;

 CREATE TABLE IF NOT EXISTS messages (
    id CHAR(20) NOT NULL DEFAULT ('message_' || nextval('messages_seq')),
    name CHAR(30) NOT NULL,
);

ALTER SEQUENCE messages_seq OWNED BY messages.id;

I know it is late but I just want to share on what I have done for this. I'm not allowed to add another table or trigger so I need to generate it in a single query upon insert. For your case, can you try this query.

CREATE TABLE YOURTABLE(
IDNUMBER VARCHAR(7) NOT NULL PRIMARY KEY,
ENAME VARCHAR(30) not null
);

Perform a select and use this select query and save to the parameter @IDNUMBER

(SELECT IFNULL
     (CONCAT('LHPL',LPAD(
       (SUBSTRING_INDEX
        (MAX(`IDNUMBER`), 'LHPL',-1) + 1), 5, '0')), 'LHPL001')
    AS 'IDNUMBER' FROM YOURTABLE ORDER BY `IDNUMBER` ASC)

And then Insert query will be :

INSERT INTO YOURTABLE(IDNUMBER, ENAME) VALUES 
(@IDNUMBER, 'EMPLOYEE NAME');

The result will be the same as the other answer but the difference is, you will not need to create another table or trigger. I hope that I can help someone that have a same case as mine.