MariaDB按serial_numb分组保留最后N条记录DELETE报错如何解决

方案1：全版本兼容写法 #

通过嵌套一层临时派生表绕过LIMIT不能直接用于IN子查询的限制，兼容所有支持子查询的MariaDB版本：

DELETE FROM instant_data m1
WHERE id NOT IN (
    -- 嵌套一层临时表规避语法限制
    SELECT id FROM (
        SELECT m2.id
        FROM instant_data m2
        WHERE m2.serial_numb = m1.serial_numb
        ORDER BY m2.id DESC
        LIMIT 10
    ) AS temp_tbl
);

方案2：窗口函数优化写法（MariaDB 10.2+ 支持） #

对于数据量较大的场景，使用窗口函数关联删除的执行效率更高：

DELETE m1
FROM instant_data m1
INNER JOIN (
    SELECT 
        id,
        -- 按serial_numb分组，id倒序编号，最新的10条编号为1~10
        ROW_NUMBER() OVER (PARTITION BY serial_numb ORDER BY id DESC) AS row_num
    FROM instant_data
) AS ranked_tbl ON m1.id = ranked_tbl.id
WHERE ranked_tbl.row_num > 10;

大表优化建议 #

如果表数据量极大，担心单次删除锁表时间过长影响业务，可以加外层LIMIT分批次执行，每次删除指定条数，重复执行直到返回影响行数为0即可：

DELETE FROM instant_data m1
WHERE id NOT IN (
    SELECT id FROM (
        SELECT m2.id
        FROM instant_data m2
        WHERE m2.serial_numb = m1.serial_numb
        ORDER BY m2.id DESC
        LIMIT 10
    ) AS temp_tbl
)
LIMIT 1000;

内容的提问来源于stack exchange，提问作者Oscar Fuentes