MariaDB 10.4.22如何不用IN子句实现带LIMIT偏移的行删除

需要删除带偏移量的指定行时，由于原生DELETE子句不直接支持偏移参数，常规通过ID IN+子查询LIMIT的写法在MariaDB 10.4.22版本中会触发报错：

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

目标效果是删除customers表中register_date > '2012-01-01'、按注册时间升序排序后，偏移5条开始的10条记录，表结构如下：

CREATE TABLE customers (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
REGISTER_DATE DATETIME NOT NULL
);

方案1：嵌套派生表绕过限制 #

MariaDB仅限制IN关键字后直接关联的第一层子查询使用LIMIT，多嵌套一层派生表即可绕过该限制，写法如下：

DELETE FROM customers
WHERE ID IN (
    SELECT ID FROM (
        SELECT ID
        FROM customers
        WHERE register_date > '2012-01-01'
        ORDER BY register_date ASC
        LIMIT 5, 10
    ) AS tmp
);

执行逻辑是先将内层带LIMIT的查询结果生成临时派生表tmp，再匹配ID执行删除，不会触发版本语法限制。

方案2：JOIN关联删除（大数据量下性能更优） #

通过内连接匹配待删除ID的方式实现，不需要多层嵌套，数据量较大时执行效率比IN写法更高：

DELETE c1 FROM customers c1
INNER JOIN (
    SELECT ID
    FROM customers
    WHERE register_date > '2012-01-01'
    ORDER BY register_date ASC
    LIMIT 5, 10
) c2 ON c1.ID = c2.ID;

• 如果register_date字段存在重复值，建议在ORDER BY后补充主键ID排序，即写成ORDER BY register_date ASC, ID ASC，保证排序结果唯一稳定，避免偏移量计算出错。
• 正式执行删除前，可将内层SELECT子句单独执行，核对返回的ID集合是否为目标删除数据，避免误删。

内容的提问来源于stack exchange，提问作者Paul Shan