MySQL 存儲過程刪除關聯表數據：外鍵約束與事務

表結構假設

CREATE TABLE A (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE B (
    id INT PRIMARY KEY,
    a_id INT,
    data VARCHAR(100),
    FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE CASCADE -- 關鍵：級聯刪除
);

方案一：外鍵級聯刪除 (推薦，最簡單)

定義外鍵時指定 ON DELETE CASCADE，刪除 A 表記錄時自動刪除 B 表關聯記錄。

DELIMITER //
CREATE PROCEDURE DeleteA(IN p_id INT)
BEGIN
    DELETE FROM A WHERE id = p_id; -- B 表自動級聯刪除
END //
DELIMITER ;

方案二：存儲過程中顯式事務 (無級聯或需額外邏輯)

DELIMITER //
CREATE PROCEDURE DeleteA_Explicit(IN p_id INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新拋出異常
    END;
    
    START TRANSACTION;
    DELETE FROM B WHERE a_id = p_id; -- 先刪子表
    DELETE FROM A WHERE id = p_id;   -- 再刪父表
    COMMIT;
END //
DELIMITER ;

方案三：軟刪除 (業務常見)

ALTER TABLE A ADD COLUMN is_deleted TINYINT DEFAULT 0;
ALTER TABLE B ADD COLUMN is_deleted TINYINT DEFAULT 0;

CREATE PROCEDURE SoftDeleteA(IN p_id INT)
BEGIN
    UPDATE A SET is_deleted = 1 WHERE id = p_id;
    UPDATE B SET is_deleted = 1 WHERE a_id = p_id;
END

參考資源

• MySQL 存儲過程事務處理詳解
• MySQL 外鍵約束級聯操作

最佳實踐建議

1. **優先使用外鍵級聯**，減少應用層代碼，保證數據一致性。
2. 若業務需保留歷史，採用**軟刪除 + 視圖過濾**。
3. 存儲過程中**必須使用事務**，確保原子性。
4. 大量刪除時分批次 (如每次 1000 行)，避免長事務鎖表。