子查询再多套一层（不过这样就不支持走索引了，线上环境慎用！）
不支持的写法：
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
