mysql存储过程递归删除外键关联表

#说明，验证环境mysql 5.6

#删除一个及其主键作为外键的关联表对应的所有数据

#如果关联表的主键也是其他表的外键，则相应删除关联表，依次递归

DROP PROCEDURE IF EXISTS delete_for_relation;

DELIMITER //

#入参 数据库实例名，表明，表中某字段，该字段对应的值

CREATE PROCEDURE delete_for_relation (my_schema varchar(255), tb_name varchar(255), del_col_name varchar(255), del_col_value varchar(255))
    BEGIN

        DECLARE done INT DEFAULT FALSE;#判断游标结束标志
        
DECLARE tablename,columnname,refcolumname,next_col_value,next_table,my_sql,del_sql varchar(512) default null;#存储中间的表，字段，sql语句
        
#1.从tb_name开始，找到对应的外键关联表，放入游标
        
DECLARE CUR_FIRST_TABLES CURSOR FOR
select distinct(TABLE_NAME),COLUMN_NAME,REFERENCED_COLUMN_NAME,REFERENCED_TABLE_NAME
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = tb_name and TABLE_SCHEMA = my_schema;
        
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;#设置游标结束条件
        

#关联查询语句
        set my_sql = CONCAT(" from ", tb_name, " where ", del_col_name ," in (", del_col_value, ")");
        
        #删除语句
set del_sql = CONCAT("delete from ", tb_name, " where ", del_col_name ," in (", del_col_value, ")");

        
        #2.先删除其对应的外键表-递归

        #递归参数开启，mysql默认不开启递归，此处设置了深度
        SET @@SESSION.max_sp_recursion_depth=25;
        
        #打开游标
OPEN CUR_FIRST_TABLES;

#开始循环
read_loop: LOOP
        
#提取游标里的数据
FETCH CUR_FIRST_TABLES INTO tablename, columnname, refcolumname, next_table;
            
#声明结束的时候
IF done THEN
  LEAVE read_loop;
END IF;

#mysql在游标中不支持动态语法，所以只能拼接SQL，然后解析

#不支持多行，则多行转字符串，然后用in

set @my_sql = CONCAT("select GROUP_CONCAT(",refcolumname, ")", my_sql, " into @next_col_value ");

prepare stmt from @my_sql;
                
execute stmt;
                
deallocate prepare stmt;
                
                set next_col_value = @next_col_value;

                IF my_schema is not null AND tablename is not null AND columnname is not null AND next_col_value is not null THEN
#递归删除
call delete_for_relation(my_schema, tablename, columnname, next_col_value);
END IF; 

END LOOP;
#关闭游标
CLOSE CUR_FIRST_TABLES;
        

        #开始删除

        select del_sql;#打印删除语句到控制台
        
        set @del_sql = del_sql;
        
        prepare mainStmt from @del_sql; 
        
execute mainStmt;
        
        deallocate prepare mainStmt;
        
    END;
//
DELIMITER ;