如何检测和修复 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中文网其他相关文章！