强制InnoDB重新检查一个或多个表上的外键？

小编典典

强制InnoDB重新检查一个或多个表上的外键？

mysql

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

SET FOREIGN_KEY_CHECKS=0;

之前，然后：

SET FOREIGN_KEY_CHECKS=1;

后。

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

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

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

有什么方法可以强制InnoDB验证表或数据库的外键约束？

阅读 260

2020-05-17

共1个答案

小编典典

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的执行。

如果引用列（又称外部索引）和引用列（通常是主键）上都有索引，则此存储过程将非常快。

2020-05-17