Lors de la manipulation de tables InnoDB avec des contraintes de clé étrangère, il est essentiel de vérifier l'intégrité des données après modifications. Cependant, la simple réactivation des vérifications de clé étrangère peut ne pas suffire à détecter les violations.
Pour éviter les incohérences de données lors du chargement de la table, vous pouvez désactiver temporairement les vérifications de clé étrangère avant d'insérer nouvelles lignes et réactivez-les une fois le processus terminé :
SET FOREIGN_KEY_CHECKS=0; -- Insert new rows SET FOREIGN_KEY_CHECKS=1;
Cependant, après avoir réactivé les vérifications de clés étrangères, il peut encore y avoir des violations non détectées en raison de la nature différée de la vérification des contraintes dans InnoDB. Pour détecter et résoudre ces problèmes de manière proactive, vous pouvez utiliser la procédure stockée suivante :
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 ;
Pour utiliser la procédure stockée, exécutez-la avec les noms de base de données et de table souhaités :
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y'); DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS; SELECT * FROM INVALID_FOREIGN_KEYS;
La table INVALID_FOREIGN_KEYS contiendra les détails de toutes les clés étrangères non valides, y compris les tables, colonnes référencées et référencées et une requête SQL qui peut être utilisé pour identifier les lignes spécifiques en infraction.
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!