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

Question

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

SET FOREIGN_KEY_CHECKS=0;

之前，然后:

SET FOREIGN_KEY_CHECKS=1;

之后。

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

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

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

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

Answer 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的参数:

1. 数据库名称模式(LIKE 样式)
2. 表格名称模式(LIKE 样式)

3. 结果是否是临时的。可以是:'Y'、'N'、NULL。

   • 如果是 'Y'，ANALYZE_INVALID_FOREIGN_KEYS 结果表将是临时表。临时表对其他 session 不可见。您可以使用临时结果表并行执行多个 ANALYZE_INVALID_FOREIGN_KEYS(...) 存储过程。
   • 但如果您对其他 session 的部分结果感兴趣，那么您必须使用 'N'，然后从其他 session 执行 SELECT * FROM INVALID_FOREIGN_KEYS; .

   • 你必须使用 NULL 在事务中跳过结果表创建，因为 MySQL 在事务中为 CREATE TABLE ... 和 DROP 执行隐式提交TABLE ...，因此创建结果表会导致事务出现问题。在这种情况下，您必须自己从 BEGIN; 创建结果表；提交/回滚； 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)
     );
     

     访问 MySQL 网站以了解隐式提交:http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

INVALID_FOREIGN_KEYS 行将只包含无效数据库、表、列的名称。但是如果有的话，你可以通过执行INVALID_FOREIGN_KEYS的INVALID_KEY_SQL列的值看到无效的引用行。

如果引用列(也称为外部索引)和引用列(通常是主键)上有索引，则此存储过程将非常快。