子查询再多套一层(不过这样就不支持走索引了,线上环境慎用!)
不支持的写法:
DELETE FROM your_table WHERE id NOT IN (SELECT id FROM your_table ORDER BY created_at DESC LIMIT 100);
1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
支持的写法: 【注意】这样写就不支持走索引了
DELETE FROM your_table WHERE id NOT IN (SELECT id FROM (SELECT id FROM your_table ORDER BY created_at DESC LIMIT 100) AS sub_table);
select * FROM site_info_report_history WHERE id NOT IN (SELECT id FROM site_info_report_history ORDER BY id DESC LIMIT 10000) ORDER BY id DESC; select * FROM site_info_report_history WHERE id NOT IN (SELECT id FROM (SELECT id FROM site_info_report_history ORDER BY id DESC LIMIT 10000) AS sub_table) ORDER BY id DESC;
我这里的表太大了,直接查数据库容易崩掉。为了测试查询快下面两个SQL执行时最后加上了LIMIT 1
EXPLAIN看一下,可以看到rows等于数据库记录数,Backward index scan,说明没有走索引,直接全表扫描了,所以线上慎用!
参考:https://blog.csdn.net/weixin_42871288/article/details/121748859