InnoDB不支持含自增列的复合主键？求原因及替代实现方案

一、为什么InnoDB不支持这种配置 #

MyISAM的自增列逻辑和复合主键的前缀字段绑定——当复合主键前几列（这里是grp_name+sub_grp）相同时，自增列会在该分组内递增；换一组前缀，自增列就从1重新计数。

但InnoDB的自增实现逻辑完全不同：

• InnoDB的自增值是全局唯一递加的，引擎会维护一个全局计数器，不管主键前缀是什么，每次插入都会直接取当前计数器最大值加1，不会按分组重置。
• 另外InnoDB要求自增列必须是索引的最左前缀（如果是主键的一部分，必须放在复合主键的第一个位置），你原来的表把自增列放在复合主键最后，在InnoDB里连建表都通不过，更别说实现分组自增了。

二、InnoDB中实现分组自增的方案 #

1. 批量SELECT插入时用窗口函数计算序号 #

如果是从其他表批量导入数据，推荐用窗口函数ROW_NUMBER()按分组生成序号，这是最高效且安全的方式。

首先创建InnoDB表（item_index改为普通INT，无需自增属性）：

CREATE TABLE zzz_test.`table_with_index` (
  `grp_name` VARCHAR(70) NOT NULL,
  `sub_grp` INT NOT NULL,
  `item_index` INT NOT NULL,
  `item` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`grp_name`,`sub_grp`,`item_index`)
) ENGINE=InnoDB;

假设源表为source_table（包含grp_name、sub_grp、item字段），批量插入SQL如下：

INSERT INTO zzz_test.table_with_index (grp_name, sub_grp, item_index, item)
SELECT
  grp_name,
  sub_grp,
  ROW_NUMBER() OVER (PARTITION BY grp_name, sub_grp ORDER BY item) AS item_index,
  item
FROM source_table;

注意：ORDER BY子句要根据业务需求指定排序规则，确保序号生成的一致性，避免重复或错乱。

2. 单条插入时用触发器自动计算序号 #

如果需要支持应用程序逐条插入数据，可以用触发器自动计算当前分组的最大序号并加1。

创建表的SQL同上，然后创建BEFORE INSERT触发器：

DELIMITER //
CREATE TRIGGER trg_table_with_index_autoinc
BEFORE INSERT ON zzz_test.table_with_index
FOR EACH ROW
BEGIN
  SELECT COALESCE(MAX(item_index), 0) + 1 INTO NEW.item_index
  FROM zzz_test.table_with_index
  WHERE grp_name = NEW.grp_name AND sub_grp = NEW.sub_grp;
END //
DELIMITER ;

这样每次插入数据时，触发器会自动查询当前分组的最大item_index，加1后赋值给新记录的item_index。

注意：高并发场景下触发器可能导致主键冲突（多个事务同时拿到相同的最大值），此时需要加锁或改用批量插入方案。

3. 兼容旧版本MySQL的自定义变量方案 #

如果你的MySQL版本不支持窗口函数（如5.7及以前），可以用自定义变量来实现分组自增：

INSERT INTO zzz_test.table_with_index (grp_name, sub_grp, item_index, item)
SELECT
  grp_name,
  sub_grp,
  @row_num := IF(@current_grp = CONCAT(grp_name, '-', sub_grp), @row_num + 1, 1) AS item_index,
  item,
  @current_grp := CONCAT(grp_name, '-', sub_grp)
FROM source_table, (SELECT @row_num := 0, @current_grp := '') AS vars
ORDER BY grp_name, sub_grp, item;

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