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.

If you really need this you can achieve your goal with help of separate table for sequencing (if you don't mind) and a trigger.

Tables

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE table1
(
  id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

Now the trigger

DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

Then you just insert rows to table1

INSERT INTO Table1 (name) 
VALUES ('Jhon'), ('Mark');

And you'll have

|      ID | NAME |

| LHPL001 | Jhon | | LHPL002 | Mark |

Here is SQLFiddle demo

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 |
+---------+------+

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.

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;