萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 5.1 存儲程序、函數與復制交互完整 FAQ:權限、確定性、時基、錯誤處理

核心結論速覽

問題答案關鍵細節
存儲程序/函數對復制有效嗎?是主庫執行的 DML 事件逐條復制到從庫
CREATE/ALTER/DROP 會復制嗎?是DDL 語句基於語句級復制,雙端結構保持一致
調用語句 (CALL/SELECT func()) 會復制嗎?不僅復制內部產生的 DML 事件,調用本身不記入 Binlog
安全要求?創建者需 SUPER 權限,或開啟 log_bin_trust_routine_creators=1防止從庫執行惡意定義器上下文代碼
非確定性/隨機函數能復制嗎?不能正確復制主從結果不一致,需聲明 DETERMINISTIC 或關閉信任變量
時間基準函數 (NOW/UUID) 能復制嗎?不能精確復制Binlog 僅記錄 DML 事件,無時間上下文
大批量 DML 非事務表部分失敗怎麼辦?主庫部分寫入,從庫無更新函數內 DML 可加 IGNORE 關鍵字忽略錯誤

詳細解析

1. 復制機制:語句級 vs 行級

MySQL 5.1 默認**語句級復制 (SBR)**。存儲程序內的每條 DML 會被單獨記入 Binlog 並重放。行級復制 (RBR) 下,只記錄行變更,存儲程序調用不記錄,但內部 DML 產生的行變更會記錄。

2. 安全約束:定義器上下文風險

從庫以 **定義器 (DEFINER)** 權限執行復制事件。若定義器為高權限用戶,惡意存儲程序可在從庫提權。對策:

  • 強制創建者擁有 SUPER 權限(默認)。
  • 或設置 log_bin_trust_routine_creators=1,允許擁有 CREATE ROUTINE 的用戶創建,**但需嚴格審計代碼**。

3. 確定性聲明與隨機函數陷阱


CREATE FUNCTION rand_num() RETURNS INT
DETERMINISTIC  -- 聲明確定性,實際調用 RAND() 會報錯
BEGIN RETURN FLOOR(RAND()*100); END;
  • 聲明 DETERMINISTIC 但內部調用 RAND()、UUID()、NOW() 會報錯(FUNCTION ... DETERMINISTIC, NO SQL, or READS SQL DATA)。
  • 若必須使用隨機/時間函數,**不聲明 DETERMINISTIC**,並設置 log_bin_trust_routine_creators=1,但**復制結果在從庫將不同於主庫**。

4. 非事務表大批量 DML 部分失敗行為差異

  • 主庫:MyISAM 表 INSERT ... VALUES (1),(2),(3)... 第 2 行失敗,前 1 行已寫入,**不回滾**。
  • 從庫:復制線程執行同樣語句,**遇錯停止**,無任何行寫入 → **主從數據不一致**。
  • 函數/存儲程序內 DML 建議加 IGNORE:INSERT IGNORE ...,主庫跳過錯誤行繼續,從庫同步邏輯一致。

最佳實踐清單

  1. 生產環境**強制啟用 Row-Based Replication (RBR)**:binlog_format=ROW,從根本消除存儲程序復制不一致風險。
  2. 嚴格控制 CREATE ROUTINE 權限,審計所有存儲程序代碼。
  3. 業務邏輯盡量下沉到應用層,數據庫層僅做 CRUD 與簡單聚合。
  4. 若必須用存儲程序,確保**純確定性、無副作用、單一事務邊界**。
copyright © 萬盛學電腦網 all rights reserved