一.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 t03 set 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 set a=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),哈哈,是不是一不小心就踩进坑了啊,眼睛睁大了哈……
转载于:https://blog.51cto.com/huanghualiang/1557143