MySQL多表更新的一個坑

簡述

MySQL支援update t1,t2 set t1.a=2;這種文法，别的關系資料庫例如oracle和sql server都不支援。這種文法有時候寫起來挺友善，但他有一個坑。

測試腳本

drop database fander;
create database fander;
use fander;
create table t1(a int);
create table t2(a int);
insert into t1 value(1);
select * from t1;
update t1,t2 set t1.a=2;
select * from t1;           

測試結果

mysql> drop database fander;
create table t2(a int);
insert into t1 value(1);
select * from t1;
update t1,t2 set t1.a=2;
select * from t1;Query OK, 6 rows affected (0.35 sec)

mysql> create database fander;
Query OK, 1 row affected (0.00 sec)

mysql> use fander;
Database changed
mysql> create table t1(a int);
Query OK, 0 rows affected (0.11 sec)

mysql> create table t2(a int);
Query OK, 0 rows affected (0.23 sec)

mysql> insert into t1 value(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> update t1,t2 set t1.a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>            

結果非預期

我們想更新t1表的a列值為2，但發現并沒有更新成功。。。

原因

因為t2是空表！

update t1,t2 set t1.a=2; 這個語句看起來修改與t2無關。t2是否空表應該不影響update行為才對呀？

https://dev.mysql.com/doc/refman/8.0/en/update.html

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.

官方手冊其實寫明了，update文法是根據table_references，即關聯後的表，再做更新的。

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t1,t2;
Empty set (0.00 sec)           

因為t2為空表，t1的資料和空表資料做笛卡爾積，關聯表後則為空。

update對這個空的table_references做更新，當然就找不到記錄，就沒有更新成功咯。

總結

這是一個坑，一般使用遇不到，但需要知道。