MySQL 5 存儲過程編寫實踐：語法規範、變量聲明、異常處理、游標循環、加密算法示例與調試技巧

語法結構

CREATE PROCEDURE name ([IN|OUT|INOUT] param type, ...)
[characteristic ...] routine_body

CREATE FUNCTION name ([param type, ...])
RETURNS type [characteristic ...] routine_body

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

routine_body:
  BEGIN ... END

開發關鍵點

1. 註釋：-- 行註釋，/* */ 塊註釋。
2. 變量聲明：DECLARE var type [DEFAULT val]; 臨時變量可用 @var 免聲明。
3. 腳本導入分隔符：DELIMITER $$ ... DELIMITER ; 避免分號過早終結。
4. 函數兼容性：部分函數名與 Oracle/Sybase 同名異義或同義異名，遷移時必須驗證。
5. 調試技巧：創建調試表，關鍵節點 INSERT 記錄流程變量值，彌補無斷點調試工具的缺憾。

加密算法示例：每次調用結果不同的簡易加密

函數版

CREATE FUNCTION fun_addmm(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 pro_addmm(IN inpass VARCHAR(10), OUT outpass VARCHAR(11))
BEGIN
  -- 變量聲明同函數版
  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);
    SET i = 0;
    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

測試結果

CALL pro_addmm('zhouys', @a); SELECT @a;  -- U_$I6$4
CALL pro_addmm('zhouys', @a); SELECT @a;  -- 9P8UEGM
-- 同輸入每次結果不同（基於秒級隨機種子）
SELECT fun_submm('U_$I6$4');  -- ZHOUYS (解密函數未列出)

算法弱點與改進方向

• 不支持大小寫、中文、特殊字符。
• 強度極低（替換密碼+時間種子），不可用於安全場景。
• 改進：引入 AES_ENCRYPT/AES_DECRYPT、SHA2、PBKDF2 等內置加密函數，或應用層使用成熟庫（bcrypt、Argon2）。

現代最佳實踐

• 業務邏輯盡量下沉應用層，存儲過程僅用於極簡單的數據完整性/原子操作。
• 複雜計算、加密、外部調用在應用層完成，數據庫專注存儲與查詢。
• MySQL 8.0+ 支持 CTE、窗口函數、JSON、即時分析，許多舊存儲過程邏輯可單條 SQL 替代。