强制 InnoDB 重新检查表/表上的外键？

我有一组 InnoDB表，我定期需要通过删除一些行和插入其他行来维护它们。有几个表具有引用其他表的外键约束，因此这意味着表加载顺序很重要。要插入新行而不用担心表的顺序，我使用:

SET FOREIGN_KEY_CHECKS=0;

之前，然后:

SET FOREIGN_KEY_CHECKS=1;

之后。

加载完成后，我想检查一下更新表中的数据是否仍然保持参照完整性——新行不会破坏外键约束——但似乎没有办法做到这一点。

作为测试，我输入了确定违反外键约束的数据，在重新启用外键检查后，mysql 没有产生任何警告或错误。

如果我试图找到一种方法来指定表加载顺序，并在加载过程中保留外键检查，这将不允许我在具有自引用外键约束的表中加载数据，因此这将不是一个可接受的解决方案。

有没有办法强制 InnoDB 验证表或数据库的外键约束？

22179 次浏览
小开

没有工具可以做到这一点。但您可以编写一个脚本，它将遍历所有表，删除并重新创建外键约束。在娱乐方面，如果有错误，就会有错误。

小开
最佳答案 
DELIMITER $$


DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$


CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))


LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA


BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);


DECLARE done INT DEFAULT 0;


DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;


CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;


CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;




OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;




SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;


EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';');
END IF;
DEALLOCATE PREPARE stmt;


END LOOP foreign_key_cursor_loop;
END$$


DELIMITER ;


CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;


SELECT * FROM INVALID_FOREIGN_KEYS;

You can use this stored procedure to check the all database for invalid foreign keys. The result will be loaded into INVALID_FOREIGN_KEYS table. Parameters of ANALYZE_INVALID_FOREIGN_KEYS:

  1. Database name pattern (LIKE style)
  2. Table name pattern (LIKE style)

  3. Whether the result will be temporary. It can be: 'Y', 'N', NULL.

    • In case of 'Y' the ANALYZE_INVALID_FOREIGN_KEYS result table will be temporary table. The temporary table won't be visible for other sessions. You can execute multiple ANALYZE_INVALID_FOREIGN_KEYS(...) stored procedure parallelly with temporary result table.
    • But if you are interested in the partial result from an other session, then you must use 'N', then execute SELECT * FROM INVALID_FOREIGN_KEYS; from an other session.

    • You must use NULL to skip result table creation in transaction, because MySQL executes implicit commit in transaction for CREATE TABLE ... and DROP TABLE ..., so the creation of result table would cause problem in transaction. In this case you must create the result table yourself out of BEGIN; COMMIT/ROLLBACK; block:

      CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);

      Visit MySQL site about implicit commit: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

The INVALID_FOREIGN_KEYS rows will contain only the name of invalid database, table, column. But you can see the invalid referring rows with the execution of value of INVALID_KEY_SQL column of INVALID_FOREIGN_KEYS if there is any.

This stored procedure will be very fast if there are indexes on the referring columns (aka. foreign index) and on the referred columns (usually primary key).

小开

Thanks for this great answer - this is a very handy tool. Here is a slightly modified version of the procedure that includes SQL in the output table to delete keys with invalid keys - handy for the cases where you have confirmed that these rows are simply orphans from missing/disabled delete cascade rules (and not orphans from primary key changes or other more complex cases).

DELIMITER $$


DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$


CREATE
PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64),
temporary_result_table ENUM('Y', 'N'))


LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA


BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);
DECLARE KEYS_SQL_VAR VARCHAR(1024);


DECLARE done INT DEFAULT 0;


DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;


CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024),
`INVALID_KEY_DELETE_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;


CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024),
`INVALID_KEY_DELETE_SQL` VARCHAR(1024)
);
END IF;




OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;
IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;




SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ',
'ON (REFERRING', '.`', COLUMN_NAME_VAR, '`', ' = ', 'REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE REFERRING', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND REFERRED', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;


EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAME` = COLUMN_NAME_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`REFERENCED_TABLE_SCHEMA` = REFERENCED_TABLE_SCHEMA_VAR,
`REFERENCED_TABLE_NAME` = REFERENCED_TABLE_NAME_VAR,
`REFERENCED_COLUMN_NAME` = REFERENCED_COLUMN_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = CONCAT('SELECT ',
'REFERRING.', '`', COLUMN_NAME_VAR, '` ', 'AS "Invalid: ', COLUMN_NAME_VAR, '", ',
'REFERRING.* ',
@from_part, ';'),
`INVALID_KEY_DELETE_SQL` = CONCAT('DELETE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '` ',
'FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' ',
'LEFT JOIN `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' ',
'ON (', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' = ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ') ',
'WHERE ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', '.`', COLUMN_NAME_VAR, '`', ' IS NOT NULL ',
'AND ', '`', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', '.`', REFERENCED_COLUMN_NAME_VAR, '`', ' IS NULL', ';');
END IF;
DEALLOCATE PREPARE stmt;


