MySQL因为外键关联无法删除数据解决方法以及修改数据库全部表中某种外键的值

最近工作遇到一个问题，一个储存课程的表中存在大量名称重复的课程，应该是写的导入功能中没有进行名称判断导致的，但是用户已经用了很久了，并且课程这个表又是基础表，很多其他的表中都有课程的外键，现在想把课程表中的重复的记录删除是不可能的，因为用户已经用了一段时间了，同样的名称的不同记录都被使用过，比如同样是语文，一个id是1，一个id是2。其他表中保存的数据中有的课程外键是1，有的课程外键是2这就导致如果2个语文的详细信息如果不同就会造成信息不一致，故现在要把这个数据库中所有表中课程外键为1的记录全部修改为2，但存在这个外键的表有几十个，不可能一个表一个表的update，并且除了语文还比如数学等几十条其他重复的数据。

费时近一天终于找到一个通用的解决办法，使用mysql的存储过程来进行批量更新，获取存在课程外键的表和列通过MySQL自带的系统表查询得到，现将解决此问题的存储过程代码记录于此，给遇到同样问题的人一个参考，也给自己记个笔记：

此第一个存储过程是处理本人问题的非通用方法，第二个存储过程为通用方法，可以直接复制去用；

使用时根据实际情况写第一个存储过程，然后循环调用第二个存储过程；

-- ----------------------------
-- Procedure structure for delete_repeat_kc
-- ----------------------------
DROP PROCEDURE IF EXISTS `delete_repeat_kc`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_repeat_kc`()
BEGIN
#此存储过程只能修改数据库全部表中外键为课程的值
DECLARE restResults int;
DECLARE firstId VARCHAR(128);
DECLARE secondId VARCHAR(128);
loop1:LOOP

#将存在重复的课程全部查询出来放到临时表中
DROP table if EXISTS tmp_repeat_kc;
create TEMPORARY table tmp_repeat_kc ENGINE=memory
SELECT * FROM jw_kc WHERE kcmc 
IN ( SELECT tt.kcmc FROM ( 
SELECT t.kcid kcid, t.mc kcmc, t.ct FROM ( 
SELECT kc.id kcid, kc.kcmc mc, count(1) ct FROM jw_kc kc 
GROUP BY kc.kcmc ) t WHERE t.ct > 1 ) tt ) ORDER BY kcmc;
#此语句查询出所有存在重复的课程记录，没有重复的将不会查询出来，并进行了排序
#意味着第一条和第二条数据一定的重复的，故每删除一条数据就查询一次，然后处理第一条和第二条数据
#这样如果是1 2条重复则处理后1删除第一条数据 第二次查询时第1条数据不存在，第二条数据就不存在重复
#故都不会查询出来，这时的1 2条数据就为其他重复的课程了
#如果 1 2 3条数据重复，则删除第一条，第二次查询出原来的 2 3条，后续结果同上
select count(1) into restResults from tmp_repeat_kc;
#查询重复记录的条数，根据查询规则要么0条，要么至少2条，不可能1条
if restResults=0 then leave loop1;end if;
select id  into firstId from tmp_repeat_kc limit 0,1;
select id  into secondId from tmp_repeat_kc limit 1,1;
#查询第一条数据的id和第二条数据的id，这2条数据的课程名称必定是重复的
#最后调用存储过程将全数据库外键为课程的记录的课程外键的值如果等于第一条的id就替换为第2条的id
call update_fk_id_from_all_table(firstId,secondId,'ncbd','jw_kc');
end loop loop1;
END
;;
DELIMITER ;

第二个存储过程的参数分别为：原来的id,要修改为的新id，数据库名称，要修改的外键对应的表名（非外键所在表：比如此例中就是课程表）
-- ----------------------------
-- Procedure structure for update_fk_id_from_all_table
-- ----------------------------
DROP PROCEDURE IF EXISTS `update_fk_id_from_all_table`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_fk_id_from_all_table`(in old_id VARCHAR(128),in new_id VARCHAR(128),in databasename VARCHAR(128),in fk_tablename VARCHAR(128))
BEGIN
#此存储过程旨在修改全数据库中所有表中引用同一个表的所有外键的值
DECLARE concatsql VARCHAR(1000);
DECLARE tablename VARCHAR(128);
DECLARE columnname VARCHAR(128);
DECLARE i int;
DECLARE t int;
#查询所有用到此外键的表名以及列名
drop table if EXISTS related_table_column;
CREATE TEMPORARY table related_table_column ENGINE=memory 
select * from information_schema.KEY_COLUMN_USAGE a WHERE a.REFERENCED_TABLE_NAME=fk_tablename
and a.CONSTRAINT_SCHEMA=databasename; 
set i=0;
select count(1) into t from related_table_column;

#循环表里此临时表
loop1:LOOP
if i>=t then leave loop1;end if;
select table_name,column_name into tablename,columnname from related_table_column limit i,1;
#当fk_tablename表中的主键为字符型的时候加上以下\'转义单引号，否则去掉
set concatsql=CONCAT('update ',tablename,' set ',columnname,'=\'',new_id,'\' where ',columnname,'=\'',old_id,'\'');
set @executesql=concatsql;
PREPARE stmt from @executesql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set i=i+1;
end loop loop1;
END
;;
DELIMITER ;

至此就是全部内容，本人水平有限，有什么错误的地方万望指出，希望此文对有此问题的人有所帮助！

 

原文地址：https://blog.csdn.net/enjoy_waste_time/article/details/78569381