如何让数据库自动生成唯一的15位VARCHAR类型索引字符串？

Great question! Let's break down how to fix this properly—relying on manual MD5(RAND()) inserts without a safety net is definitely risky for duplicates.

First, let's fix the foundational issue: your current INDEX only speeds up queries, it doesn't enforce uniqueness. To guarantee no duplicate values in the unique field, first convert it to a UNIQUE INDEX so the database blocks duplicate inserts automatically:

-- Optional but recommended: Ensure the field can't be null
ALTER TABLE your_table MODIFY COLUMN `unique` VARCHAR(15) NOT NULL;
-- Add unique index to enforce uniqueness
ALTER TABLE your_table ADD UNIQUE INDEX idx_unique (`unique`);

Note: unique is a MySQL keyword, so you must wrap it in backticks ` to avoid syntax errors.

Now, here are three practical solutions to auto-generate unique alphanumeric strings, tailored to different MySQL versions and business needs:

方案1：Use UUID (Works for MySQL 8.0.19+) #

MySQL 8.0.19+ allows functions as default values. UUIDs have an extremely low duplicate risk, and we can truncate one to fit your 15-character limit:

ALTER TABLE your_table MODIFY COLUMN `unique` VARCHAR(15) NOT NULL DEFAULT LEFT(UUID(), 15);

After this, you don't need to specify the unique field at all when inserting data—the database handles it automatically:

INSERT INTO your_table (other_column1, other_column2) VALUES ('value1', 'value2');

方案2：Custom Function + Trigger (Compatible with Older MySQL Versions) #

If you're on a MySQL version before 8.0.19, you can create a function to generate unique strings and a trigger to auto-apply it:

Step 1: Create a Function to Generate Unique Strings #

This function loops until it finds a string that doesn't already exist in the table:

DELIMITER //
CREATE FUNCTION generate_unique_str() RETURNS VARCHAR(15)
DETERMINISTIC
BEGIN
    DECLARE new_str VARCHAR(15);
    DECLARE str_exists BOOLEAN;
    
    REPEAT
        -- UUID has lower duplicate risk than MD5(RAND()), but you can use either
        SET new_str = LEFT(UUID(), 15);
        -- Check if the string already exists
        SELECT EXISTS(SELECT 1 FROM your_table WHERE `unique` = new_str) INTO str_exists;
    UNTIL NOT str_exists END REPEAT;
    
    RETURN new_str;
END //
DELIMITER ;

Step 2: Create a BEFORE INSERT Trigger #

This trigger auto-fills the unique field if you don't provide a value during insertion:

DELIMITER //
CREATE TRIGGER trigger_auto_set_unique
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    -- Auto-generate if no value is provided for the `unique` field
    IF NEW.`unique` IS NULL OR NEW.`unique` = '' THEN
        SET NEW.`unique` = generate_unique_str();
    END IF;
END //
DELIMITER ;

Now you can insert data without specifying the unique field, just like in方案1.

方案3：Auto-Increment ID to Base62 (100% Unique, Recommended for Strict Use Cases) #

For guaranteed absolute uniqueness, convert an auto-increment ID to a base62 (0-9a-zA-Z) string. This ensures no duplicates and fits your alphanumeric requirement:

Step 1: Add an Auto-Increment Primary Key (If You Don't Have One) #

ALTER TABLE your_table ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

Step 2: Create a Function to Convert ID to Base62 #

DELIMITER //
CREATE FUNCTION id_to_base62(id BIGINT) RETURNS VARCHAR(15)
DETERMINISTIC
BEGIN
    DECLARE chars VARCHAR(62) DEFAULT '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE result_str VARCHAR(15) DEFAULT '';
    DECLARE remainder INT;
    
    WHILE id > 0 DO
        SET remainder = id % 62;
        SET result_str = CONCAT(SUBSTRING(chars, remainder + 1, 1), result_str);
        SET id = FLOOR(id / 62);
    END WHILE;
    
    RETURN result_str;
END //
DELIMITER ;

Step 3: Create an AFTER INSERT Trigger #

Since the auto-increment ID is only assigned after insertion, use an AFTER INSERT trigger to update the unique field:

DELIMITER //
CREATE TRIGGER trigger_id_to_unique
AFTER INSERT ON your_table
FOR EACH ROW
BEGIN
    UPDATE your_table SET `unique` = id_to_base62(NEW.id) WHERE id = NEW.id;
END //
DELIMITER ;

This method guarantees 100% uniqueness, and a 15-character base62 string can represent an enormous range of IDs—way beyond what most businesses will ever need.

Quick Comparison of Solutions #

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