如何为表创建按指定列分组的行内自增触发器？

看来你需要实现分组自增的move_number——也就是每个game_id对应的move_number从1开始独立计数，而不是全局连续递增。你的初始方案用了全局序列，所以没法按game_id重置，我来帮你调整一下：

问题根源 #

全局序列inc_seq是全局唯一递增的，不管插入的game_id是什么，都会持续累加，自然没办法在新的game_id出现时重置计数。我们需要针对每个game_id单独统计当前已有的最大move_number，然后加1作为新插入行的move_number。

解决方案：替换触发器并移除全局序列 #

首先，你可以删除原来的触发器和序列（因为它们已经不符合需求了）：

DROP TRIGGER inc_on_insert;
DROP SEQUENCE inc_seq;

然后创建新的触发器，它会在插入前自动计算当前game_id对应的下一个move_number：

CREATE OR REPLACE TRIGGER trg_moves_move_number
BEFORE INSERT ON moves
FOR EACH ROW
BEGIN
  -- 查询当前game_id下最大的move_number，加1；如果无记录则设为1
  SELECT COALESCE(MAX(move_number), 0) + 1
  INTO :new.move_number
  FROM moves
  WHERE game_id = :new.game_id
  FOR UPDATE; -- 锁定该game_id的行，避免并发插入时出现重复值
END;
/

代码说明 #

• COALESCE(MAX(move_number), 0)：如果当前game_id还没有任何记录，MAX(move_number)会返回NULL，COALESCE会把它替换成0，加1后就是1，完美实现新分组从1开始计数。
• FOR UPDATE：当多个会话同时往同一个game_id插入数据时，这个子句会锁定该game_id的所有行，确保同一时间只有一个会话能计算并插入下一个move_number，避免出现重复值触发唯一约束的报错。

测试示例 #

假设你插入以下数据：

INSERT INTO moves (game_id) VALUES (1);
INSERT INTO moves (game_id) VALUES (1);
INSERT INTO moves (game_id) VALUES (2);
INSERT INTO moves (game_id) VALUES (1);
INSERT INTO moves (game_id) VALUES (3);
INSERT INTO moves (game_id) VALUES (2);

查询moves表会得到：

完全符合你需要的按game_id分组自增的效果。

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