mysql同时更新两张表6_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行为才对呀？

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做更新，当然就找不到记录，就没有更新成功咯。

总结

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