END LOOP foreign_key_cursor_loop;
END$$


DELIMITER ;


CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;


SELECT * FROM INVALID_FOREIGN_KEYS;
小开

The same check but for invalid UNIQUE keys analysis:

--> Small bug/feature: It will report duplicate nulls also. (While mysql allows duplicate nulls).

DELIMITER $$


DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS$$


CREATE
PROCEDURE `ANALYZE_INVALID_UNIQUE_KEYS`(
checked_database_name VARCHAR(64),
checked_table_name VARCHAR(64))


LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA


BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAMES_VAR VARCHAR(1000);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);


DECLARE done INT DEFAULT 0;


DECLARE unique_key_cursor CURSOR FOR
select kcu.table_schema sch,
kcu.table_name tbl,
group_concat(kcu.column_name) colName,
kcu.constraint_name constName
from
information_schema.table_constraints tc
join
information_schema.key_column_usage kcu
on
kcu.constraint_name=tc.constraint_name
and kcu.constraint_schema=tc.constraint_schema
and kcu.table_name=tc.table_name
where
kcu.table_schema like checked_database_name
and kcu.table_name like checked_table_name
and tc.constraint_type="UNIQUE" group by sch, tbl, constName;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


DROP TEMPORARY TABLE IF EXISTS INVALID_UNIQUE_KEYS;
CREATE TEMPORARY TABLE INVALID_UNIQUE_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAMES` VARCHAR(1000),
`CONSTRAINT_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT
);






OPEN unique_key_cursor;
unique_key_cursor_loop: LOOP
FETCH unique_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAMES_VAR,
CONSTRAINT_NAME_VAR;
IF done THEN
LEAVE unique_key_cursor_loop;
END IF;


