Wie erzwinge ich Fremdschlüsselprüfungen in InnoDB-Tabellen, wenn „SET FOREIGN_KEY_CHECKS=1' keine Warnungen oder Fehler auslöst?

So erzwingen Sie Fremdschlüsselprüfungen in InnoDB-Tabellen

Problem

Sie verwalten eine Reihe von InnoDB-Tabellen, die regelmäßig aktualisiert werden und Zeilen gelöscht werden und Einfügen. Für diese Tabellen gelten Fremdschlüsseleinschränkungen, weshalb die Reihenfolge beim Laden der Tabelle von entscheidender Bedeutung ist. Um Probleme mit Fremdschlüsseleinschränkungen zu vermeiden, müssen Sie Fremdschlüsselprüfungen deaktivieren und wieder aktivieren (SET FOREIGN_KEY_CHECKS=0 und SET FOREIGN_KEY_CHECKS=1). Sie stellen jedoch fest, dass das Laden von Daten bei deaktivierten Prüfungen keine Warnungen oder Fehler auslöst, wenn Sie sie erneut aktivieren, obwohl absichtlich gegen Fremdschlüsselregeln verstoßen wird.

Lösung

InnoDB bietet keine eine direkte Möglichkeit, eine Fremdschlüsselprüfung zu erzwingen, aber es gibt Problemumgehungen.

Verwendung einer gespeicherten Prozedur

Ein Ansatz besteht darin, eine gespeicherte Prozedur (ANALYZE_INVALID_FOREIGN_KEYS) zu erstellen, die alle Fremdschlüssel in einer Datenbank untersucht und identifiziert diejenigen, gegen die verstoßen wird. Die Prozedur durchläuft Fremdschlüssel, generiert Abfragen zur Prüfung auf ungültige Referenzen und speichert die Ergebnisse in einer temporären Tabelle.

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 ;

Um die Prozedur zu verwenden, geben Sie drei Parameter an:

1. Das zu überprüfende Datenbanknamenmuster (checked_database_name) (z. B. % für alle Datenbanken)
2. Das zu überprüfende Tabellennamenmuster (checked_table_name) (z. B. % für alle Tabellen)
3. Ob das Ergebnis Tabelle sollte temporär sein (temporary_result_table) (Y für temporär, N für permanent)

Die Prozedur lädt die Ergebnisse in eine temporäre oder permanente Tabelle mit dem Namen INVALID_FOREIGN_KEYS. Diese Tabelle enthält Informationen über ungültige Fremdschlüssel, einschließlich Tabellenschema, Tabellenname, Spaltenname, Einschränkungsname, referenziertes Tabellenschema, referenzierter Tabellenname, referenzierter Spaltenname, Anzahl ungültiger Schlüssel und eine SQL-Abfrage zum Abrufen der ungültigen Zeilen.

Verwendungsbeispiel

So prüfen Sie alle Datenbanken auf ungültige Fremdschlüssel:

CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y');

So zeigen Sie die Ergebnisse an:

SELECT * FROM INVALID_FOREIGN_KEYS;

So rufen Sie die ab Wenn Sie ungültige Zeilen für einen bestimmten Fremdschlüssel haben, führen Sie die entsprechende INVALID_KEY_SQL-Abfrage aus der Tabelle INVALID_FOREIGN_KEYS aus.

Hinweis

Dieser Ansatz erfordert Indizes sowohl für die verweisenden als auch für die referenzierten Spalten für eine optimale Leistung.

Das obige ist der detaillierte Inhalt vonWie erzwinge ich Fremdschlüsselprüfungen in InnoDB-Tabellen, wenn „SET FOREIGN_KEY_CHECKS=1' keine Warnungen oder Fehler auslöst?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!