MySQL如何给已有表新增的空列批量按行赋值无需逐行写where条件

解决方案 #

你用INSERT语句无法实现需求是正常的：INSERT的作用是新增表行，要修改已有行的字段值需要使用UPDATE语句。

MySQL可以配合用户变量+ELT函数实现按指定行顺序批量赋值，无需逐行写WHERE条件，示例代码如下：

-- 请先替换以下变量为你的实际值：
-- your_table: 实际表名
-- id: 你用来确定行顺序的排序字段，例如主键、创建时间，确保排序后行顺序符合你定义的第一到第五行
-- new_column: 你新增的待赋值列名
UPDATE your_table t
JOIN (
    SELECT 
        id,
        ELT(@row_num := @row_num + 1, 2, 3, 1, 2, 0) AS assign_val
    FROM your_table, (SELECT @row_num := 0) AS init
    ORDER BY id ASC
) AS tmp ON t.id = tmp.id
SET t.new_column = tmp.assign_val;

注意事项 #

• 执行更新前建议先执行下面的SELECT语句验证排序规则和赋值结果是否符合预期，避免误改数据：

SELECT 
    id,
    ELT(@row_num := @row_num + 1, 2, 3, 1, 2, 0) AS assign_val
FROM your_table, (SELECT @row_num := 0) AS init
ORDER BY id ASC;

• 如果你的表行数超过5行，超出部分的新列值会保持NULL，如需循环赋值可以将ELT部分替换为模运算逻辑即可。
• 表名、列名如果包含.等特殊字符，需要用反引号`包裹，例如`table.name`、`column.name`。

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