mysql删除外键并重建

-- 使用information_schema查询外键信息：
SELECT 
    CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,  REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = '数据库名称';

-- 是否有跨库外键
SELECT 
    CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,  REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA IS NOT NULL;
    AND REFERENCED_TABLE_SCHEMA != TABLE_SCHEMA;

-- 将外键导入指定文件
SELECT 
    CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,  REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA IS NOT NULL
    into OUTFILE 'D:/tmp/test.txt';

   
-- 存储过程删除外键
-- 在MySQL中，可以使用以下存储过程来删除所有外键约束：
DELIMITER $$

CREATE PROCEDURE DeleteAllForeignKeys()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE fk_name VARCHAR(255);
    DECLARE cur_fk CURSOR FOR 
        SELECT CONCAT('ALTER TABLE ', table_name, ' DROP FOREIGN KEY ', constraint_name) 
        FROM information_schema.key_column_usage 
        WHERE constraint_schema = DATABASE() AND constraint_name LIKE 'fk_%';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_fk;

    read_loop: LOOP
        FETCH cur_fk INTO fk_name;
        IF done THEN 
            LEAVE read_loop;
        END IF;
        SET @stmt = fk_name;
        PREPARE stmt FROM @stmt;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur_s;
END$$

DELIMITER ;

--使用该存储过程前，请确保您已经备份了数据库，并且了解这将移除所有外键约束，操作无法撤销。执行存储过程的命令是：
CALL DeleteAllForeignKeys();
--注意：此存储过程假设外键名以'fk_'开头。如果您的外键命名规则不同，请相应地调整查询。
-- 删除存储过程
DROP PROCEDURE IF EXISTS DeleteAllForeignKeys();


-- 通过临时表导入备份数据
CREATE TABLE temp_ref_info (
	CONSTRAINT_NAME VARCHAR(64), 
	TABLE_SCHEMA VARCHAR(64), 
	TABLE_NAME VARCHAR(64), 
	COLUMN_NAME VARCHAR(64), 
	REFERENCED_TABLE_NAME VARCHAR(64), 
	REFERENCED_COLUMN_NAME VARCHAR(64)
);

-- 将文件数据导入临时表
LOAD DATA INFILE 'D:/tmp/test.txt'
INTO TABLE temp_ref_info
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '
'
(CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,  REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME);


--通过存储过程添加外键
DROP PROCEDURE IF EXISTS add_ref;
DELIMITER $$
CREATE PROCEDURE add_ref()
BEGIN
DECLARE const_name VARCHAR(64);
DECLARE db_name VARCHAR(64);
DECLARE tbl_name VARCHAR(64);
DECLARE col_name VARCHAR(64);
DECLARE col_name VARCHAR(64);
DECLARE ref_col_name VARCHAR(64);

DECLARE done INT DEFAULT 0;

DECLARE add_ref_key CURSOR FOR SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,  REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM temp_ref_info;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN add_ref_key;

tables_loop: LOOP

FETCH add_ref_key INTO const_name, db_name, tbl_name, col_name, col_name, ref_col_name;

IF done THEN 
LEAVE tables_loop;
END IF;
SET @add_ref_ddl := CONCAT('ALTER TABLE ', db_name, '.', tbl_name, ' ADD CONSTRAINT ', const_name, ' FOREIGN KEY (', col_name, ' ) REFERENCES ', db_name, '.', ref_tbl_name, '(', ref_col_name, ')');
PREPARE stmt FROM @add_ref_ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE add_ref_key;
END$$
DELIMITER;

call add_ref()

-- 删除存储过程
DROP PROCEDURE IF EXISTS add_ref;

-- 上面存储过程不能处理复合主键的场景，得排查有复合主键的表单独操作