MySQL 存儲過程入門實戰：語法、變量、流程控制、動態 SQL、常用函數速查表

基礎結構

CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param type)
BEGIN
  DECLARE var type [DEFAULT value];
  -- SQL 語句
  -- 流程控制
END

• 分隔符修改：DELIMITER // ... END // DELIMITER ; (避免分號衝突)。
• phpMyAdmin 執行時在「分隔符」欄填 //。

參數模式

完整範例：登入驗證 + 更新登入資訊

CREATE PROCEDURE sp_login(
  IN p_username CHAR(15),
  IN p_password CHAR(32),
  IN p_ip CHAR(18),
  IN p_logintime DATETIME
)
LABEL_PROC: BEGIN
  DECLARE v_uid MEDIUMINT(8);
  DECLARE v_realpass CHAR(32);
  DECLARE v_nickname VARCHAR(30);
  DECLARE v_oltime SMALLINT(6);

  SELECT u.uid, u.password, f.nickname, u.oltime
    INTO v_uid, v_realpass, v_nickname, v_oltime
    FROM cdb_members u
    JOIN cdb_memberfields f ON f.uid = u.uid
   WHERE u.username = p_username;

  IF v_uid IS NULL THEN
    SELECT 2 AS ErrorCode;  -- 用戶不存在
    LEAVE LABEL_PROC;
  END IF;

  IF p_password <> v_realpass THEN
    SELECT 3 AS ErrorCode;  -- 密碼錯誤
    LEAVE LABEL_PROC;
  END IF;

  UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;

  SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
END LABEL_PROC //

• SELECT ... INTO 將查詢結果直接賦給變量。
• LABEL_PROC: + LEAVE LABEL_PROC 替代 RETURN (MySQL 存儲過程無 RETURN)。

動態 SQL (PREPARE/EXECUTE)

CREATE PROCEDURE sp_dynamic(IN p_hash CHAR(40))
BEGIN
  DECLARE v_sql VARCHAR(200);
  SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode = \'', p_hash, '\' LIMIT 1');
  SET @sql = v_sql;           -- 用戶變量
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

• @sql 為用戶會話變量，PREPARE 需要。
• 防注入：生產環境請用 PREPARE stmt FROM ? + EXECUTE stmt USING @param 參數化。

流程控制語法

-- IF
IF condition THEN ... ELSEIF condition THEN ... ELSE ... END IF;

-- WHILE
[label:] WHILE expr DO ... END WHILE [label];

-- LOOP
[label:] LOOP ... END LOOP [label];

-- REPEAT
[label:] REPEAT ... UNTIL expr END REPEAT [label];

-- 跳出標籤區塊
LEAVE label;
ITERATE label;  -- 類似 continue

常用函數速查表

字串

CONCAT(s1,s2...), SUBSTRING(str,pos,len), TRIM(), REPLACE(str,from,to),
LENGTH(), CHAR_LENGTH(), LOCATE(sub,str), LEFT(), RIGHT(), UPPER(), LOWER()

數學

ABS(), CEIL(), FLOOR(), ROUND(n,d), MOD(), POW(), RAND(), SQRT(),
CONV(n,from_base,to_base), HEX(), BIN()

日期時間

NOW(), CURDATE(), CURTIME(), DATE_ADD(d, INTERVAL n UNIT), DATE_SUB(),
DATEDIFF(d1,d2), DATE_FORMAT(d, fmt), STR_TO_DATE(str, fmt),
UNIX_TIMESTAMP(), FROM_UNIXTIME(), EXTRACT(unit FROM d),
LAST_DAY(), DAY(), MONTH(), YEAR(), HOUR(), MINUTE(), SECOND()

管理指令

SHOW PROCEDURE STATUS [LIKE 'pattern'];
SHOW CREATE PROCEDURE proc_name;
DROP PROCEDURE [IF EXISTS] proc_name;

運算子優先級提醒

• := 賦值最低，= 比較/賦值視上下文。
• 邏輯：NOT > AND > OR > XOR。
• 位運算：~ > & > | > <</>>。

結語

存儲過程將業務邏輯下沉資料庫層，減少網路往返、封裝複雜事務。關鍵點：**分隔符管理、IN/OUT/INOUT 參數、SELECT INTO 賦值、LABEL+LEAVE 模擬 RETURN、PREPARE 動態 SQL、參數化防注入**。配合 SHOW PROCEDURE STATUS 管理，構建可維護的資料庫端邏輯層。

1. 上一篇文章： MySQL 服務 1067 錯誤 (進程意外終止) 多維度解決方案彙總
2. 下一篇文章： MySQL 無法啟動/無法停止故障排查：安全配置後常見問題與解決方案

MySQL 資料庫相關知識

mysqlbinlog 版本不匹配導致解析亂碼與報錯：5.6 必須用 3.4+ 版本工具

核心結論 MySQL 5.6 服務器產生的 Binlog，必

MySQL 字符集深度圖解：GBK、UTF8、Latin1 轉換鏈路與亂碼根因

字符集轉換鏈路（客戶端 ↔ 服務端） 客戶端發送數據

MySQL 關聯子查詢優化：用 INNER JOIN 替代 EXISTS，從 2.4 秒降到 0.68 秒

場景：EXISTS 關聯子查詢慢 = 30000 AND t

Infobright：開源 MySQL 列式數據倉庫解決方案深度解析

什麼是 Infobright？ Infobright 是基於