更新具有相同值的多列，简短的语法 [英] Update multiple columns with same value, short syntax

问题描述

问题提取自 mysql 使用相同的 now() 更新多个列

第二个问题为什么这个查询不更新列:

Second question why this query doesn't update the columns:

mysql> update table set last_update=last_monitor=now() where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

创建了一个 fiddle 来说明混淆.

Created a fiddle to illustrate the confusion.

create table t1 (
  c1 INT NOT NULL,
  c2 INT NOT NULL
);
insert into t1 values (0,0);

update t1 set c1=c2=1;

select * from t1;
| c1 | c2 |
|----|----|
|  0 |  0 |

我希望将 c1 和 c2 设置为 1 或查询因语法错误而失败.

I am expecting c1 and c2 to be set to 1 or the query to fail due to syntax error.

实际结果是查询成功，没有更新列，所以c1或c2保持在0的初始值

Actual result is that the query is succeeding without updating the columns, so c1 or c2 remain at initial value of 0

由于目前的行为对我来说没有意义，我肯定错过了一些东西.有人可以分享一些有关 MySQL 引擎(或任何其他 SQL 引擎)如何评估此表达式的信息吗?

As the current behavior does not makes sense to me, I'm definitely missing something. Can someone share some light on how this expression gets evaluated by MySQL engine (or any other SQL engine) ?

推荐答案

我希望 c1 和 c2 设置为 1 或查询失败语法错误.

I am expecting c1 and c2 to be set to 1 or the query to fail due to syntax error.

两者都不是.
表达式:

Neither is true.
The expression:

c1=c2=1

被评估为:

c1=(c2=1)

对于 MySql，布尔表达式 c2=1 对 false 或 求值为 0 或 1分别为真.
所以 0 或 1 将被分配给 c1 并且在这种情况下因为 c2=1 是 false 结果将是 0.
您必须对每一列使用单独的分配:

For MySql the boolean expression c2=1 evaluates to 0 or 1 for false or true respectively.
So 0 or 1 will be assigned to c1 and in this case since c2=1 is false the result will be 0.
You must use separate assignments to each column:

update t1 
set 
  c1=1,
  c2=1;

这篇关于更新具有相同值的多列，简短的语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！