MySQL SET子句中用AND替代WHERE是否符合语法规范？

先直接点出核心：MySQL确实允许你在SET子句里写table1.foo = 2 and table1.bar in (1,2,3)——但它的理解和你想的完全不一样。你以为这里的AND是用来过滤要更新的行（类似WHERE的作用），但实际上，MySQL把整个2 and table1.bar in (1,2,3)当成了一个布尔计算表达式，然后把计算结果赋值给table1.foo。

在MySQL里，布尔值会被转换成整数：true对应1，false对应0。所以这条语句的实际效果是：

• 对于table1.bar in (1,2,3)为真的行，2 AND 1的结果是1，于是table1.foo被设为1
• 对于table1.bar not in (1,2,3)的行，2 AND 0的结果是0，于是table1.foo被设为0

这完全不是你想要的「把符合条件的行的foo设为2」的逻辑！

为什么之前没出问题？ #

这纯粹是巧合：可能之前你的数据里，table1.bar大多落在(1,2,3)范围内，所以foo被设为1，刚好这个值没有违反你的键约束（比如外键关联的表有1这个值，或者foo的约束允许1存在）。但这次遇到了table1.bar不在范围内的行，foo被设为0，而0违反了键约束（比如外键表没有0这个值，或者foo有非零约束），于是错误就爆发了。

正确的写法 #

你需要把过滤条件放到WHERE子句里，让它真正起到筛选要更新的行的作用：

update table1 join table2 on table1.id = table2.id 
set table1.foo = 2 
where table1.bar in (1,2,3)

如果是要更新多个字段，SET子句里应该用逗号分隔不同的赋值操作，比如：

update table1 join table2 on table1.id = table2.id 
set table1.foo = 2, table1.baz = 'abc'
where table1.bar in (1,2,3)

总结 #

• 语法上MySQL允许这种写法，但逻辑完全偏离你的预期
• 键约束错误是因为意外给foo赋了0/1，而不是你想要的2
• 之前没报错只是数据巧合，不是写法正确

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