Rumah  >  Artikel  >  pangkalan data  >  Bagaimana untuk Mengesan dan Memperbaiki Pelanggaran Kunci Asing dalam Jadual InnoDB?

Bagaimana untuk Mengesan dan Memperbaiki Pelanggaran Kunci Asing dalam Jadual InnoDB?

Linda Hamilton
Linda Hamiltonasal
2024-11-10 07:00:02628semak imbas

How to Detect and Fix Foreign Key Violations in InnoDB Tables?

Mengesan 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.

Mengkonfigurasi Pemeriksaan Kunci Asing

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;

Menyemak Kunci Asing Integriti

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 ;

Penggunaan

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!

Kenyataan:
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn