Wie erkennt und behebt man Fremdschlüsselverletzungen in InnoDB-Tabellen?

Erkennen von Fremdschlüsselverletzungen in InnoDB-Tabellen

Beim Bearbeiten von InnoDB-Tabellen mit Fremdschlüsseleinschränkungen ist es wichtig, die Datenintegrität nach Änderungen zu überprüfen. Allerdings reicht es möglicherweise nicht aus, die Fremdschlüsselprüfungen einfach erneut zu aktivieren, um Verstöße zu erkennen.

Konfigurieren der Fremdschlüsselprüfung

Um Dateninkonsistenzen beim Laden der Tabelle zu verhindern, können Sie die Fremdschlüsselprüfungen vor dem Einfügen vorübergehend deaktivieren Erstellen Sie neue Zeilen und aktivieren Sie sie erneut, nachdem der Vorgang abgeschlossen ist:

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

Überprüfen des Fremdschlüssels Integrität

Nach der erneuten Aktivierung der Fremdschlüsselprüfungen kann es jedoch aufgrund der verzögerten Natur der Einschränkungsprüfung in InnoDB immer noch zu unentdeckten Verstößen kommen. Um diese Probleme proaktiv zu erkennen und zu beheben, können Sie die folgende gespeicherte Prozedur verwenden:

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 ;

Verwendung

Um die gespeicherte Prozedur zu verwenden, führen Sie sie mit den gewünschten Datenbank- und Tabellennamen aus:

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');
DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS;

SELECT * FROM INVALID_FOREIGN_KEYS;

Die Tabelle INVALID_FOREIGN_KEYS enthält Details zu allen ungültigen Fremdschlüsseln, einschließlich der verweisenden und referenzierten Tabellen, Spalten und einer SQL-Abfrage kann verwendet werden, um die spezifischen verletzenden Zeilen zu identifizieren.

Das obige ist der detaillierte Inhalt vonWie erkennt und behebt man Fremdschlüsselverletzungen in InnoDB-Tabellen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!