update and的坑

开发那边抛出个有意思的问题，下面的现象如何解释呢？

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

mysql> select * from A; +------+------+ | t1   | t2   | +------+------+ |    1 |    1 | |    2 |    2 | |    3 |    3 | +------+------+ 3 rows in set (0.00 sec) mysql> update A set t1=0 and t2=5; Query OK, 3 rows affected (0.00 sec) Rows matched: 3  Changed: 3  Warnings: 0   mysql> select * from A;           +------+------+ | t1   | t2   | +------+------+ |    0 |    1 | |    0 |    2 | |    0 |    3 | +------+------+ 3 rows in set (0.00 sec) update  语句 and 怎么解释？

　　

Update的语法是：

?

1 2 3 4 5 6 7 8 9 10 11

UPDATE [LOW_PRIORITY] [IGNORE] table_reference     SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...     [WHERE where_condition]     [ORDER BY ...]     [LIMIT row_count]   Multiple-table syntax:   UPDATE [LOW_PRIORITY] [IGNORE] table_references     SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...     [WHERE where_condition]

　　

select的语法：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

Name: 'SELECT' Description: Syntax: SELECT     [ALL | DISTINCT | DISTINCTROW ]       [HIGH_PRIORITY]       [STRAIGHT_JOIN]       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]     select_expr [, select_expr ...]     [FROM table_references       [PARTITION partition_list]     [WHERE where_condition]     [GROUP BY {col_name | expr | position}       [ASC | DESC], ... [WITH ROLLUP]]     [HAVING where_condition]     [ORDER BY {col_name | expr | position}       [ASC | DESC], ...]     [LIMIT {[offset,] row_count | row_count OFFSET offset}]     [PROCEDURE procedure_name(argument_list)]     [INTO OUTFILE 'file_name'         [CHARACTER SET charset_name]         export_options       | INTO DUMPFILE 'file_name'       | INTO var_name [, var_name]]     [FOR UPDATE | LOCK IN SHARE MODE]]

　　

update语法是set后面以逗号区分

mysql> select * from test where t1=0 and t2=5;
Empty set (0.00 sec)

 

使用and被解析成 t1=0 and t2=5 把后面作为一个整体，值变成0了

变成这样了，update A set t1=(0 and t2=5)，t2==5 and 0 

 

换种写法更加明白了

 

【参考资料】

1、http://s.petrunia.net/blog/?p=85

先记录下，后面整体再整理！！！