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;您可以使用此存储过程来检查所有数据库中是否有无效的外键。结果将被加载到
INVALID_FOREIGN_KEYS表中。参数
ANALYZE_INVALID_FOREIGN_KEYS：
- 数据库名称模式（LIKE样式）
- 表名模式（LIKE样式）
结果是否将是暂时的。它可以是：
'Y'
，'N'
，NULL
。- 在的情况下，
'Y'
该ANALYZE_INVALID_FOREIGN_KEYS
结果表会临时表。临时表对于其他会话将不可见。您可以ANALYZE_INVALID_FOREIGN_KEYS(...)
与临时结果表并行执行多个存储过程。 - 但是，如果您对来自另一个会话的部分结果感兴趣，则必须使用
'N'
，然后SELECt * FROM INVALID_FOREIGN_KEYS;
从另一个会话执行。 - 您必须使用
NULL
跳过事务中结果表的创建，因为MySQL在CREATE TABLE ...
和中在事务中执行隐式提交DROP TABLE ...
，因此结果表的创建将在事务中引起问题。在这种情况下，您必须自己创建结果表BEGIN; COMMIT/ROLLBACK;
：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)
);
- 在的情况下，
访问有关隐式提交的MySQL网站：http : //dev.mysql.com/doc/refman/5.6/en/implicit-
commit.html
这些
INVALID_FOREIGN_KEYS行将仅包含无效数据库，表，列的名称。但是您会看到无效的引用行
INVALID_KEY_SQL以及
INVALID_FOREIGN_KEYS是否存在column的value的执行。
如果引用列（又称外部索引）和引用列（通常是主键）上都有索引，则此存储过程将非常快。
