UPDATE 查询在没有 WHERE 子句的情况下更改了行，但有 AND 子句 - 为什么？
我运行了以下查询，它应该有一个 where 子句，但我忘记添加它：
UPDATE
tblFormElementInstances as fei
JOIN
tblFormElements as fe using(intFormElementId)
SET
fei.intStep = 1
AND
fei.intDivisionId = 1 OR fei.intDivisionId IS NULL);
MySQL 返回以下消息：
--查询正常，受影响的 42 行（0.06 秒）
--匹配的行数：94 更改：42 警告：0
我本以为它会抛出语法错误，但事实并非如此。此外，该表中有 96 行，具有不同的 intDivisionId（即不仅仅是 1 或 NULL），这表明 MySQL 完成了一些过滤（匹配的行 = 94）。
另外，intStep 实际上被更改为 0，而不是 1。
有谁知道：
1) 为什么这个查询有效？
2) 为什么将 intStep 改为 0 而不是 1？
3）为什么它没有匹配所有96个？
（计数更改为 42 是因为某些行已经具有 intStep = 1。）
I ran the following query, which should have had a where clause but I forgot to add it:
UPDATE
tblFormElementInstances as fei
JOIN
tblFormElements as fe using(intFormElementId)
SET
fei.intStep = 1
AND
fei.intDivisionId = 1 OR fei.intDivisionId IS NULL);
MySQL returned the following message:
--Query OK, 42 rows affected (0.06 sec)
--Rows matched: 94 Changed: 42 Warnings: 0
I would have expected it to throw a syntax error, but it didn't. Additionally there are 96 rows in that table, with differing intDivisionIds (i.e. not just 1 or NULL), which suggests that some filtering was done by MySQL (rows matched = 94).
Also, intStep was actually changed to 0, not 1.
Does anyone know:
1) Why this query worked at all?
2) Why it changed intStep to 0 and not 1?
3) Why it didn't match all 96?
(The changed count of 42 is because some rows already had intStep = 1.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
它的工作没有语法错误，因为
1 AND是一个有效的表达式。您将 intStep 设置为这个表达式（我添加了括号来显示优先级）：
这是一个布尔表达式，要么是 0 要么 1，因此它将某些行更改为 0，某些行更改为 1。如果 intDivisionId 不为 1，它会更改为 0 ，并且如果 intDivisionId 不为 null。
我猜测 tblFormElementInstances 中有 96 行，但其中只有 94 行在 tblFormElements 中有匹配行。 JOIN 意味着只有匹配的行才有资格进行更新。
尝试这个查询来测试这个理论，我打赌它会返回 94：
@Jason McCreary 很好地观察到您在示例末尾有一个不平衡的括号。这应该会导致语法错误。既然你说你没有收到语法错误，我假设你的示例中错误地包含了括号。
It works without a syntax error because
1 AND <expr>is a valid expression.You set intStep to this expression (I have added parentheses to show precedence):
That's a boolean expression that is either 0 or 1, and so it changes some rows to 0 and some rows to 1. It changes to 0 if intDivisionId is not 1, and if intDivisionId is not null.
I would guess you have 96 rows in tblFormElementInstances, but only 94 of these rows have a matching row in tblFormElements. The JOIN means only matching rows are eligible for the UPDATE.
Try this query to test this theory, I bet it'll return 94:
@Jason McCreary makes a good observation that you have an imbalanced parenthesis at the end of your example. That should result in a syntax error. Since you say you didn't get a syntax error, I assume that parenthesis is included in your example by mistake.
)应该会导致语法错误。也许MySQL没有那么严格。1 AND fei.intDivisionId = 1 OR fei.intDivisionId IS NULL的结果是0。正如您自己所指出的，第一个AND应该是WHEREJOIN与 中的 2 行不匹配>其他表。)should have resulted in a syntax error. Maybe MySQL is not that strict.1 AND fei.intDivisionId = 1 OR fei.intDivisionId IS NULLwas0. As you noted yourself, that firstANDshould have been aWHEREJOINdidn't match 2 rows out of the other table.