如何在MySQL中创建形如't00000000'或't0000000'的自增ID？

Absolutely, you can create that style of auto-incrementing ID in MySQL—here are two reliable methods to achieve this, depending on your MySQL version:

This is the cleanest and most efficient approach, since it leverages MySQL's built-in auto-increment functionality to avoid concurrency issues.

1. Create your table with a standard auto-increment integer as the primary key, then add a generated column that formats the ID with the 't' prefix and leading zeros:

CREATE TABLE your_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    custom_id VARCHAR(20) GENERATED ALWAYS AS (CONCAT('t', LPAD(id, 8, '0'))) STORED,
    -- Add your other columns here (e.g., name, created_at)
);

2. How it works:
   • The id column handles the auto-increment logic reliably (atomic, no race conditions).
   • custom_id is automatically computed: LPAD(id, 8, '0') pads the integer ID with leading zeros to make it 8 digits long, then CONCAT('t', ...) adds the prefix.
   • When inserting rows, you don't need to specify custom_id at all—MySQL generates it for you:

     INSERT INTO your_table (name) VALUES ('Example');
     -- This will create a row where id=1 and custom_id='t00000001'
     

If you're stuck on an older MySQL version that doesn't support generated columns, a trigger can handle the ID formatting. Note that this has more edge cases (like concurrency risks) compared to the first method.

1. Create a table with the custom ID column as the primary key:

CREATE TABLE your_table (
    custom_id VARCHAR(20) PRIMARY KEY,
    -- Add your other columns here
);

2. Create a trigger that runs before inserting a row to generate the formatted ID:

DELIMITER //
CREATE TRIGGER before_insert_your_table
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    DECLARE next_id INT;
    -- Get the highest existing numeric part of custom_id, default to 0 if table is empty
    SELECT COALESCE(MAX(CAST(SUBSTRING(custom_id, 2) AS UNSIGNED)), 0) + 1 INTO next_id FROM your_table;
    -- Format the new ID with prefix and leading zeros
    SET NEW.custom_id = CONCAT('t', LPAD(next_id, 8, '0'));
END //
DELIMITER ;

• Adjust leading zero count: Change the 8 in LPAD(id, 8, '0') to any number you need. For example, LPAD(id, 7, '0') will generate IDs like t0000001.
• Concurrency consideration: The trigger method isn't atomic for high-traffic systems—two concurrent inserts might try to use the same next_id and cause a duplicate key error. The generated column method avoids this entirely.
• Primary key choice: If you need custom_id to be the primary key, you can still use the generated column method (just set custom_id as the primary key instead of id, though keeping id as an auto-increment PK is still recommended for performance).

内容的提问来源于stack exchange，提问作者thomas walker