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

Question

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.

Answer 1

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