update and的坑

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

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的语法是：

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的语法：

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

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