【MySQL】多表连接更新（update），使用临时表加快效率_mysql update 多表

场景

写一个update语句，需要关联多个表。关联一个表还好，但是你要关联四五个表：

update aTable a
inner join bTable b on a.id = b.id
inner join cTable c on b.define1 = c.subcode
inner join dTable d on d.id = c.subcode
set a.Demand_orgid = c.req_org_id
where xxx = xxx;
1
2
3
4
5
6

上面这个SQL，在一个update里面连接了3个inner join。那么你会发现执行速度慢的出奇。

如果你使用left join，那么会更卡几倍。这点有待考证，所以能用inner join连就用inner join 连吧。

解决方案

MySQL为我们提供了一种临时表，可以应用于这种复杂数据更新的场景：

在MySQL中，临时表是一种特殊类型的表，它允许您存储一个临时结果集，可以在单个会话中多次重用。
当使用JOIN子句查询需要单个SELECT语句的数据是不可能或遇到瓶颈的时候，临时表非常方便。 在这种情况下，我们就可以使用临时表来存储直接结果，并使用另一个查询来处理它。

参考资料：https://www.yiibai.com/mysql/temporary-table.html

MYSQL临时表只能出现在【数据库连接的单个会话中】。也就是说你重启Navicat，临时表缓存就消失

那么解决方案就出来了，你可以先使用select语句，将你所有需要关联的条件整合成一张临时表：

-- 创建临时表
CREATE TEMPORARY TABLE tempTable0(
select a.resid,b.define1,c.reqid,a.id  from tableA a
inner join tableB b on a.id = b.id 
inner join tableC c on c.id = b.id
where c.id <> '10086'
);
-- 查看你的临时表：
select *　from　tempTable0;
1
2
3
4
5
6
7
8
9

然后你就有了一张可以随便玩的临时表。

最后写更新语句的时候，只需要写一个inner join，连接这个临时表，就可以实现复杂数据更新了：

update tableA a
inner join tempTable0 b on a.id = b.id
set a.Demand_orgid = b.req_org_id;
1
2
3

补充：临时表选择数据库、删除临时表

临时表是根据当前链接选择的数据库创建的

-- 选择数据库
use db1;
-- 创建临时表
CREATE TEMPORARY TABLE tempTable0 (
  select  publishtime  from table0 as a 
  left join db3.bs as b on b.pu_billcode = a.upcode);

-- 选择临时表
  select * from db1.tempTable0;
-- 删除临时表
  DROP TEMPORARY TABLE IF EXISTS db1.tempTable0;
1
2
3
4
5
6
7
8
9
10
11