How Can I Implement Auto-Incrementing Primary Keys with Prefixes in MySQL?

Auto-Increment Primary Keys with Prefixes in MySQL

When working with MySQL tables, it is sometimes desirable to have a primary key that automatically increments while also incorporating a specific prefix. This can be particularly useful for organizing and tracking data.

Achieving Auto-Increment with Prefix

To create a primary key with both auto-increment functionality and a prefix, follow these steps:

1. Create a Sequence Table

Create a separate table that will serve as a sequence generator:

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

2. Create the Main Table

Create the main table where you want the primary key with a prefix:

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

3. Create the Trigger

Use a trigger to automatically generate the prefixed primary key value upon insertion into the main table:

DELIMITER $$
CREATE TRIGGER tg_my_table_insert
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  INSERT INTO my_sequence VALUES (NULL);
  SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

4. Inserting Rows

Now, you can insert rows into the main table and observe the auto-incremented primary keys with the prefix:

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

Example Result:

| ID | NAME |

| LHPL001 | Jhon |
| LHPL002 | Mark |

Note: This approach involves using a separate sequence table and a trigger, which may not be the most efficient solution for high-throughput scenarios. However, it is a straightforward method for achieving auto-increment primary keys with prefixes in MySQL.

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