MySQL 5.6.44中如何按行给my_table的val1列设置不同硬编码值？

在MySQL 5.6.44里，你原来的UPDATE语句不仅语法错误，更关键的是没有把硬编码的列表值和原表的行（通过id字段）关联起来，所以没法实现“id1对应spam、id2对应eggs、id3对应ham”的精准更新。下面给你两种可靠的实现方法：

方法一：显式关联ID与新值（最直观） #

这种方法通过UNION ALL构造包含目标id和对应新值的临时表，再通过JOIN关联原表，确保每一行都能被正确更新：

UPDATE my_table t
JOIN (
    SELECT 1 AS id, 'spam' AS new_val
    UNION ALL
    SELECT 2, 'eggs'
    UNION ALL
    SELECT 3, 'ham'
) temp ON t.id = temp.id
SET t.val1 = temp.new_val;

执行这条语句后，你的my_table就会变成你期望的结构：

方法二：利用变量自动匹配连续ID（更灵活） #

如果你的id是连续自增的，且硬编码列表的顺序和id的顺序完全对应，可以用用户变量来自动生成匹配的id，不需要手动写每个ID值：

-- 初始化行号变量
SET @row_num = 0;

UPDATE my_table t
JOIN (
    SELECT 
        (@row_num := @row_num + 1) AS id,
        val
    FROM (
        SELECT 'spam' AS val UNION ALL
        SELECT 'eggs' UNION ALL
        SELECT 'ham'
    ) vals
) temp ON t.id = temp.id
SET t.val1 = temp.val;

这种方法适合后续需要扩展列表内容的场景，只需要在子查询里添加新的SELECT语句即可。

为什么原语句报错？ #

你原来的语句UPDATE my_table SET val1 = SELECT * FROM (VALUES ('spam'),('eggs'),('ham'))有两个问题：

1. MySQL 5.6对VALUES()构造临时表的支持有限，不能直接这样在UPDATE的赋值语句里使用；
2. 没有任何关联条件，数据库不知道每个值对应哪一行，逻辑上也不成立。

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