Rumah > Artikel > pangkalan data > Bagaimana untuk Mengesan dan Memperbaiki Pelanggaran Kunci Asing dalam Jadual InnoDB?
Apabila memanipulasi jadual InnoDB dengan kekangan kunci asing, adalah penting untuk mengesahkan integriti data selepas pengubahsuaian. Walau bagaimanapun, hanya mendayakan semula semakan kunci asing mungkin tidak mencukupi untuk mengesan pelanggaran.
Untuk mengelakkan ketidakkonsistenan data semasa pemuatan jadual, anda boleh melumpuhkan semakan kunci asing buat sementara waktu sebelum memasukkan baris baharu dan dayakannya semula selepas proses selesai:
SET FOREIGN_KEY_CHECKS=0; -- Insert new rows SET FOREIGN_KEY_CHECKS=1;
Walau bagaimanapun, selepas mendayakan semula semakan kunci asing, mungkin masih terdapat pelanggaran yang tidak dapat dikesan disebabkan oleh sifat tertunda semakan kekangan dalam InnoDB. Untuk mengesan dan membetulkan isu ini secara proaktif, anda boleh menggunakan prosedur tersimpan berikut:
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 ;
Untuk menggunakan prosedur tersimpan, laksanakannya dengan pangkalan data dan nama jadual yang dikehendaki:
CALL ANALYZE_INVALID_FOREIGN_KEYS('%', '%', 'Y'); DROP PROCEDURE IF EXISTS ANALYZE_INVALID_FOREIGN_KEYS; SELECT * FROM INVALID_FOREIGN_KEYS;
Jadual INVALID_FOREIGN_KEYS akan mengandungi butiran sebarang kunci asing yang tidak sah, termasuk rujukan dan jadual, lajur dan pertanyaan SQL yang dirujuk yang boleh digunakan untuk mengenal pasti baris yang melanggar tertentu.
Atas ialah kandungan terperinci Bagaimana untuk Mengesan dan Memperbaiki Pelanggaran Kunci Asing dalam Jadual InnoDB?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!