MySQL 觸發器與存儲過程完整測試指南：從建表到驗證

實驗目標

通過一個完整的「用戶註冊 → 觸發器記錄操作日誌 → 存儲過程批量造數」流程，驗證觸發器與存儲過程的正確性與交互行為。

1. 建立業務表與日誌表

-- 業務表
CREATE TABLE `airuser` (
  `userId` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 操作日誌表
CREATE TABLE `airuser_record` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) DEFAULT NULL,
  `edittime` TIMESTAMP NULL DEFAULT NULL,
  `edittype` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 創建 BEFORE INSERT 觸發器

DROP TRIGGER IF EXISTS insert_trigger;
DELIMITER //
CREATE TRIGGER insert_trigger 
BEFORE INSERT ON airuser
FOR EACH ROW
BEGIN
    INSERT INTO airuser_record (username, edittime, edittype)
    VALUES (NEW.username, NOW(), 'insert');
END//
DELIMITER ;

-- 查看觸發器
SHOW TRIGGERS;

關鍵點：
- BEFORE INSERT 保證日誌寫入與主表在同一事務，若主表插入回滾，日誌自動回滾。
- NEW.username 訪問即將插入的值。
- DELIMITER // 避免分號衝突。

3. 編寫批量插入存儲過程

DROP PROCEDURE IF EXISTS createUsers;
DELIMITER //
CREATE PROCEDURE createUsers(IN cnt INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < cnt DO
        INSERT INTO airuser (username) VALUES (CONCAT('user_', i));
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

SHOW PROCEDURE STATUS WHERE Name = 'createUsers';

4. 執行與驗證

-- 調用存儲過程插入 10 條
CALL createUsers(10);

-- 檢查業務表
SELECT * FROM airuser;

-- 檢查日誌表（應同步產生 10 條 insert 記錄）
SELECT * FROM airuser_record;

預期結果

進階測試建議

• 事務回滾驗證：在存儲過程中手動 ROLLBACK，確認日誌表無殘留。
• 併發壓測：多線程並發 CALL createUsers(1000)，觀察 airuser_record 行數是否精確等於總插入數。
• 異常處理：觸發器中添加 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 記錄錯誤而不中斷主流程。
• 性能基準：對比「存儲過程批量插」vs「應用層循環插」的 TPS 差異，通常前者快 3-5 倍（減少網絡往返）。

總結

本文提供從 0 到 1 的完整可運行腳本，涵蓋觸發器創建、存儲過程編寫、調用驗證全流程，可直接作為項目模板或教學案例。