MySQL 5 存儲過程與函數實戰：語法、調試技巧與加密算法示例

基礎語法

創建存儲過程

CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param_name type, ...)
[characteristic ...]
BEGIN
  -- SQL 語句塊
END

創建函數

CREATE FUNCTION func_name (param_name type, ...)
RETURNS return_type
[characteristic ...]
BEGIN
  -- SQL 語句塊，必須有 RETURN
END

特性選項

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

開發必知 5 大避坑指南

1. 分隔符重定義：腳本批量導入時，默認分號會誤切斷過程體。必須先聲明：
   DELIMITER $$ ... 過程體 ... END $$ DELIMITER ;
2. 變量聲明：DECLARE var_name type [DEFAULT value]; 會話變量用 @var_name 直接引用，無需聲明
3. 註釋風格：支持 -- 單行 和 /* 多行 */
4. 內建函數差異：部分函數名與 Oracle/Sybase 同名但行為不同（如 IFNULL() vs NVL()、DATE_ADD() 語法差異），遷移時需逐一驗證
5. 調試手段：無官方斷點調試器，實用笨辦法——建調試表，關鍵節點 INSERT INTO debug_log VALUES (step, msg, NOW()) 追蹤流程

實戰案例：可逆字符串加密（函數版 + 過程版）

算法特點：同輸入每次產出不同密文（引入時間種子），長度保留，可解密。弱點：不支持大小寫/中文、強度低、僅供教學。

函數版

CREATE FUNCTION fun_encrypt(inpass VARCHAR(10)) RETURNS VARCHAR(11)
BEGIN
  DECLARE string_in  VARCHAR(39) DEFAULT '_$#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  DECLARE string_out VARCHAR(78) DEFAULT 'YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#$_';
  DECLARE offset     TINYINT;
  DECLARE outpass    VARCHAR(30) DEFAULT '';
  DECLARE len        TINYINT;
  DECLARE i          TINYINT DEFAULT 0;

  SET len = LENGTH(inpass);
  IF len <= 0 OR len > 10 THEN RETURN ''; END IF;

  SET offset = (SECOND(NOW()) % 39) + 1;
  SET outpass = CONCAT(outpass, SUBSTRING(string_out, offset, 1));
  SET string_out = CONCAT(string_out, string_out);

  REPEAT
    SET i = i + 1;
    SET outpass = CONCAT(outpass,
      SUBSTR(string_out, INSTR(string_in, SUBSTRING(inpass, i, 1)) + offset, 1)
    );
  UNTIL i >= len END REPEAT;

  RETURN outpass;
END

過程版

CREATE PROCEDURE proc_encrypt(IN inpass VARCHAR(10), OUT outpass VARCHAR(11))
BEGIN
  DECLARE string_in  VARCHAR(39) DEFAULT '_$#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  DECLARE string_out VARCHAR(78) DEFAULT 'YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#$_';
  DECLARE offset     TINYINT;
  DECLARE len        TINYINT;
  DECLARE i          TINYINT DEFAULT 0;

  SET len = LENGTH(inpass);
  IF len <= 0 OR len > 10 THEN
    SET outpass = '';
  ELSE
    SET offset = (SECOND(NOW()) % 39) + 1;
    SET outpass = CONCAT('', SUBSTRING(string_out, offset, 1));
    SET string_out = CONCAT(string_out, string_out);

    REPEAT
      SET i = i + 1;
      SET outpass = CONCAT(outpass,
        SUBSTR(string_out, INSTR(string_in, SUBSTRING(inpass, i, 1)) + offset, 1)
      );
    UNTIL i >= len END REPEAT;
  END IF;
END

調用驗證

mysql> CALL proc_encrypt('zhouys', @a); SELECT @a;
+---------+
| @a      |
+---------+
| U_PI6$4 |
+---------+

mysql> CALL proc_encrypt('zhouys', @a); SELECT @a;
+---------+
| @a      |
+---------+
| 9P8UEGM |
+---------+

mysql> SELECT fun_decrypt('U_PI6$4');  -- 需配套實現解密函數
+----------------------+
| fun_decrypt('U_PI6$4') |
+----------------------+
| ZHOUYS               |
+----------------------+

生產環境建議

• 加密業務請使用 AES_ENCRYPT()/AES_DECRYPT() 或應用層成熟庫（OpenSSL/libsodium）
• 存儲過程適合：高頻、邏輯固定、數據密集型操作（批量結算、報表聚合）
• 業務邏輯盡量保持在應用層，利用版本控制、單元測試、CI/CD 流程保障質量
• 定期 ANALYZE PROCEDURE、監控 mysql.proc 表大小，防止緩存膨脹