关于mysql多个字段update时错误使用and连接字段的问题

执行语句一

update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989;

结果为只将 book_id 字段值更新为 0, 其他字段都没有更改

mysql> select id,book_id,unit_id,article_id from spoken;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       5 |      55 |      55555 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

mysql> update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,book_id,unit_id,article_id from spoken;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       0 |      55 |      55555 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

 

执行语句二

update spoken set book_id = 2,unit_id = 14,article_id = 47409 where id = 284989; （正常语句）

三个字段值都变更为给定值，

mysql> select id,book_id,unit_id,article_id from spoken;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       0 |      55 |      55555 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

mysql> update spoken set book_id = 2,unit_id = 14,article_id = 47409 where id = 284989;          

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,book_id,unit_id,article_id from spoken;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       2 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

 

执行语句三

update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989;

只将第一个字段变更为 1

mysql> select id,book_id,unit_id,article_id from spoken;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       2 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

mysql> update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989;   

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,book_id,unit_id,article_id from spoken;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       1 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

 

分析，

1 、 正常的 update 语法为语句二，更新多个字段的值，多个字段之间使用逗号“，”分隔。

2 、但问题语句一和问题语句三更新多个字段的值使用 and ，分隔多个字段；

且语句一将 book_id 变更为 ，语句三将 book_id 变更为 1 ；

 

一、问题语句一

update spoken set book_id = 2 and unit_id = 14 and article_id = 47409 where id = 284989;

等价于

update spoken set book_id = ( 2 and unit_id = 14 and article_id = 47409 )  where id = 284989;

等价于

update spoken set book_id = ( 2 and ( unit_id = 14 )  and ( article_id = 47409 ))  where id = 284989;

 

相当于将 book_id 的值更新为下面语句的值

select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989 ;

该语句由三个表达式通过 mysql 的逻辑运算符 and 连接

表达式一为 :  2

表达式二为： unit_id = 14 （ select unit_id = 14 from spoken where id = 284989 ; ）

表达式三为： article_id = 47409 （ select article_id = 47409 from spoken where id = 284989 ; ）

 

由于当时 unit_id = 55,article_id=55555

表达一的值为 2

表达式二值为0

表达式三的值为0

所以 select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989 ;

的值为 2 and 0 and 0 即为 。

即执行语句的结果等价于 update spoken set book_id = 0 where id = 284989;

 

Mysql 的逻辑运算

http://www.cnblogs.com/pzk7788/p/6891299.html

逻辑与 ( AND 或 && )

(1) 当所有操作数均为非零值、并且不为 NULL 时，所得值为 1
(2) 当一个或多个操作数为 0 时，所得值为 0 
(3) 其余情况所得值为 NULL

mysql> SELECT 1 AND -1, 1 && 0, 0 AND NULL, 1 && NULL ;
+----------+--------+------------+-----------+
| 1 AND -1   | 1 && 0   | 0 AND NULL | 1 && NULL |
+----------+--------+------------+-----------+
| 1           | 0         | 0            | NULL      |
+----------+--------+------------+-----------+

二、同理可得语句三

2 and unit_id = 14 and article_id = 47409

相当于将 book_id 的值更新为下面语句的值

select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989 ;

该语句由三个表达式通过 mysql 的逻辑运算符 and 连接

表达式一为 :  2

表达式二为： unit_id = 14 （ select unit_id = 14 from spoken where id = 284989 ; ）

表达式三为： article_id = 47409 （ select article_id = 47409 from spoken where id = 284989 ; ）

由于当时 unit_id = 14,article_id=47409

表达一的值为 2

表达式二值为 1

表达式三的值为 1

所以 select 2 and (unit_id = 14) and (article_id = 47409) from spoken where id = 284989 ;

的值为 2 and 1 and 1 即为 1 。

即执行语句的结果等价于 update spoken set book_id = 1 where id = 284989;

 

额外的问题：

Mysql 如果对 mysql 的数值型如 int 做匹配时， unit_id 字段和 14 做匹配时

如下三个语句都匹配到结果

select id,book_id,unit_id,article_id from spoken where unit_id=14;

select id,book_id,unit_id,article_id from spoken where unit_id='14';

select id,book_id,unit_id,article_id from spoken where unit_id='14aaa';

（ 字符串转数值会截取第一个非数字前面的数字 ）

mysql>  select id,book_id,unit_id,article_id from spoken where unit_id=14;

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       0 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

mysql> select id,book_id,unit_id,article_id from spoken where unit_id='14';

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       0 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set (0.00 sec)

mysql> select id,book_id,unit_id,article_id from spoken where unit_id='14aaa';

+--------+---------+---------+------------+

| id     | book_id | unit_id | article_id |

+--------+---------+---------+------------+

| 284989 |       0 |      14 |      47409 |

+--------+---------+---------+------------+

1 row in set, 1 warning (0.00 sec)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31535470/viewspace-2168563/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31535470/viewspace-2168563/