`SET FOREIGN_KEY_CHECKS=1` が警告またはエラーのトリガーに失敗した場合に、InnoDB テーブルで外部キー チェックを強制する方法

InnoDB テーブルで外部キー チェックを強制する方法

問題

行の削除を伴う定期的な更新を受ける一連の InnoDB テーブルを管理しています。そして挿入。これらのテーブルには外部キー制約があるため、テーブルのロード順序が重要になります。外部キー制約の問題を回避するには、外部キーのチェックを無効にして再度有効にする (SET FOREIGN_KEY_CHECKS=0 および SET FOREIGN_KEY_CHECKS=1) ことに頼ります。ただし、外部キー ルールに意図的に違反しているにもかかわらず、チェックを無効にしてデータをロードすると、再度有効にしたときに警告やエラーが発生しないことがわかります。

解決策

InnoDB は提供しません。外部キーのチェックを強制する直接的な方法ですが、回避策はあります。

ストアド キーの使用プロシージャ

1 つの方法は、データベース内のすべての外部キーを検査し、違反している外部キーを特定するストアド プロシージャ (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 ;

プロシージャを使用するには、次の 3 つのパラメータを指定します。

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 中国語 Web サイトの他の関連記事を参照してください。