如何偵測和修復 InnoDB 表中的外鍵違規？

偵測 InnoDB 表中的外鍵違規

使用外鍵約束操作 InnoDB 表時，必須在修改後驗證資料完整性。然而，僅重新啟用外鍵檢查可能不足以偵測違規行為。

設定外鍵檢查

為了防止表載入過程中資料不一致，可以在插入之前暫時停用外鍵檢查新行並在過程完成後重新啟用它們：

SET FOREIGN_KEY_CHECKS=0;
-- Insert new rows
SET FOREIGN_KEY_CHECKS=1;

檢查外鍵完整性

但是，重新啟用外鍵檢查後，由於以下原因，可能仍然存在未檢測到的違規情況InnoDB中約束檢查的延遲性質。要主動偵測並修復這些問題，您可以使用以下預存程序：

DELIMITER $$

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 DONE INT DEFAULT 0;
  -- Iterate over foreign keys
  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;

  -- Create temporary result table for invalid foreign keys
  IF temporary_result_table = 'Y' THEN
    DROP TEMPORARY TABLE IF EXISTS INVALID_FOREIGN_KEYS;
    CREATE TEMPORARY TABLE INVALID_FOREIGN_KEYS(...);
  ELSE
    DROP TABLE IF EXISTS INVALID_FOREIGN_KEYS;
    CREATE TABLE INVALID_FOREIGN_KEYS(...);
  END IF;

  -- Check foreign key violations and insert into result table
  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 = "...";
    SET @full_query = "...";
    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` = "...";
    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表將包含任何無效外鍵的詳細信息，包括引用和引用的表、列以及可用於識別特定違規行的 SQL 查詢。

以上是如何偵測和修復 InnoDB 表中的外鍵違規？的詳細內容。更多資訊請關注PHP中文網其他相關文章！