How Can I Prefix Auto-Incrementing Primary Keys in MySQL?

Prefixing Auto-Incrementing Primary Keys in MySQL

As a database administrator, you may encounter a situation where you want to prefix your auto-incrementing primary keys with a specific value. In this guide, we will explore a solution using a separate sequencing table and a trigger.

Creating the Sequencing Table

First, create a table to generate the sequence values:

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

Creating the Main Table

Next, create your main table with the primary key column using the VARCHAR type and specifying a default value of '0':

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

Trigger for Prefixing the Primary Key

To set the prefixed value for the primary key, create a trigger that fires before each insert operation on the main table:

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 into the Main Table

Now, you can insert data into the main table without specifying the primary key value. The trigger will automatically generate and prefix the IDs:

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

Output

Upon inserting data, the table will display the following records with the prefixed primary keys:

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

SQLFiddle Demonstration

For a practical demonstration, you can refer to the SQLFiddle demo here: [link]

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