MySQL实现按服务分类的自定义字母数字自增主键方案咨询

要实现同服务分类下独立自增的自定义ID，需要借助辅助表维护分类计数 + 触发器自动生成ID的组合方案，具体步骤如下：

1. 创建辅助表（维护分类自增计数） #

这个表用来记录每个服务分类的首字母，以及该分类当前已使用的最大序号，确保同分类下序号独立递增：

CREATE TABLE service_seq (
    service_prefix CHAR(1) PRIMARY KEY,
    current_seq INT NOT NULL DEFAULT 10000 -- 初始值设为10000，首次自增后即为10001
);

2. 创建主用户表 #

主表包含服务字段和自定义ID字段，其中ID作为主键：

CREATE TABLE user_table (
    custom_id CHAR(6) PRIMARY KEY,
    service_name VARCHAR(50) NOT NULL
);

3. 编写插入触发器 #

通过触发器在插入主表数据时自动完成ID生成逻辑：

• 提取服务名称的首字母并转为大写
• 从辅助表获取该首字母对应的当前序号，若不存在则初始化
• 序号自增后拼接首字母生成6位ID
• 更新辅助表的序号，同时将生成的ID插入主表

DELIMITER //

CREATE TRIGGER generate_custom_id BEFORE INSERT ON user_table
FOR EACH ROW
BEGIN
    DECLARE prefix CHAR(1);
    DECLARE next_seq INT;
    
    -- 提取服务名称首字母并转为大写
    SET prefix = UPPER(LEFT(NEW.service_name, 1));
    
    -- 若该分类未在辅助表中，初始化其序号
    INSERT INTO service_seq (service_prefix, current_seq)
    VALUES (prefix, 10000)
    ON DUPLICATE KEY UPDATE current_seq = current_seq;
    
    -- 获取自增后的序号
    SELECT current_seq + 1 INTO next_seq FROM service_seq WHERE service_prefix = prefix;
    
    -- 更新辅助表的当前序号
    UPDATE service_seq SET current_seq = next_seq WHERE service_prefix = prefix;
    
    -- 生成并赋值自定义ID
    SET NEW.custom_id = CONCAT(prefix, next_seq);
END //

DELIMITER ;

4. 测试插入效果 #

插入测试数据验证逻辑：

-- 首次插入Audi，生成A10001
INSERT INTO user_table (service_name) VALUES ('Audi');
-- 再次插入Audi，生成A10002
INSERT INTO user_table (service_name) VALUES ('Audi');
-- 首次插入Lamborghini，生成L10001
INSERT INTO user_table (service_name) VALUES ('Lamborghini');

查询主表结果：

SELECT * FROM user_table;

输出示例：

注意事项 #

• 若服务名称为空或首字母非英文字母，需在触发器中添加校验逻辑（比如抛出错误），避免生成无效ID
• 需限制next_seq不超过99999，可在触发器中添加判断：若next_seq > 99999则抛出错误，防止ID超出6位长度
• 高并发场景下可能出现序号冲突，可将相关操作包裹在事务中，或使用更原子化的语句优化逻辑

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