MariaDB中带同表子查询的DELETE删重复数据失败如何解决

问题根因 #

这个报错和SQL_SAFE_UPDATES参数没有关系，核心是MariaDB/MySQL的InnoDB引擎不支持在DELETE语句中直接对同一张表做读写同时操作：执行删除时引擎会给涉及的行加写锁，而同表子查询需要实时读取表内数据，会触发锁冲突，最终导致查询被中断。
另外你原来的查询逻辑本身有数据错误风险：GROUP BY message_id,uid时没有通过聚合函数明确指定要保留的记录id，非严格SQL模式下会随机返回分组内的任意id，子句里的ORDER BY created_at也不会对GROUP BY的结果生效，根本达不到“按创建时间排序保留指定记录”的效果，直接用很容易误删数据。

可用执行方案 #

方案1：多表关联删除（性能最优，无需额外临时表）

通过自关联的方式先匹配出所有需要保留的记录id，直接删除匹配不到的冗余数据，完全避开同表子查询的锁问题。
如果你的需求是同一个uid+message_id分组下，保留创建时间最早的记录，直接执行下面的语句即可：

SET SQL_SAFE_UPDATES = 0;
DELETE m1 FROM messages m1
LEFT JOIN (
    SELECT MIN(id) AS keep_id 
    FROM messages
    WHERE uid = '11899414026778263'
    GROUP BY message_id, uid
) m2 ON m1.id = m2.keep_id
WHERE m2.keep_id IS NULL AND m1.uid = '11899414026778263';
SET SQL_SAFE_UPDATES = 1;

如果需要保留分组内创建时间最新的记录，把语句里的MIN(id)替换成MAX(id)就行。

执行删除前务必先校验数据：把DELETE m1 FROM替换成SELECT m1.* FROM运行，确认返回的待删除记录和预期完全一致后再执行删除操作。

方案2：嵌套派生表删除（兼容低版本MariaDB）

如果多表关联在你的环境下执行异常，可以给保留id的子查询再加一层嵌套，让引擎先生成存储保留id的临时派生表，再做删除判断，绕开同表读写冲突：

SET SQL_SAFE_UPDATES = 0;
DELETE FROM messages WHERE id NOT IN (
    SELECT keep_id FROM (
        SELECT MIN(id) AS keep_id
        FROM messages
        WHERE uid = '11899414026778263'
        GROUP BY message_id, uid
    ) AS tmp_keep_ids
);
SET SQL_SAFE_UPDATES = 1;

执行注意事项 #

• 删除前先备份目标数据，避免误删：可以先执行CREATE TABLE messages_bak AS SELECT * FROM messages WHERE uid = '11899414026778263';做全量备份。
• 如果单条uid下的重复数据量超过10万，建议分批删除，每次删除加LIMIT 1000循环执行，避免长事务锁表影响线上业务，直到语句返回影响行数为0即删除完成。

内容的提问来源于stack exchange，提问作者Joachim Rives