《MySQL必知必會》閱讀筆記第 4 章：存儲過程、游標、觸發器、事務、備份與維護

存儲過程實戰範例：級聯刪除文章

CREATE PROCEDURE sp_delete_article_by_id(IN aid INT)
BEGIN
    DECLARE temp INT;

    -- 刪除評論
    SELECT COUNT(*) FROM gk_reply_article WHERE a_id = aid INTO temp;
    IF temp <> 0 THEN
        DELETE FROM gk_reply_article WHERE a_id = aid;
    END IF;

    -- 刪除點讚
    SELECT COUNT(*) FROM gk_like_article WHERE a_id = aid INTO temp;
    IF temp <> 0 THEN
        DELETE FROM gk_like_article WHERE a_id = aid;
    END IF;

    -- 刪除文章本體
    SELECT COUNT(*) FROM gk_article WHERE id = aid INTO temp;
    IF temp <> 0 THEN
        DELETE FROM gk_article WHERE id = aid;
    END IF;
END

游標使用流程

CREATE PROCEDURE processorders()
BEGIN
    DECLARE o INT;
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
    OPEN ordernumbers;
    FETCH ordernumbers INTO o;
    -- 迴圈處理...
    CLOSE ordernumbers;
END

隱含關閉：達到 END 自動關閉，但建議顯式 CLOSE。

觸發器四要素

1. 唯一名稱
2. 關聯表
3. 觸發事件：INSERT、UPDATE、DELETE
4. 觸發時機：BEFORE 或 AFTER

僅支援表，不支援視圖。

事務處理

確保批次操作原子性：要麼全成功，要麼全回滾。

START TRANSACTION;
INSERT ...; UPDATE ...; DELETE ...;
COMMIT; -- 或 ROLLBACK;

配置與效能監控

• SHOW VARIABLES; 查看當前設定。
• SHOW STATUS; 查看狀態計數器。
• SHOW PROCESSLIST; 顯示活躍執行緒，KILL id 終止長查詢。

備份策略

mysqldump 邏輯備份

# 全庫
mysqldump -u user -p db_name > backup.sql

# 單表
mysqldump -u user -p db_name tbl_name > backup.sql

# 只結構
mysqldump -u user -p -d --add-drop-table db_name > struct.sql

# 遠端備份
mysqldump -u user -p -h 192.168.1.2 db_name > /backup/db.sql

mysqlhotcopy 物理備份 (僅 MyISAM)

mysqlhotcopy db_name /backup/dir/

備份前刷新表

FLUSH TABLES;

表維護指令

ANALYZE TABLE gk_article;   -- 更新統計資訊
CHECK TABLE gk_article;     -- 檢查錯誤
REPAIR TABLE gk_article;    -- 修復 (MyISAM)

字元集與校對查看

SHOW CHARACTER SET;
SHOW COLLATION;

預設埠號

3306