MySQL 存儲過程與常用函數完全指南

MySQL 存儲過程與常用函數完全指南

掌握 MySQL 內建函數與存儲過程，能顯著提升 SQL 編寫效率、封裝業務邏輯、減少網路交互。本文系統整理數學、字符串、日期、系統、加密、流程控制六大類函數，並詳細講解存儲過程/函數的創建、調用、管理與優勢。

一、常用內建函數速查

1. 數學函數

技巧： ORDER BY RAND() 可實現隨機排序，但大表慎用（全表掃描）。

2. 字符串函數

3. 日期時間函數

4. 系統/信息函數

• VERSION()：資料庫版本
• CONNECTION_ID()：當前連線 ID
• DATABASE() / SCHEMA()：當前資料庫
• USER() / CURRENT_USER() / SYSTEM_USER()：當前用戶
• CHARSET(str)：字符串字符集
• LAST_INSERT_ID()：最後自增 ID

5. 加密函數

• MD5(str)：MD5 雜湊（32 位十六進制）
• PASSWORD(str)：MySQL 密碼加密算法（注意：5.7.6+ 已棄用，8.0 移除，僅供舊版兼容）

6. 流程控制函數

二、存儲過程與函數詳解

核心優勢

• 效能：預編譯緩存，減少解析優化開銷；批量操作減少網路往返。
• 封裝：將業務邏輯封裝在資料庫層，應用端簡化。
• 安全：通過 DEFINER/INVOKER 權限模型控制訪問；避免直接暴露表結構。
• 維護：修改邏輯只需更新資料庫端，無需重新部署應用。

1. 創建存儲過程

語法結構：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic...]
routine_body

參數模式：IN（輸入）、OUT（輸出）、INOUT（輸入輸出）。

特性：LANGUAGE SQL、DETERMINISTIC（確定性）、READS SQL DATA、MODIFIES SQL DATA、SQL SECURITY {DEFINER|INVOKER}、COMMENT。

範例 1：無參數查詢

DELIMITER //
CREATE PROCEDURE sp_demo()
BEGIN
    SELECT * FROM user;
END //
DELIMITER ;

範例 2：帶輸入輸出參數

DELIMITER //
CREATE PROCEDURE age_from_user(IN user_id INT, OUT user_age INT)
READS SQL DATA
BEGIN
    SELECT age INTO user_age FROM user WHERE id = user_id;
END //
DELIMITER ;

2. 創建存儲函數

函數必須有 RETURNS 子句，體內需包含 RETURN 語句。

DELIMITER //
CREATE FUNCTION username_from_user(user_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
    RETURN (SELECT username FROM user WHERE id = user_id);
END //
DELIMITER ;

3. 調用方式

• 過程：CALL sp_name([params...])。
  例：CALL sp_demo();、CALL age_from_user(1, @age); SELECT @age;
• 函數：在 SQL 表達式中直接調用。
  例：SELECT username_from_user(3);

4. 查看與管理

三、最佳實踐建議

1. 合理使用：複雜業務邏輯、批量數據處理、報表統計適合用存儲過程；簡單計算、格式轉換用函數。
2. 避免過度：不要將所有業務邏輯搬進資料庫，導致難以版本控制、測試、擴展。
3. 參數驗證：過程內部優先驗證輸入參數合法性。
4. 錯誤處理：使用 DECLARE ... HANDLER 捕獲異常，避免事務殘留。
5. 權限最小化：採用 SQL SECURITY DEFINER 並授予調用者僅 EXECUTE 權限。
6. 命名規範：採用統一前綴（如 sp_、fn_）區分業務域。

總結

MySQL 內建函數涵蓋數學、字符串、日期、系統、加密、流程控制等日常開發高頻需求；存儲過程/函數提供了強大的服務端邏輯封裝能力。熟練掌握並遵循最佳實踐，能讓資料庫層承擔更多計算任務，釋放應用層壓力，構建更高效、安全、可維護的系統。