MySQL如何批量删除SQL行且跳过存在有效外键引用的记录

MySQL没有原生支持「删除时自动跳过触发外键约束行」的语法，你可以通过以下两种低成本方案实现需求，无需手动梳理所有外键关联关系：

方案1：存储过程逐行删除+异常捕获（适合数据量偏小的场景） #

利用MySQL存储过程的异常捕获机制，逐行删除目标条目，碰到外键约束报错就直接跳过，完全依赖数据库本身的约束判断，不会误删被引用的行：

DELIMITER //
CREATE PROCEDURE delete_unused_files(IN target_path VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE file_id INT;
    -- 定义游标读取目标路径下的所有文件id
    DECLARE cur CURSOR FOR SELECT id FROM files WHERE path = target_path;
    -- 捕获外键约束违反错误（错误码1451），直接跳过不处理
    DECLARE CONTINUE HANDLER FOR 1451 BEGIN END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO file_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        DELETE FROM files WHERE id = file_id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

调用方式：
CALL delete_unused_files('/tmp/folder-with-duplicates');
执行完成后可删除残留存储过程：
DROP PROCEDURE IF EXISTS delete_unused_files;

方案2：元数据自动生成批量删除语句（适合数据量偏大的场景） #

如果重复条目量级大，逐行删除效率低，可以直接从information_schema元数据表中自动拉取所有关联files.id的字段，生成批量删除语句，无需手动梳理关联表：

-- 自动生成所有关联files.id的字段查询语句
SELECT GROUP_CONCAT(
    'SELECT ', COLUMN_NAME, ' AS id FROM ', TABLE_NAME
    SEPARATOR ' UNION '
) INTO @used_ids_query
FROM information_schema.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME = 'files' 
AND REFERENCED_COLUMN_NAME = 'id'
AND TABLE_SCHEMA = DATABASE();

-- 拼接最终删除SQL
SET @delete_sql = CONCAT('DELETE FROM files WHERE path = ''/tmp/folder-with-duplicates'' AND id NOT IN (', @used_ids_query, ')');

-- 可先打印SQL确认逻辑无误再执行
-- SELECT @delete_sql;

-- 执行删除
PREPARE stmt FROM @delete_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意：两种方案执行前都建议先备份相关表数据，避免误操作。

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