How to automatically increment the primary key of a MySQL table with some prefix

Question

I have a table like this

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

I want to increase my id field, for example 'LHPL001','LHPL002','LHPL003' ... etc. What should I do for this? Please let me know any way possible.

Answer 1

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

Table

 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 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 into table1

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

And you will have

 |  ID |  NAME |
 ------------------
 |  LHPL001 |  Jhon |
 |  LHPL002 |  Mark |

Here is the SQLFiddle demo

Answer 2

Create a table with the usual numerical identifier auto_increment, but either define it using ZEROFILL or use LPAD to add zeros when you select. Then CONCAT values ​​to get your intended behavior. Example:

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

Answer 3

I know it's late, but I just want to share what I did for this. I am not allowed to add another table or trigger, so I need to generate it in one query when pasting. In your case, you can try this query.

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

Make a selection and use this selection query and save the @IDNUMBER parameter

 (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 the Insert query will look like this:

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

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