12.26

1. 窗口函数ROW_NUMBER去重删除（MySQL 8.0+，精准保留目标行）

sql

-- 按name分组，保留每组最小id，删其余重复行
DELETE t FROM user t
JOIN (
SELECT id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) rn
FROM user
) temp ON t.id = temp.id
WHERE temp.rn > 1;

• 窗口函数分组排序，比JOIN写法更清晰，适合复杂分组去重。

2. 触发器辅助的标记删除（逻辑删，保留数据可恢复）

sql

-- 先建触发器，删除时写入删除日志
DELIMITER //
CREATE TRIGGER trg_user_delete BEFORE DELETE ON user
FOR EACH ROW
BEGIN
INSERT INTO user_delete_log(id, name, delete_time)
VALUES(OLD.id, OLD.name, NOW());
END //
DELIMITER ;

-- 执行删除（触发日志记录，实现逻辑删+可追溯）
DELETE FROM user WHERE id = 2002;

• 适合需保留删除记录、可追溯或恢复的场景，避免物理删丢数据。

3. 存储过程分批删除（大表防锁表，可控高效）

sql

DELIMITER //
CREATE PROCEDURE batch_delete_large_logs(IN days INT, IN batch_size INT)
BEGIN
REPEAT
DELETE FROM large_logs
WHERE log_time < DATE_SUB(NOW(), INTERVAL days DAY)
LIMIT batch_size;
UNTIL ROW_COUNT() = 0 END REPEAT;
END //
DELIMITER ;

-- 调用：删90天前日志，每次删1000条
CALL batch_delete_large_logs(90, 1000);

• 大表分批删，避免长事务锁表，降低对业务的影响。