work mysql 清除关联表数据

问题提出

有两个表主从关联，如果删除了主表数据，同时也要删除从表数据相对应的数据。

比如有两个表，一个是 member 用户表，一个是 delivery 骑手表，骑手也是用户。
他们通过 member_id 关联。

1. 建表：

CREATE TABLE `es_member` (
  `member_id` varchar(64) NOT NULL COMMENT '会员ID',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`member_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `es_delivery` (
  `member_id` varchar(64) NOT NULL COMMENT '会员id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

2. 数据自己随便加几条

3. 思路： 找出他们现在存在关联关系的数据的 member_id 的集合, 然后删除不在这个集合中的

DELETE FROM `es_delivery` WHERE member_id NOT IN (SELECT d.member_id FROM es_member m INNER JOIN es_delivery d ON m.member_id = d.member_id);

4. 报错：

You can't specify target table 'es_delivery' for update in FROM clause

原因：不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。

5. 解决思路：如果不能再一个表中同时查询并更新，那就建立个中间表。

修改：

DELETE FROM `es_delivery` WHERE member_id NOT IN (SELECT m_id FROM(SELECT m.member_id m_id FROM es_member m INNER JOIN es_delivery d ON m.member_id = d.member_id) AS temp);

分解：

1. 先 select 出 有关联的数据

SELECT m.member_id m_id FROM es_member m INNER JOIN es_delivery d ON m.member_id = d.member_id

2. 创建一个中间表存放 member_id

SELECT m_id FROM(SELECT m.member_id m_id FROM es_member m INNER JOIN es_delivery d ON m.member_id = d.member_id) AS temp

3. 最后删除已经没有关联的数据

DELETE FROM `es_delivery` WHERE member_id NOT IN ();