MySQL的update語句避坑

mysql> drop table if exists t1;

Query OK, 0 rows affected (0.01 sec)

mysql>  CREATE TABLE `t1` (

    -> `id` int(11) DEFAULT NULL,

    -> `c1` int(11) DEFAULT NULL,

    -> `c2` varchar(16) DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> insert into t1 values (1,1,'A'),(2,2,'B'),(3,3,'C');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;

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

| id   | c1   | c2   |

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

|    1 |    1 | A    |

|    2 |    2 | B    |

|    3 |    3 | C    |

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

3 rows in set (0.00 sec)

正常的update語句，set部分用逗號分隔：

mysql> update t1 set c1=11,c2='AA' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;

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

| id   | c1   | c2   |

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

|    1 |   11 | AA   |

|    2 |    2 | B    |

|    3 |    3 | C    |

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

3 rows in set (0.00 sec)

set部分用and連線，輕則造成報錯，重則得到意料之外的資料！

mysql> update t1 set c2='BB' and c1= 22 where id=2;

ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'BB'

mysql> update t1 set c1=22 and c2='BB' where id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;

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

| id   | c1   | c2   |

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

|    1 |   11 | AA   |

|    2 |    0 | B    |

|    3 |    3 | C    |

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

3 rows in set (0.00 sec)

這是由於實際被轉換為類似下面的邏輯了：

(1)update t1 set c2=('BB' and c1= 22) where id=2;

(2)update t1 set c1=(22 and c2='BB') where id=2;

由於第一個語句中'BB'不能轉換為邏輯上的true/false，因此報錯了

第二個語句中22為true，c2='BB'為false，因此整個邏輯表示式的結果為false，即0，因此最終結果是id=2的資料c1欄位被更新為0。

實測發現該問題在5.7、8.0、MGR環境中都存在，我們自己的指令碼稽核平臺也有此問題，需要特別關注！！！

附：

mysql的update語法：

oracle的update語句：

SQL> update t1 set c1=22 and c2='BB' where id=2;

update t1 set c1=22 and c2='BB' where id=2

                    *

ERROR at line 1:

ORA-00933: SQL command not properly ended

參考文件：

https://blog.csdn.net/weixin_31208627/article/details/113455985