SET @from_part = CONCAT('FROM (SELECT COUNT(*) counter FROM', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`',
' GROUP BY ', COLUMN_NAMES_VAR , ') as s where s.counter > 1');
SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' INTO @invalid_key_count;');
PREPARE stmt FROM @full_query;
EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_UNIQUE_KEYS
SET
`TABLE_SCHEMA` = TABLE_SCHEMA_VAR,
`TABLE_NAME` = TABLE_NAME_VAR,
`COLUMN_NAMES` = COLUMN_NAMES_VAR,
`CONSTRAINT_NAME` = CONSTRAINT_NAME_VAR,
`INVALID_KEY_COUNT` = @invalid_key_count;
END IF;
DEALLOCATE PREPARE stmt;


END LOOP unique_key_cursor_loop;
END$$


DELIMITER ;


CALL ANALYZE_INVALID_UNIQUE_KEYS('%', '%');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_UNIQUE_KEYS;


SELECT * FROM INVALID_UNIQUE_KEYS;
小开

I modified the script to handle multiple columns foreign keys.

DELIMITER $$


DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS$$


CREATE PROCEDURE `ANALYZE_INVALID_FOREIGN_KEYS`(
IN `checked_database_name` VARCHAR(64),
IN `checked_table_name` VARCHAR(64),
IN `temporary_result_table` ENUM('Y', 'N')
)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA


BEGIN
DECLARE TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE TABLE_NAME_VAR VARCHAR(64);
DECLARE COLUMN_NAME_VAR VARCHAR(64);
DECLARE CONSTRAINT_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_SCHEMA_VAR VARCHAR(64);
DECLARE REFERENCED_TABLE_NAME_VAR VARCHAR(64);
DECLARE REFERENCED_COLUMN_NAME_VAR VARCHAR(64);


DECLARE done INT DEFAULT 0;


DECLARE foreign_key_cursor CURSOR FOR
SELECT
`TABLE_SCHEMA`,
`TABLE_NAME`,
`COLUMN_NAME`,
`CONSTRAINT_NAME`,
`REFERENCED_TABLE_SCHEMA`,
`REFERENCED_TABLE_NAME`,
`REFERENCED_COLUMN_NAME`
FROM
information_schema.KEY_COLUMN_USAGE
WHERE
`CONSTRAINT_SCHEMA` LIKE checked_database_name AND
`TABLE_NAME` LIKE checked_table_name AND
`REFERENCED_TABLE_SCHEMA` IS NOT NULL;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


IF temporary_result_table = 'N' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;


CREATE TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
ELSEIF temporary_result_table = 'Y' THEN
DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;


CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(
`TABLE_SCHEMA` VARCHAR(64),
`TABLE_NAME` VARCHAR(64),
`COLUMN_NAME` VARCHAR(64),
`CONSTRAINT_NAME` VARCHAR(64),
`REFERENCED_TABLE_SCHEMA` VARCHAR(64),
`REFERENCED_TABLE_NAME` VARCHAR(64),
`REFERENCED_COLUMN_NAME` VARCHAR(64),
`INVALID_KEY_COUNT` INT,
`INVALID_KEY_SQL` VARCHAR(1024)
);
END IF;


SET @prev_constraint_name = '';
SET @prev_table_schema = '';
SET @prev_table_name = '';
SET @prev_referenced_table_schema = '';
SET @prev_referenced_table_name = '';


SET @from_part = '';
SET @where_part = '';
SET @where_nullable = '';


SET @all_columns = '';
SET @all_referenced_columns = '';


OPEN foreign_key_cursor;
foreign_key_cursor_loop: LOOP
FETCH foreign_key_cursor INTO
TABLE_SCHEMA_VAR,
TABLE_NAME_VAR,
COLUMN_NAME_VAR,
CONSTRAINT_NAME_VAR,
REFERENCED_TABLE_SCHEMA_VAR,
REFERENCED_TABLE_NAME_VAR,
REFERENCED_COLUMN_NAME_VAR;


IF done THEN
LEAVE foreign_key_cursor_loop;
END IF;


IF (@prev_constraint_name <> CONSTRAINT_NAME_VAR AND @from_part <> '' AND @where_part <> '') THEN


SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
PREPARE stmt FROM @full_query;


EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = @prev_table_schema,
`TABLE_NAME` = @prev_table_name,
`COLUMN_NAME` = @all_columns,
`CONSTRAINT_NAME` = @prev_constraint_name,
`REFERENCED_TABLE_SCHEMA` = @prev_referenced_table_schema,
`REFERENCED_TABLE_NAME` = @prev_table_name,
`REFERENCED_COLUMN_NAME` = @all_referenced_columns,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = @invalid_query;
END IF;
DEALLOCATE PREPARE stmt;


SET @where_part = '';
SET @where_nullable = '';


SET @all_columns = '';
SET @all_referenced_columns = '';
END IF;


IF (LENGTH(@where_part) > 0) THEN
SET @where_nullable = CONCAT(@where_nullable, ' OR ');
SET @where_part = CONCAT(@where_part, ' AND ');


SET @all_columns = CONCAT(@all_columns, ', ' COLUMN_NAME_VAR);
SET @all_referenced_columns = CONCAT(@all_referenced_columns, ', ' REFERENCED_COLUMN_NAME_VAR);
ELSE
SET @from_part = CONCAT('FROM ', '`', TABLE_SCHEMA_VAR, '`.`', TABLE_NAME_VAR, '`', ' AS REFERRING ');
SET @from_where_part = CONCAT('NOT EXISTS (SELECT * FROM `', REFERENCED_TABLE_SCHEMA_VAR, '`.`', REFERENCED_TABLE_NAME_VAR, '`', ' AS REFERRED ');


SET @all_columns = COLUMN_NAME_VAR;
SET @all_referenced_columns = REFERENCED_COLUMN_NAME_VAR;
END IF;


SET @where_nullable = CONCAT(@where_nullable, 'REFERRING.', COLUMN_NAME_VAR, ' IS NOT NULL');
SET @where_part = CONCAT(@where_part, 'REFERRING.', COLUMN_NAME_VAR, ' = ', 'REFERRED.', REFERENCED_COLUMN_NAME_VAR);


SET @prev_constraint_name = CONSTRAINT_NAME_VAR;
SET @prev_table_schema = TABLE_SCHEMA_VAR;
SET @prev_table_name = TABLE_NAME_VAR;
SET @prev_referenced_table_schema = REFERENCED_TABLE_SCHEMA_VAR;
SET @prev_referenced_table_name = REFERENCED_TABLE_NAME_VAR;


END LOOP foreign_key_cursor_loop;


IF (@where_part <> '' AND @from_part <> '') THEN


SET @full_query = CONCAT('SELECT COUNT(*) ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ') INTO @invalid_key_count;');
SET @invalid_query = CONCAT('SELECT * ', @from_part, ' WHERE (', @where_nullable , ') AND ', @from_where_part, 'WHERE ', @where_part, ')');
PREPARE stmt FROM @full_query;


EXECUTE stmt;
IF @invalid_key_count > 0 THEN
INSERT INTO
INVALID_FOREIGN_KEYS
SET
`TABLE_SCHEMA` = @prev_table_schema,
`TABLE_NAME` = @prev_table_name,
`COLUMN_NAME` = @all_columns,
`CONSTRAINT_NAME` = @prev_constraint_name,
`REFERENCED_TABLE_SCHEMA` = @prev_referenced_table_schema,
`REFERENCED_TABLE_NAME` = @prev_table_name,
`REFERENCED_COLUMN_NAME` = @all_referenced_columns,
`INVALID_KEY_COUNT` = @invalid_key_count,
`INVALID_KEY_SQL` = @invalid_query;
END IF;
DEALLOCATE PREPARE stmt;
END IF;
END$$


DELIMITER ;


CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;


SELECT * FROM INVALID_FOREIGN_KEYS;

提交答案