當「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中文網其他相關文章！