How to Create an Auto-Incrementing Primary Key with a Prefix in MySQL?

Auto-Incrementing Primary Key with Prefix in MySQL

In need of an auto-incrementing primary key with a specified prefix, such as 'LHPL001', 'LHPL002', and so on? Here's a concise guide to achieve this in MySQL.

One approach involves utilizing a separate sequencing table and a trigger to generate the desired prefix. Below are the steps:

1. Create the sequencing table:

   CREATE TABLE table1_seq
   (
     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
   );

2. Create the target table with a plain VARCHAR primary key:

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

3. Create 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 ;

With this setup, you can now insert rows into table1, and the trigger will automatically generate the prefixed primary key:

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

The result in the table1 table will be:

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

Refer to the provided SQLFiddle demo for an interactive demonstration of this approach.

The above is the detailed content of How to Create an Auto-Incrementing Primary Key with a Prefix in MySQL?. For more information, please follow other related articles on the PHP Chinese website!