当'SET FOREIGN_KEY_CHECKS=1”无法触发警告或错误时，如何在 InnoDB 表中强制进行外键检查？

如何在 InnoDB 表中强制执行外键检查

问题

您正在管理一组定期更新（涉及行删除）的 InnoDB 表和插入。这些表具有外键约束，因此表加载的顺序至关重要。为了避免外键约束问题，您需要禁用并重新启用外键检查（SET FOREIGN_KEY_CHECKS=0 和 SET FOREIGN_KEY_CHECKS=1）。但是，您发现在禁用检查的情况下加载数据不会在重新启用检查时触发任何警告或错误，尽管故意违反了外键规则。

解决方案

InnoDB 不提供强制外键检查的直接方法，但有解决方法。

使用存储过程

一种方法是创建一个存储过程（ANALYZE_INVALID_FOREIGN_KEYS）来检查数据库中的所有外键并指出任何违规行为。该过程循环外键，生成查询以检查无效引用，并将结果存储在临时表中。

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
        -- ... (procedure implementation)
    END$$

DELIMITER ;

要使用该过程，请提供三个参数：

1. 要检查的数据库名称模式（checked_database_name）（例如，% 表示所有数据库）
2. 要检查的表名称模式（checked_table_name）（例如，% 表示所有表）
3. 结果是否表应该是临时的 (temporary_result_table)（Y 表示临时，N 表示永久）

该过程将结果加载到名为 INVALID_FOREIGN_KEYS 的临时表或永久表中。该表包含有关无效外键的信息，包括表架构、表名称、列名称、约束名称、引用的表架构、引用的表名称、引用的列名称、无效键的计数以及用于检索无效行的 SQL 查询。

使用示例

检查所有数据库中是否存在无效外键：

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');

查看结果：

SELECT * FROM INVALID_FOREIGN_KEYS;

检索对于特定外键的无效行，请从 INVALID_FOREIGN_KEYS 表执行相应的 INVALID_KEY_SQL 查询。

注意

此方法需要在引用列和被引用列上建立索引以获得最佳性能。

以上是当'SET FOREIGN_KEY_CHECKS=1”无法触发警告或错误时，如何在 InnoDB 表中强制进行外键检查？的详细内容。更多信息请关注PHP中文网其他相关文章！