想实现删除最近100条记录之前的所有数据。
一开始想实现的SQL如下:
DELETE FROM car_base_state WHERE car_id IN (
SELECT car_id FROM car_base_state ORDER BY currtime DESC, car_id DESC
LIMIT (SELECT count(*) FROM car_base_state) OFFSET 100
)
但是这个sql是错误的。
问题一
mysql不支持limit+子句
的方式,如下两种语句都是错误的:
SELECT * FROM car_base_state ORDER BY currtime DESC, car_id DESC
LIMIT 2,(SELECT count(*) FROM car_base_state)
或
SELECT * FROM car_base_state ORDER BY currtime DESC, car_id DESC
LIMIT (SELECT count(*) FROM car_base_state) OFFSET 2
于是SQL改为:
DELETE FROM car_base_state WHERE car_id IN (
SELECT car_id FROM car_base_state ORDER BY currtime DESC, car_id DESC
LIMIT 100,-1
)
这个SQL仍然是错误的。
问题二
mysql也不再支持limit n,-1
的方式。
于是只能把-1改为较大的数,SQL改为:
DELETE FROM car_base_state WHERE car_id IN (
SELECT car_id FROM car_base_state ORDER BY currtime DESC, car_id DESC
LIMIT 100,100000
)
但是仍然报错:
问题三
Mysql不再支持IN
子查询包含LIMIT
,如下错误提示:
[42000][1235] This version of MySQL doesn’t yet support ‘LIMIT &
IN/ALL/ANY/SOME subquery’
所以进一步修改SQL为:
DELETE FROM car_base_state
WHERE state_id IN (
SELECT state_id FROM (
SELECT state_id
FROM car_base_state
ORDER BY currtime DESC, car_id DESC
LIMIT 100, 100000
) a
)
终于可以了。
参考:
数据库之删除超过50条的数据
MySQL的Limit详解
mysql错误之limit和-1
解决mysql中limit和in不能同时使用的问题