How to Create Auto-Incrementing Primary Keys with Prefixes in MySQL?

Creating Auto-Incremented Primary Keys with Prefixes in MySQL

When working with databases, it's often desirable to have a primary key that not only provides a unique identifier but also incorporates a meaningful prefix for easy organization. If you have a table with a primary key column named 'id' and wish to increment it with a prefix like 'LHPL001', 'LHPL002', and so on, here's a potential solution using a separate table for sequencing and a trigger:

Table Setup

Create a table called table1_seq to store the sequence numbers and a table called table1 to store the actual data, with the desired primary key field:

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

Trigger for Prefixed Auto-Increment

To generate the prefixed primary keys, create a BEFORE INSERT trigger on table1:

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 ;

Inserting Data

Once the trigger is in place, you can simply insert rows into table1:

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

Result

The data in table1 will now have prefixed primary keys:

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

This approach allows you to achieve the desired auto-incrementing primary key behavior with a meaningful prefix, simplifying data organization and retrieval.

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