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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to auto-increment