How to Automatically Generate Custom Primary Keys with a Prefix in MySQL?

Custom Primary Keys with Prefix Auto-Incrementation

In database design, it's common to require custom primary keys that follow a specific format, such as in this case where the ID field needs to increment in the form 'LHPL001','LHPL002','LHPL003'.

Solution 1: Trigger and Sequence Table

This approach utilizes a separate table to generate sequences and a trigger to assign these sequences to new records.

Implementation:

1. Create Sequence Table:

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

2. Create Main Table:

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

3. Create 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, new rows inserted into the table1 will automatically generate a sequence number prefixed with 'LHPL' in the id field.

Example:

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

Result:

ID	NAME
LHPL001	Jhon
LHPL002	Mark

The above is the detailed content of How to Automatically Generate Custom Primary Keys with a Prefix in MySQL?. For more information, please follow other related articles on the PHP Chinese website!