MySQL如何配置表实现variant_id按product_id-自增序号格式自动生成

行业内主流采用的是双字段存储+查询时拼接的方案，不推荐直接存储格式化的variant_id字符串，原因如下：

• 数值类型的主键索引效率更高、关联查询性能更好，没有字符串主键的空间占用高、查询慢的问题
• 完全避免并发场景下格式化ID生成冲突的问题，后续数据维护、关联其他表的成本更低
• 业务需要展示格式化ID时，实时拼接的计算成本几乎可以忽略，灵活性更高

方案1（推荐）：双字段独立存储+查询拼接 #

1. 表结构调整（替换MyISAM为InnoDB） #

你当前使用的MyISAM引擎完全不建议生产环境使用：它不支持事务、只有表级锁、不支持外键约束、崩溃后无法安全恢复数据，并发性能极差，直接替换为InnoDB即可，无兼容成本。
调整后的表结构参考：

CREATE TABLE variants (
  variant_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '全局唯一自增主键',
  product_id INT UNSIGNED NOT NULL COMMENT '关联商品ID',
  variant_seq INT UNSIGNED NOT NULL COMMENT '单商品下变体自增序号',
  variant_name VARCHAR(255) NOT NULL COMMENT '变体名称',
  -- 其他业务字段自行补充
  FOREIGN KEY (product_id) REFERENCES products(product_id),
  UNIQUE KEY uk_product_seq (product_id, variant_seq) -- 保证同商品下序号不重复
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 自动生成单商品变体序号 #

如果业务并发量不高，插入前先查询当前商品的最大序号加1即可；如果并发量较高，用触发器自动生成更稳妥：

DELIMITER //
CREATE TRIGGER trg_variants_before_insert
BEFORE INSERT ON variants
FOR EACH ROW
BEGIN
  SET NEW.variant_seq = (
    SELECT IFNULL(MAX(variant_seq), 0) + 1
    FROM variants
    WHERE product_id = NEW.product_id
  );
END //
DELIMITER ;

3. 查询时拼接格式化ID #

业务需要展示product_id-序号格式的ID时，直接用SQL拼接即可：

SELECT 
  *,
  CONCAT(product_id, '-', variant_seq) AS formatted_variant_id
FROM variants;

查询结果会直接返回你需要的1-1、3-1等格式的ID。

方案2（不推荐）：直接存储格式化ID #

如果业务有强要求必须把product_id-序号的字符串作为主键存储，可以用触发器实现，但是要注意该方案性能远低于方案1，仅适合小流量场景：

DELIMITER //
CREATE TRIGGER trg_variants_before_insert
BEFORE INSERT ON variants
FOR EACH ROW
BEGIN
  DECLARE current_seq INT;
  SELECT IFNULL(MAX(SUBSTRING_INDEX(variant_id, '-', -1)), 0) + 1 INTO current_seq
  FROM variants
  WHERE product_id = NEW.product_id;
  SET NEW.variant_id = CONCAT(NEW.product_id, '-', current_seq);
END //
DELIMITER ;

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