MySQL UPDATE自增语句执行异常：未实现预期递增效果求助

这问题我之前做业务时也踩过坑！咱们先理清楚你遇到的问题根源，再给你靠谱的解决方案：

问题出在哪？ #

你写的这条语句：

UPDATE AddItem SET IUID=IUID+1 WHERE UID=1

它的作用是一次性把所有UID=1的行的IUID字段都在当前值基础上加1，而不是你期望的“给新插入的行分配递增的序号”。

你看到结果都是1，大概率是每次插入新行时IUID的默认值是0，然后你立刻执行这条UPDATE——这时候新行的0变成1，但如果之前已经有UID=1的行，它们的IUID也会跟着加1，导致序号混乱。如果是每次插入后都执行，那旧行的序号会不断变大，新行永远是1，这显然不是你想要的。

正确实现分组自增的方案 #

如果你要实现同一个UID下，IUID从1开始依次递增（比如UID=1的行IUID是1、2、3；UID=2的行是1、2...），有两种常用的靠谱方法：

方法1：插入时直接计算最大值+1 #

在插入新行的时候，直接查询当前UID对应的最大IUID，然后加1作为新行的IUID：

INSERT INTO AddItem (UID, IUID, 其他字段)
VALUES (1, (SELECT IFNULL(MAX(IUID), 0) + 1 FROM AddItem WHERE UID=1), '其他值');

• IFNULL是为了处理该UID下还没有任何行的情况（此时MAX(IUID)是NULL，加1会变成NULL，用IFNULL转成0再+1，得到1）。

方法2：使用触发器自动维护 #

如果不想每次插入都写子查询，可以创建一个触发器，在插入新行时自动计算并设置IUID：

DELIMITER //
CREATE TRIGGER trg_AddItem_IUID_AutoIncrement
BEFORE INSERT ON AddItem
FOR EACH ROW
BEGIN
    SET NEW.IUID = (SELECT IFNULL(MAX(IUID), 0) + 1 FROM AddItem WHERE UID = NEW.UID);
END //
DELIMITER ;

之后你插入数据时只需要写：

INSERT INTO AddItem (UID, 其他字段) VALUES (1, '其他值');

触发器会自动帮你把IUID设为该UID下的递增序号。

额外注意事项 #

• 如果你的并发量很高，上面两种方法可能会出现竞态问题（比如两个请求同时插入同一UID的行，导致IUID重复）。这时候可以结合事务+锁来解决，比如用SELECT ... FOR UPDATE锁定查询的行：

START TRANSACTION;
SELECT IFNULL(MAX(IUID), 0) + 1 INTO @new_iuid FROM AddItem WHERE UID=1 FOR UPDATE;
INSERT INTO AddItem (UID, IUID) VALUES (1, @new_iuid);
COMMIT;

• 如果你已经有历史数据需要修正，可以用这条语句批量更新同一个UID下的IUID为递增序列：

UPDATE AddItem ai
JOIN (
    SELECT 
        id, -- 假设你的表有主键id
        UID,
        ROW_NUMBER() OVER (PARTITION BY UID ORDER BY 插入时间字段) AS new_iuid
    FROM AddItem
) temp ON ai.id = temp.id
SET ai.IUID = temp.new_iuid;

把上面的插入时间字段换成你表中记录插入顺序的字段（比如create_time），这样就能把每个UID下的行按插入顺序重新分配1、2、3...的IUID。

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