mysql update 问题_MySQL update 逻辑问题，你注意了？

一.mysql 5.6

mysql> select version();+------------+| version()  |+------------+| 5.6.16-log |

1.无主键

mysql> create table t02(a int,b int)engine=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t02 values(1,1),(0,1);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t02;

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

| a    | b    |

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

|    1 |    1 |

|    0 |    1 |

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

2 rows in set (0.00 sec)

mysql> update t02 set a=1 and b=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from t02;

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

| a    | b    |

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

|    0 |    1 |

|    0 |    1 |

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

2 rows in set (0.00 sec)

mysql> create table t03(id int,a int,b int,primary key(id))engine=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t03 values(1,1,1),(3,0,1);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t03;

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

| id | a    | b    |

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

|  1 |    1 |    1 |

|  3 |    0 |    1 |

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

2 rows in set (0.00 sec)

mysql> update t03 set a=1 and b=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from t03;

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

| id | a    | b    |

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

|  1 |    0 |    1 |

|  3 |    0 |    1 |

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

2 rows in set (0.00 sec)

2.有主键

mysql> insert into t02 values(1,1),(0,1);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t02;

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

| a    | b    |

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

|    1 |    1 |

|    0 |    1 |

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

2 rows in set (0.00 sec)

二. mysql 5.5

mysql> select version();

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

| version()  |

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

| 5.5.36-log |

1.无主键

mysql> create table t02(a int,b int)engine=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t02 values(1,1),(0,1),(3,2);

mysql> update t02 set a=1 and b=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from t02;

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

| a    | b    |

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

|    0 |    1 |

|    0 |    1 |

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

2 rows in set (0.00 sec)

mysql> truncate table t02 ;

Query OK, 0 rows affected (0.00 sec)

mysql>insert into t02 values(1,1),(0,1),(3,2);

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> update t02 set a=1 and b=2;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from t02;

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

| a    | b    |

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

|    0 |    1 |

|    0 |    1 |

|  1 |    2 |

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

3 rows in set (0.00 sec)

2.有主键

mysql> create table t03(id int,a int,b int,primary key(id))engine=innodb;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t03 values(1,1,1),(3,0,1);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t03;

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

| id | a    | b    |

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

|  1 |    1 |    1 |

|  3 |    0 |    1 |

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

2 rows in set (0.00 sec)

mysql> update t03set a=1 and b=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from t03;

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

| id | a    | b    |

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

|  1 |  0 |    1 |

|  3 |    0 |    1 |

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

2 rows in set (0.00 sec)

mysql> truncate table t03;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t03 values(1,1,1),(3,0,1),(2,3,5),(5,9,5);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t03 ;

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

| id | a    | b    |

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

|  1 |    1 |    1 |

|  2 |    3 |    5 |

|  3 |    0 |    1 |

|  5 |    9 |    5 |

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

4 rows in set (0.00 sec)

mysql>update t03 set a=1 and b=2;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from t03 ;

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

| id | a    | b    |

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

|  1 |    0 |    1 |

|  2 |    0 |    5 |

|  3 |    0 |    1 |

|  5 |    0 |    5 |

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

4 rows in set (0.00 sec)

mysql> update t02 seta=7 and b=7;

Query OK, 1 row affected (0.00 sec)

Rows matched: 3  Changed: 1  Warnings: 0

mysql> select * from t02;

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

| a    | b    |

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

|   0|    1 |

|    0|    1 |

|    1|    7 |

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

总结：这个update(update t03 set a=1 and b=2;) 千万不要当成是update t03 set a=1,b=2;

update t03 set a=1 and b=2;  其实是update t03 set a=(1 and b=2);

#其实就是对列 a 赋值啊，1 and b=2 把列b=2 的再and 1 取 and 之后的逻辑值(0或1)，哈哈，是不是一不小心就踩进坑了啊,眼睛睁大了哈……