执行语句一 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)
