MySQL 自定義函數開發全攻略：語法、流程控制、變量、實戰案例與內建函數速查

MySQL 儲存函數（Stored Function）可封裝可重用邏輯，減少網路往返與應用層代碼。本文系統講解建立、管理、流程控制、變量使用，並給出「生成學號」「隨機姓名」兩個實戰案例，附贈常用內建函數速查表。

一、函數建立與管理語法

特性選項：DETERMINISTIC（確定性）、NO SQL/READS SQL DATA/MODIFIES SQL DATA、SQL SECURITY {DEFINER|INVOKER}。

二、流程控制結構

1. IF-ELSEIF-ELSE

IF condition THEN stmts;
[ELSEIF condition THEN stmts;] ...
[ELSE stmts;]
END IF;

2. CASE

CASE case_value
  WHEN when_value THEN stmts;
  [WHEN ...] ...
  [ELSE stmts;]
END CASE;

3. WHILE 迴圈

[label:] WHILE condition DO stmts; END WHILE [label];

• LEAVE label：跳出指定標籤迴圈（相當 break）。
• ITERATE label：進入下一次迴圈（相當 continue）。

4. REPEAT / LOOP

REPEAT stmts; UNTIL condition END REPEAT;
[label:] LOOP stmts; END LOOP [label];

三、變量聲明與賦值

賦值方式：SET var = expr; 或 SELECT col INTO var FROM ...;

四、實戰案例

案例 1：按班級生成遞增學號（格式：班級代碼+3位流水）

DROP FUNCTION IF EXISTS gen_stu_no;
DELIMITER $$
CREATE FUNCTION gen_stu_no(p_class_id INT) RETURNS CHAR(10)
BEGIN
  DECLARE v_last_no CHAR(10);
  DECLARE v_class_code CHAR(10);
  
  -- 取該班最新學號
  SELECT stu_no INTO v_last_no
  FROM join_student
  WHERE class_id = p_class_id
  ORDER BY stu_no DESC LIMIT 1;
  
  IF v_last_no IS NULL THEN
    -- 首位學生：取班級代碼 + 001
    SELECT c_name INTO v_class_code FROM join_class WHERE id = p_class_id;
    RETURN CONCAT(v_class_code, '001');
  ELSE
    -- 遞增：保留前 7 位（班級代碼），後 3 位 +1 補零
    RETURN CONCAT(LEFT(v_last_no, 7), LPAD(RIGHT(v_last_no, 3) + 1, 3, '0'));
  END IF;
END$$
DELIMITER ;

案例 2：隨機生成中文姓氏+單字名

DROP FUNCTION IF EXISTS rand_cn_name;
DELIMITER $$
CREATE FUNCTION rand_cn_name() RETURNS CHAR(2)
BEGIN
  DECLARE v_surnames CHAR(16) DEFAULT '趙錢孫李周吳鄭王馮陳褚衛蔣沈韓楊';
  DECLARE v_given CHAR(10) DEFAULT '甲乙丙丁戊己庚辛壬癸';
  DECLARE v_name CHAR(2);
  
  SET v_name = CONCAT(
    SUBSTRING(v_surnames, FLOOR(RAND()*16)+1, 1),
    SUBSTRING(v_given, FLOOR(RAND()*10)+1, 1)
  );
  RETURN v_name;
END$$
DELIMITER ;

五、MySQL 常用內建函數速查表

1. 數值函數

ABS(x)           -- 絕對值
CEIL(x)/FLOOR(x) -- 向上/向下取整
ROUND(x,d)       -- 四捨五入保留 d 位
MOD(m,n)         -- 取模
POW(m,n)         -- m^n
SQRT(x)          -- 平方根
RAND()           -- 0~1 隨機數
TRUNCATE(x,d)    -- 截斷 d 位小數（不四捨五入）
FORMAT(x,d)      -- 千分位格式化

2. 日期時間函數

NOW() / CURRENT_TIMESTAMP()  -- 當前日期時間
CURDATE() / CURTIME()        -- 當前日期/時間
DATE(expr) / TIME(expr)      -- 提取日期/時間部分
DATE_FORMAT(date, fmt)       -- 格式化（%Y-%m-%d %H:%i:%s）
UNIX_TIMESTAMP([date])       -- 日期轉時間戳
FROM_UNIXTIME(ts)            -- 時間戳轉日期
DATE_ADD/DATE_SUB            -- 日期加減 INTERVAL

3. 字串函數

LENGTH(str)           -- 字節長度
CHAR_LENGTH(str)      -- 字符長度
SUBSTRING(str, pos, len) -- 截取
REPLACE(str, from, to)  -- 替換
CONCAT(str1, str2, ...) -- 拼接
INSTR(str, substr)      -- 子串首次位置
LOCATE(substr, str, pos) -- 指定位置開始查找
LPAD/RPAD(str, len, pad) -- 左/右填充
LTRIM/RTRIM/TRIM        -- 去空格
LCASE/UCASE             -- 大小寫轉換

4. 流程函數

IF(expr, true_val, false_val)           -- 三元運算
CASE WHEN cond THEN val ... ELSE val END  -- 多分支

5. 聚合函數

COUNT/SUM/MAX/MIN/AVG
GROUP_CONCAT(expr [ORDER BY ...] SEPARATOR ',') -- 分組拼接

6. 加密/資訊函數

MD5(str)           -- MD5 雜湊
VERSION()          -- MySQL 版本
DATABASE()         -- 當前資料庫
USER()             -- 當前用戶
DEFAULT(col)       -- 列預設值

六、最佳實踐提醒

1. 函數內禁止修改表數據（無 INSERT/UPDATE/DELETE 權限），否則報錯 ERROR 1415。
2. 大量計算邏輯優先考慮應用層完成，函數適合輕量、無副作用、高復用場景。
3. 避免在函數中執行耗時查詢，導致調用方 SQL 執行時間不可控。
4. 使用 DETERMINISTIC 宣告確定性函數，優化器可緩存結果。