Implementing Auto Increment Primary Key with Prefix in MySQL: A Comprehensive Trigger and Sequence Table Solution

Technical Background and Requirement Analysis

In modern database design, there is often a need to generate unique identifiers with specific formats. While traditional auto-increment integer primary keys are simple and efficient, they cannot meet the readability and semantic requirements of identifiers in business systems. For instance, in order management systems, user management systems, or inventory management systems, developers frequently need to generate composite identifiers that combine fixed prefixes with auto-incrementing numbers, such as 'LHPL001', 'LHPL002'.

Core Solution Architecture

Since MySQL does not natively support auto-increment functionality with prefixes on string fields, a combined approach using sequence tables and triggers is required. The core concept of this solution involves separating sequence management from formatting operations, with database triggers automatically handling identifier generation and formatting during insert operations.

Database Table Structure Design

First, create two essential tables: a sequence table and a business data table. The sequence table is dedicated to maintaining auto-increment sequences, while the business table stores actual business data.

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

The sequence table table1_seq contains a standard auto-increment integer primary key that automatically generates the next sequence value with each new record insertion. The business table table1 defines its primary key field as a string type to store the formatted composite identifier.

Trigger Implementation Mechanism

Create a BEFORE INSERT trigger that automatically generates formatted identifiers before data insertion into the business 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 ;

The execution flow of this trigger is as follows: when inserting a new record into the table1 table, first insert an empty record into the sequence table, utilizing MySQL's AUTO_INCREMENT mechanism to generate a new sequence number. Then use the LAST_INSERT_ID() function to retrieve the most recently generated sequence value, and format it into a string of specified length using CONCAT and LPAD functions, concatenating it with the prefix.

Data Operation Examples

After completing the table structure and trigger setup, business data can be inserted directly:

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

After executing the above insert statements, querying the table data will yield:

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

In-depth Technical Analysis

LAST_INSERT_ID() Function Characteristics: This function returns the value of the last AUTO_INCREMENT column, and when used within triggers, it accurately retrieves the sequence value generated by the current insert operation. Since the trigger executes within the same transaction as the insert operation, it ensures atomicity in sequence acquisition.

String Formatting Processing: The LPAD function is used to left-pad numbers to a specified length, ensuring generated identifiers maintain a uniform format. For example, LPAD(1, 3, '0') returns '001', while LPAD(12, 3, '0') returns '012'.

Production Environment Considerations

Concurrent Access Handling: In high-concurrency scenarios, multiple transactions inserting data simultaneously may cause sequence contention. While MySQL's AUTO_INCREMENT mechanism provides good concurrency control by default, it's essential to ensure that sequence table insert operations don't become performance bottlenecks.

Performance Optimization Strategies: Triggers add additional database overhead to each insert operation. For high-frequency insertion scenarios, regular monitoring of database performance is recommended, with consideration given to batch insertion optimization or asynchronous processing solutions when necessary.

Data Integrity Assurance: This solution relies on the correct execution of triggers, requiring robust trigger logic. Particularly after database migration, backup restoration, or other maintenance operations, verification of trigger and sequence table functionality is crucial.

Extended Application Scenarios

This approach is not limited to simple prefix+number combinations but can be extended to support more complex identifier generation rules. For example, it can dynamically generate prefixes based on datetime stamps, business type codes, or adjust the format and length of the numeric portion according to different business rules.

Best Practice Recommendations

In practical project applications, it's recommended to incorporate sequence table and trigger creation logic into database version management to ensure environment consistency. Additionally, appropriate initial values and auto-increment steps should be set for sequence tables to avoid conflicts with existing data. Regular review and optimization of trigger performance will ensure long-term system stability.