按指定删除顺序从多子表到父表动态删除关联数据的SQL实现问题

实现思路 #

• 假设你存储父子表关联规则的配置表名为table_relation_config，可根据实际表名修改
• 按Delete_Order降序拼接删除语句，保证子表先删、父表后删
• 支持匹配父表指定主键值批量删除关联数据

动态SQL实现（SQL Server版本） #

DECLARE @TargetParentID INT = 1001; -- 此处修改为你要删除的父表Parent1的主键值
DECLARE @Sql NVARCHAR(MAX) = N'';

-- 按删除顺序降序拼接删除语句
SELECT @Sql += N'
DELETE c
FROM ' + QUOTENAME(Child_table) + N' c
' + CASE WHEN Parent_Table IS NOT NULL THEN 
    N'INNER JOIN ' + QUOTENAME(Parent_Table) + N' p ON c.' + QUOTENAME(Child_column) + N' = p.' + QUOTENAME(Parent_Column) + N'
    WHERE p.' + QUOTENAME(Parent_Column) + N' = @TargetParentID;'
ELSE 
    N'WHERE c.' + QUOTENAME(Child_column) + N' = @TargetParentID;'
END
FROM table_relation_config
ORDER BY Delete_Order DESC;

-- 执行前可先打印@Sql核对语句是否符合预期
-- PRINT @Sql
-- 执行动态SQL
EXEC sp_executesql @Sql, N'@TargetParentID INT', @TargetParentID;

动态SQL实现（MySQL版本） #

SET @TargetParentID = 1001; -- 此处修改为你要删除的父表Parent1的主键值
SET @Sql = '';

-- 按删除顺序降序拼接删除语句
SELECT GROUP_CONCAT(
    CONCAT(
        'DELETE c FROM ', '`', Child_table, '` c ',
        IF(Parent_Table IS NOT NULL, 
            CONCAT('INNER JOIN `', Parent_Table, '` p ON c.`', Child_column, '` = p.`', Parent_Column, '` WHERE p.`', Parent_Column, '` = ? '),
            CONCAT('WHERE c.`', Child_column, '` = ? ')
        )
    ) SEPARATOR ';'
) INTO @Sql
FROM table_relation_config
ORDER BY Delete_Order DESC;

-- 执行前可先打印@Sql核对语句是否符合预期
-- SELECT @Sql;
-- 执行动态SQL，传入参数数量等于配置表总行数
PREPARE stmt FROM @Sql;
EXECUTE stmt USING @TargetParentID, @TargetParentID, @TargetParentID, @TargetParentID;
DEALLOCATE PREPARE stmt;

补充说明 #

• 如果你要删除的是父表中多个符合条件的主键，把@TargetParentID的匹配条件改为你需要的过滤规则即可
• 拼接语句时用了标识符包裹规则，避免表名、列名是关键字时报错
• Oracle环境逻辑和上述实现一致，仅动态SQL拼接、执行语法需要适配Oracle规则即可

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