Bagaimana Memaksa Pemeriksaan Kunci Asing dalam Jadual InnoDB Apabila `SET FOREIGN_KEY_CHECKS=1` Gagal Mencetuskan Amaran atau Ralat?

Cara Menguatkuasakan Pemeriksaan Kunci Asing dalam Jadual InnoDB

Isu

Anda sedang menguruskan set jadual InnoDB yang menjalani kemas kini berkala yang melibatkan pemadaman baris dan sisipan. Jadual ini mempunyai kekangan kunci asing, menjadikan susunan pemuatan jadual penting. Untuk mengelakkan isu kekangan kunci asing, anda bergantung pada melumpuhkan dan mendayakan semula semakan kunci asing (SET FOREIGN_KEY_CHECKS=0 dan SET FOREIGN_KEY_CHECKS=1). Walau bagaimanapun, anda mendapati bahawa memuatkan data dengan semakan dilumpuhkan tidak mencetuskan sebarang amaran atau ralat apabila anda mendayakannya semula, walaupun dengan sengaja melanggar peraturan kunci asing.

Penyelesaian

InnoDB tidak menyediakan cara langsung untuk memaksa semakan kunci asing, tetapi terdapat penyelesaian.

Menggunakan Stored Prosedur

Satu pendekatan ialah mencipta prosedur tersimpan (ANALYZE_INVALID_FOREIGN_KEYS) yang memeriksa semua kunci asing dalam pangkalan data dan mengenal pasti mana-mana yang dilanggar. Prosedur menggelung melalui kunci asing, menjana pertanyaan untuk menyemak rujukan tidak sah dan menyimpan keputusan dalam jadual sementara.

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 ;

Untuk menggunakan prosedur, sediakan tiga parameter:

1. Corak nama pangkalan data (checked_database_name) untuk diperiksa (cth., % untuk semua pangkalan data)
2. Nama jadual corak (nama_jadual_ditandai) untuk menyemak (cth., % untuk semua jadual)
3. Sama ada jadual keputusan harus bersifat sementara (jadual_hasil_sementara) (Y untuk sementara, N untuk kekal)

Prosedur memuatkan keputusan ke dalam jadual sementara atau kekal bernama INVALID_FOREIGN_KEYS. Jadual ini mengandungi maklumat tentang kunci asing yang tidak sah, termasuk skema jadual, nama jadual, nama lajur, nama kekangan, skema jadual yang dirujuk, nama jadual yang dirujuk, nama lajur yang dirujuk, kiraan kekunci tidak sah dan pertanyaan SQL untuk mendapatkan semula baris yang tidak sah.

Contoh Penggunaan

Untuk menyemak kunci asing yang tidak sah dalam semua pangkalan data:

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

Untuk melihat keputusan:

SELECT * FROM INVALID_FOREIGN_KEYS;

Untuk mendapatkan semula baris yang tidak sah untuk kunci asing tertentu, laksanakan pertanyaan INVALID_KEY_SQL yang sepadan daripada jadual INVALID_FOREIGN_KEYS.

Nota

Pendekatan ini memerlukan indeks pada kedua-dua lajur yang merujuk dan dirujuk untuk prestasi optimum.

Atas ialah kandungan terperinci Bagaimana Memaksa Pemeriksaan Kunci Asing dalam Jadual InnoDB Apabila `SET FOREIGN_KEY_CHECKS=1` Gagal Mencetuskan Amaran atau Ralat?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!