MySQL 外键级联操作"静默"跳过子表触发器——一个活了近 20 年的 Bug

-- 创建父表
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20)
);

-- 创建子表，外键带 ON DELETE CASCADE
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE
);

-- 子表触发器：删除时写审计日志
CREATE TABLE item_audit (
    item_id INT,
    product_name VARCHAR(50),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //
CREATE TRIGGER trg_item_before_delete
BEFORE DELETE ON order_items
FOR EACH ROW
BEGIN
    INSERT INTO item_audit (item_id, product_name)
    VALUES (OLD.id, OLD.product_name);
END//
DELIMITER ;

-- 插入测试数据
INSERT INTO orders VALUES (1, 'pending');
INSERT INTO order_items VALUES (101, 1, 'Widget A');
INSERT INTO order_items VALUES (102, 1, 'Widget B');

-- 直接删除子表行 —— 触发器正常执行
DELETE FROM order_items WHERE id = 101;
-- item_audit 中有记录：101, 'Widget A'

-- 删除父表行，级联删除子表行 —— 触发器不执行！
DELETE FROM orders WHERE id = 1;
-- order_items 中 102 号行被级联删除了
-- 但 item_audit 中没有 102 的记录！触发器被跳过了

-- 改用不带级联的外键
CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(50),
    FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE RESTRICT  -- 或 NO ACTION
);

import pymysql

def delete_order_with_items(conn, order_id):
    """显式删除子表行，确保触发器执行"""
    with conn.cursor() as cur:
        # 先查子表行（可选，用于确认）
        cur.execute(
            "SELECT id FROM order_items WHERE order_id = %s",
            (order_id,)
        )
        item_ids = [row[0] for row in cur.fetchall()]

        # 逐行删除子表 —— 触发器会正常执行
        for item_id in item_ids:
            cur.execute(
                "DELETE FROM order_items WHERE id = %s",
                (item_id,)
            )

        # 再删除父表行
        cur.execute(
            "DELETE FROM orders WHERE id = %s",
            (order_id,)
        )

    conn.commit()

DELIMITER //
CREATE PROCEDURE sp_delete_order(IN p_order_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_item_id INT;

    DECLARE cur CURSOR FOR
        SELECT id FROM order_items WHERE order_id = p_order_id;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_item_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 显式删除，触发器执行
        DELETE FROM order_items WHERE id = v_item_id;
    END LOOP;
    CLOSE cur;

    DELETE FROM orders WHERE id = p_order_id;
END//
DELIMITER ;

-- 调用
CALL sp_delete_order(1);

DELIMITER //
CREATE TRIGGER trg_order_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    -- 在父表触发器中手动处理子表审计
    INSERT INTO item_audit (item_id, product_name)
    SELECT id, product_name
    FROM order_items
    WHERE order_id = OLD.id;
END//
DELIMITER ;

-- 一条查询找出所有有风险的表组合
SELECT
    fk.CONSTRAINT_NAME,
    fk.TABLE_NAME AS child_table,
    fk.REFERENCED_TABLE_NAME AS parent_table,
    fk.DELETE_RULE,
    fk.UPDATE_RULE,
    trg.TRIGGER_NAME,
    trg.ACTION_TIMING,
    trg.EVENT_MANIFESTATION AS trigger_event
FROM information_schema.REFERENTIAL_CONSTRAINTS fk
JOIN information_schema.TRIGGERS trg
    ON trg.EVENT_OBJECT_SCHEMA = fk.CONSTRAINT_SCHEMA
    AND trg.EVENT_OBJECT_TABLE = fk.TABLE_NAME
WHERE fk.DELETE_RULE IN ('CASCADE', 'SET NULL')
   OR fk.UPDATE_RULE = 'CASCADE';