MYSQL数据库外键批量备份（还原）及批量删除外键

背景

在上一篇文章《便捷的批量修改MySQL数据库表及字段的字符集及排序规则》 中进行批量修改表字段的字符集及排序规则时，如果字段有被外键引用，则无法进行修改，会报错，如：

1832 - Cannot change column 'EXECUTION_ID_': used in a foreign key constrain ...

这种问题的解决的思路有两种。
一是：

1. 备份相关表的外键配置
2. 删除外键关联
3. 再修改
4. 还原外键配置

上面的操作如果针对小范围的修改，可以手工进行备份和还原，如果存在多个表批量操作的话 手工操作就比较繁琐。

二是：
在执行前先将外键检查关闭，执行修改sql后在 打开外键约束检查。

-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;
xxxxx

-- 打开外键检查
SET FOREIGN_KEY_CHECKS = 1;

上面的方法二已经可以解决文章开头说的问题了。如果方法一可以用于其他特殊场景，下面详细说下方法一的操作过程。

解决办法

下面提供了备份外键和删除外键的SQL。
主要是通过 mysql的information_schema的REFERENTIAL_CONSTRAINTS和KEY_COLUMN_USAGE两张表进行实现。

注意：在执行删除前，一定要先备份

备份SQL

SELECT 
CONCAT('ALTER TABLE `',TABLE_SCHEMA,'`.`', TABLE_NAME, '` ADD CONSTRAINT `' ,CONSTRAINT_NAME, '` FOREIGN KEY (', COLUMN_NAMES, ') REFERENCES `', REFERENCED_TABLE_SCHEMA ,'`.`', REFERENCED_TABLE_NAME, '` (', REFERENCED_COLUMN_NAMES, ') ON DELETE ', DELETE_RULE, ' ON UPDATE ', UPDATE_RULE, ';' 
) AS 'fk_add_sql' 
FROM (
SELECT
  temp.CONSTRAINT_SCHEMA,
	temp.CONSTRAINT_NAME,
	temp.UPDATE_RULE,
	temp.DELETE_RULE,
	temp.TABLE_SCHEMA,
	temp.TABLE_NAME,
	temp.REFERENCED_TABLE_SCHEMA,
	temp.REFERENCED_TABLE_NAME,
	GROUP_CONCAT(CONCAT('`', temp.COLUMN_NAME, '`')) AS COLUMN_NAMES,
	GROUP_CONCAT(CONCAT('`',temp.REFERENCED_COLUMN_NAME , '`')) AS REFERENCED_COLUMN_NAMES
FROM
	(
	SELECT
		r1.CONSTRAINT_SCHEMA,
		r1.CONSTRAINT_NAME,
		r1.UPDATE_RULE,
		r1.DELETE_RULE,
		k1.TABLE_SCHEMA,
		r1.TABLE_NAME,
		k1.COLUMN_NAME,
		K1.REFERENCED_TABLE_SCHEMA,
		r1.REFERENCED_TABLE_NAME,
		k1.REFERENCED_COLUMN_NAME 
	FROM
		information_schema.REFERENTIAL_CONSTRAINTS r1
		LEFT JOIN information_schema.KEY_COLUMN_USAGE k1 ON k1.CONSTRAINT_NAME = r1.CONSTRAINT_NAME 
		AND k1.TABLE_NAME = r1.TABLE_NAME 
		AND k1.REFERENCED_TABLE_NAME = r1.REFERENCED_TABLE_NAME 
	WHERE
		k1.TABLE_SCHEMA = '你的数据库名' and r1.CONSTRAINT_SCHEMA='你的数据库名'
		AND k1.REFERENCED_TABLE_NAME IS NOT NULL AND k1.TABLE_NAME IS NOT NULL
	) temp 
GROUP BY
  temp.CONSTRAINT_SCHEMA,
	temp.CONSTRAINT_NAME,
	temp.UPDATE_RULE,
	temp.DELETE_RULE,
	temp.TABLE_SCHEMA,
	temp.TABLE_NAME,
	temp.REFERENCED_TABLE_SCHEMA,
	temp.REFERENCED_TABLE_NAME) temp2;

删除SQL

SELECT DISTINCT 
	CONCAT( 'ALTER TABLE `', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;' ) AS 'fk_drop_sql' 
FROM
	information_schema.KEY_COLUMN_USAGE 
WHERE
	TABLE_SCHEMA = '你的数据库名' 
	AND REFERENCED_TABLE_NAME IS NOT NULL;