MariaDB 10.3.38使用CTE执行DELETE语句时出现语法错误求助

你的核心问题是CTE与DELETE结合时的写法不符合MariaDB的语法规范，虽然Workbench的语法检查可能未报错，但CLI会严格执行语法规则。此外，原代码中存在潜在的列匹配风险，以下是具体修正方案：

修正后的代码 #

写法一（使用JOIN） #

WITH results AS
    (SELECT address FROM table1 WHERE time <= '04:38:24' LIMIT 10),
results2 AS
    (SELECT UserId FROM table2 WHERE address IN (SELECT address FROM results)),
results3 AS
    (SELECT id FROM table4 WHERE username IN (SELECT UserId FROM results2))
DELETE table3
FROM table3
JOIN results3 ON table3.column_id = results3.id;

写法二（使用USING） #

WITH results AS
    (SELECT address FROM table1 WHERE time <= '04:38:24' LIMIT 10),
results2 AS
    (SELECT UserId FROM table2 WHERE address IN (SELECT address FROM results)),
results3 AS
    (SELECT id FROM table4 WHERE username IN (SELECT UserId FROM results2))
DELETE FROM table3
USING results3
WHERE table3.column_id = results3.id;

关键修正点 #

• 明确IN子查询的列名：原代码中select * from results用于IN子查询，如果table1包含多列会导致语法错误，改为明确指定列名（如address）避免歧义。
• 调整DELETE语法结构：MariaDB要求DELETE与JOIN/USING结合时遵循固定格式，要么是DELETE 目标表 FROM 目标表 JOIN ...，要么是DELETE FROM 目标表 USING 关联表 WHERE ...，原写法的USING位置不符合规范。

额外排查项 #

1. MariaDB版本检查：如果你的版本低于10.2，不支持CTE语法，需要升级版本，或者改用嵌套子查询替代CTE：

DELETE table3
FROM table3
JOIN (
    SELECT id FROM table4 
    WHERE username IN (
        SELECT UserId FROM table2 
        WHERE address IN (
            SELECT address FROM table1 WHERE time <= '04:38:24' LIMIT 10
        )
    )
) results3 ON table3.column_id = results3.id;

2. CLI执行问题：如果语句因换行被截断，可尝试将语句合并为一行，或使用DELIMITER //调整分隔符后执行。

外键约束注意事项 #

由于table4与table3存在外键关联，删除table3数据前需确保table4中无对应引用记录，否则会触发约束错误。如需自动级联删除，可修改外键定义添加ON DELETE CASCADE，或先删除table4中的关联数据。

内容的提问来源于stack exchange，提问作者Manish Ahluwalia