MySQL百万行大表带LIMIT的删除语句报1064语法错误如何解决

错误原因 #

MySQL 语法规范明确限制：多表关联DELETE操作不支持使用ORDER BY和LIMIT子句，仅单表DELETE语句可以直接搭配这两个子句使用，因此你写的SQL触发了1064语法错误。

修复方案 #

如果需要实现分批删除避免锁表的需求，可以调整为先查询符合条件的主键ID，再执行删除的逻辑，兼容分批删除的要求，正确语句如下：

DELETE FROM table_a
WHERE id IN (
    -- 额外嵌套一层子查询，规避MySQL不允许同表查询后直接更新的限制
    SELECT temp_id FROM (
        SELECT N.id AS temp_id
        FROM table_a N 
        INNER JOIN table_b E 
            ON N.form_id = E.form_id 
            AND N.element_id = E.element_id 
            AND E.element_type IN('checkbox','radio','select')
        WHERE N.option_value = 0
        ORDER BY N.id
        LIMIT 1000
    ) AS temp
);

扩展优化建议 #

如果需要全量删除所有符合条件的百万级数据，可以将上述语句封装为循环逻辑，每执行一次删除暂停几百毫秒，避免占用过多数据库IO资源，影响线上正常业务。

内容的提问来源于stack exchange，提问作者Noufal netspective