咨询MySQL表中自动生成唯一group列新值的高效方法

1. 自动取最大值+1的SQL更新语句 #

直接用一条SQL完成更新，无需手动查询最大值，适合需要延续现有编号序列的场景：

UPDATE mytable
SET `group` = (SELECT COALESCE(MAX(`group`), 0) + 1 FROM mytable)
WHERE name = 'name4';

注：COALESCE是为了处理表中所有group都为NULL的极端情况，此时MAX会返回NULL，COALESCE将其转为0，加1后得到初始编号1，避免语法错误。

2. 重用空缺的group编号（可选） #

如果允许优先使用中间缺失的编号（比如之前删除过group=2的记录，想先用2而非直接取最大值+1），可以用以下SQL找到最小的未被使用的正整数：

UPDATE mytable
SET `group` = (
    SELECT MIN(t1.group + 1)
    FROM mytable t1
    LEFT JOIN mytable t2 ON t1.group + 1 = t2.group
    WHERE t2.group IS NULL
    UNION
    SELECT 1
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE `group` = 1)
    LIMIT 1
)
WHERE name = 'name4';

逻辑：先查找当前序列中第一个空缺的编号；如果1到最大值的编号都存在，就返回最大值+1；如果连1都不存在，直接返回1。

3. MySQL Workbench可视化操作 #

如果习惯用界面操作：

• 打开Workbench并连接目标数据库，找到mytable，右键选择Edit Table Data
• 在数据编辑界面定位到name为name4的行，点击group列的单元格
• 输入(SELECT COALESCE(MAX(group),0)+1 FROM mytable)，点击界面上方的Apply按钮，Workbench会自动计算并更新字段

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