the

the

关注
发信
关注(28)粉丝(399)

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

本文介绍了强制InnoDB重新检查表/表上的外键？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一组 InnoDB 表，我通过删除一些行并插入其他行来定期维护这些表。有几个表具有引用其他表的外键约束，所以这意味着表加载顺序是重要的。为了插入新行而不用担心表的顺序，我使用：
$ b $ pre $ SET $ FOREIGN_KEY_CHECKS = 0;

之前，然后： 

  SET FOREIGN_KEY_CHECKS = 1;

之后。

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

作为一个测试，我输入的数据，我肯定是违反了外键约束，并重新启用如果我试图找到一种方法来指定表加载顺序，并留下外键检查期间的外键检查，MySQL产生没有警告或错误。

加载过程中，这不会允许我加载一个有自引用外键约束的表中的数据，所以这不会是一个可接受的解决方案。



任何强制InnoDB验证表或数据库的外键约束的方法？解决方案

DELIMITER $$

删除程序如果存在ANALYZE_INVALID_FOREIGN_KEYS $$

CREATE
程序`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`，$ b $`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`不是NULL;

声明未找到的继续处理程序集done = 1;

IF temporary_result_table ='N'THEN
如果存在，则删除临时表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），$ b $`REFERENCED_TABLE_NAME` VARCHAR（64），$ b $`REFERENCED_COLUMN_NAME` VARCHAR（64），
`INVALID_KEY_COUNT` INT，
`INVALID_KEY_SQL` VARCHAR（1024）
）;
ELSEIF temporary_result_table ='Y'THEN
如果存在，删除临时表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），$ （64），$ b $ REFERENCE_TABLE_SCHEMA，VARCHAR b`INVALID_KEY_COUNT` INT，
`INVALID_KEY_SQL` VARCHAR（1024）
）;
END IF;


打开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;
如果已完成
则退出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'，'。''，C​​OLUMN_NAME_VAR，'`'，'='，' REFERRED'，'。''，REFERENCED_COLUMN_NAME_VAR，'''，'）'，
'WHERE REFERRING'，'。''，C​​OLUMN_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;'）;
PREFARE 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，$ b $`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'无效：'，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程序如果存在ANALYZE_INVALID_FOREIGN_KEYS;

SELECT * FROM INVALID_FOREIGN_KEYS;

您可以使用此存储过程来检查所有数据库中的无效外键。
结果将被加载到 INVALID_FOREIGN_KEYS 表中。
ANALYZE_INVALID_FOREIGN_KEYS 的参数：
$ b


  1. 数据库名称模式（LIKE样式）

  2. 表名模式（LIKE样式）

  3. 结果是暂时的。它可以是：'Y'，'N'， NULL $ b


    • 如果'Y' ANALYZE_INVALID_FOREIGN_KEYS 结果表将是临时表。
      临时表对于其他会话将不可见。
      您可以使用临时结果表并行执行多个 ANALYZE_INVALID_FOREIGN_KEYS（...）存储过程。
    • 但是，如果您对另一个会话的部分结果感兴趣，则必须使用'N'，然后执行 SELECT * FROM INVALID_FOREIGN_KEYS;

    • 您必须使用 NULL 跳过事务中的结果表创建，因为MySQL执行隐式提交在 CREATE TABLE ... 和 DROP TABLE ... 的事务中，所以创建结果表会导致问题在交易中。在这种情况下，您必须从 BEGIN;中自行创建结果表。 COMMIT / ROLLBACK; block： 

        CREATE TABLE INVALID_FOREIGN_KEYS（
      `TABLE_SCHEMA` VARCHAR （64），
      `TABLE_NAME` VARCHAR（64），
      `COLUMN_NAME` VARCHAR（64），$ b $``CONSTRAINT_NAME` VARCHAR（64），$ b $`REFERENCED_TABLE_SCHEMA` VARCHAR（64） ，
      `REFERENCED_TABLE_NAME` VARCHAR（64），$ b $`REFERENCED_COLUMN_NAME` VARCHAR（64），
      `INVALID_KEY_COUNT` INT，
      `INVALID_KEY_SQL` VARCHAR（1024）
      ） ;

      访问有关隐式提交的MySQL网站：



INVALID_FOREIGN_KEYS 只有无效的数据库，表，列的名称。但是如果有的话，你可以看到执行 INVALID_KEY_SQL 列的 INVALID_FOREIGN_KEYS 的无效引用行。 / p>

如果引用列（也就是foreign index）和引用的列（通常是主键）上有索引，这个存储过程将非常快。


I have a set of InnoDB tables that I periodically need to maintain by removing some rows and inserting others. Several of the tables have foreign key constraints referencing other tables, so this means that the table loading order is important. To insert the new rows without worrying about the order of the tables, I use:

SET FOREIGN_KEY_CHECKS=0;

before, and then:

SET FOREIGN_KEY_CHECKS=1;

after.

When the loading is complete, I'd like to check that the data in the updated tables still hold referential integrity--that the new rows don't break foreign key constraints--but it seems that there's no way to do this.

As a test, I entered data that I was sure violated foreign key constraints, and upon re-enabling the foreign key checks, mysql produced no warnings or errors.

If I tried to find a way to specify the table loading order, and left the foreign key checks on during the loading process, this would not allow me to load data in a table that has a self-referencing foreign key constraint, so this would not be an acceptable solution.

Is there any way to force InnoDB to verify a table's or a database's foreign key constraints?

解决方案 
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).

这篇关于强制InnoDB重新检查表/表上的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

09-17 01:01
Powered by LMLPHP ©2024 the 0.005243