关于MySQL中删除满足子查询结果数据的操作

关键问题:MySQL不允许在子查询中使用要删除的表

错误示范:

DELETE FROM post_activity_user WHERE id IN ( SELECT a.id FROM post_activity_user a LEFT JOIN post_activity_info b ON a.activity_id = b.id WHERE b.enterprise_id = 10000000 AND b.activity_type = 3 )

正确处理:

DELETE FROM post_activity_user WHERE post_activity_user.id IN ( SELECT * FROM ( SELECT a.id FROM post_activity_user a LEFT JOIN post_activity_info b ON a.activity_id = b.id WHERE b.enterprise_id = 10000000 AND b.activity_type = 3 ) ee )

 

DELETE tb_a from table_a as tb_a INNER JOIN (SELECT * from table_a where name like ‘123%’) as tb_b on tb_b.id = tb_a.id; 

 

 

delete后加 limit提高效率

写在前面，如果是清空表数据建议直接用truncate，效率上truncate远高于delete，应为truncate不走事务，不会锁表，也不会生产大量日志写入日志文件；truncate table table_name 后立刻释放磁盘空间，并重置auto_increment的值。delete删除不释放磁盘空间，但后续insert会覆盖在之前删除的数据上。详细了解请跳转另一篇博文《delete、truncate、drop的区别有哪些，该如何选择》

下面只讨论delete场景，首先，delete后面是支持limit关键字的，但仅支持单个参数，也就是[limit row_count]，用于告知服务器在控制命令被返回到客户端前被删除的行的最大值。

delete limit语法如下：

（值得注意的是，当需要用到order by排序时，必须order by + limit联用，否则order by 就会被优化器优化掉，被认为无意义。）

delete [low_priority] [quick] [ignore] from tbl_name [where ...] [order by ...] [limit row_count]

加limit的的优点：

以下面的这条SQL为例：

delete from t where sex = 1;  1. 降低写错SQL的代价，就算删错了，比如limit 500,那也就丢了500条数据，并不致命，通过binlog也可以很快恢复数据。2. 避免了长事务，delete执行时MySQL会将所有涉及的行加写锁和Gap锁（间隙锁），所有DML语句执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。3. delete数据量大时，不加limit容易把cpu打满，导致越删越慢。

针对上述第二点，前提是sex上加了索引，大家都知道，加锁都是基于索引的，如果sex字段没索引，就会扫描到主键索引上，那么就算sex = 1 的只有一条记录，也会锁表。

 

 

参考：

关于MySQL中删除满足子查询结果数据的操作：https://www.cnblogs.com/wing7319/p/10458765.html

delete后加 limit是个好习惯么：https://blog.csdn.net/qq_39390545/article/details/107519747