MySQL 進階操作實戰（下）：預處理、事務、存儲過程與觸發器完全指南

前言

承接上篇，本文深入講解 MySQL 四大進階特性的語法、陷阱與最佳實踐：Prepared Statement（預處理）、Transaction（事務）、Stored Procedure（存儲過程）、Trigger（觸發器）。所有示例基於 InnoDB 引擎（MyISAM 不支援事務）。

一、預處理語句

防止 SQL 注入、提升重複執行效率的利器。

-- 1. 準備
PREPARE stmt FROM 'SELECT * FROM t1 WHERE id > ?';

-- 2. 綁定變數
SET @i = 1;

-- 3. 執行
EXECUTE stmt USING @i;

-- 4. 釋放
DEALLOCATE PREPARE stmt;

注意：預處理語句在 Session 級別有效，斷開連接自動釋放；? 只能用於數據值，不可用於標識符（表名、列名等）。

二、事務控制

SET autocommit = 0;          -- 關閉自動提交
DELETE FROM t1 WHERE id > 4;
SAVEPOINT p1;                -- 設定保存點
DELETE FROM t1;              -- 誤操作
ROLLBACK TO SAVEPOINT p1;    -- 回滾到 p1
-- ROLLBACK;                  -- 回滾到事務起點
COMMIT;                      -- 提交
SET autocommit = 1;          -- 恢復自動提交

核心原則：START TRANSACTION 與 SET autocommit=0 效果等價；SAVEPOINT 僅在事務內有效；DDL（CREATE/DROP/ALTER）會隱式提交當前事務。

三、存儲過程

1. 創建帶循環邏輯的過程

DELIMITER //
CREATE PROCEDURE p1()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100 DO
        INSERT INTO t2(name) VALUES(CONCAT('user', i));
        SET i = i + 1;
    END WHILE;
END//
DELIMITER ;

• DELIMITER // 臨時修改語句結束符，避免過程體內 ; 被誤判斷。
• 變數聲明用 DECLARE，用戶變數用 @var，兩者作用域不同。

2. 調用與檢視

CALL p1();
SHOW PROCEDURE STATUS\G
SHOW CREATE PROCEDURE p1\G

四、觸發器

定義：BEFORE/AFTER + INSERT/UPDATE/DELETE + FOR EACH ROW。

1. 同步插入（BEFORE INSERT）

DELIMITER //
CREATE TRIGGER t1_sync_ins
BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
    INSERT INTO t2(name) VALUES(NEW.name);
END//
DELIMITER ;

2. 級聯刪除（BEFORE DELETE）

DELIMITER //
CREATE TRIGGER t2_sync_del
BEFORE DELETE ON t1 FOR EACH ROW
BEGIN
    DELETE FROM t2 WHERE id = OLD.id;
END//
DELIMITER ;

3. 級聯更新（BEFORE UPDATE）

DELIMITER //
CREATE TRIGGER t3_sync_upd
BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
    UPDATE t2 SET id = NEW.id WHERE id = OLD.id;
END//
DELIMITER ;

4. 刪除觸發器

DROP TRIGGER trigger_name;

五、附錄：TRUNCATE 與 auto_increment 重置

• TRUNCATE TABLE t1;：極速清空表並重置 auto_increment 為 1（不觸發 DELETE 觸發器）。
• 或保留表結構僅重置計數器：

  ALTER TABLE t1 AUTO_INCREMENT = 1;

六、避坑清單