MySQL 存儲過程開發實戰總結：動態 SQL、字符集衝突、流程控制與常見坑

背景

從 SQL Server 遷移至 MySQL 存儲過程開發時的關鍵語法差異與避坑指南。環境：MySQL 5.1（部分特性 5.5+ 支持）。

核心技術點速覽

1. LIMIT 子句不支持變量 (MySQL 5.1)

5.1 版本中 LIMIT ?, ? 不支持預處理佔位符，需構建動態 SQL。

CREATE PROCEDURE `sp_select_reply_article`(
    IN `ra_id` INT, IN `pagefrom` INT, IN `pagesize` INT
)
BEGIN
    SET @sql = CONCAT('SELECT * FROM gk_article WHERE id = ? LIMIT ?, ?');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @ra_id, @pagefrom, @pagesize;
    DEALLOCATE PREPARE stmt;
END

注意： 動態 SQL 無法使用查詢緩存，性能弱於靜態 SQL。MySQL 5.5+ 已支持 LIMIT 變量，升級版本可簡化寫法。

2. 獲取影響行數：ROW_COUNT()

UPDATE gk_permission SET `level` = @plevel WHERE user_uid = @puser_uid;
SELECT ROW_COUNT() AS affected_rows; -- 必須緊跟在 DML 語句後，中間用分號隔開

3. 字符集排序規則衝突解決

報錯：Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

根因：表/列/連接/參數字符集不一致。強制轉換其中一方：

WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci

最佳實踐：統一庫/表/列/連接字符集為 utf8mb4 + utf8mb4_unicode_ci，從源頭消除衝突。

4. 流程控制：IF / CASE / WHILE / LOOP / REPEAT

IF (getAdmin = 1) THEN
    -- 查詢管理員信息（多表 JOIN）
    SELECT ... FROM gk_user AS us ... WHERE us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
ELSEIF (getAdmin = 0) THEN
    -- 查詢普通用戶信息
    SELECT ... FROM gk_user AS us ... WHERE us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
END IF;

5. 反引號 \` 的正確使用

• 用於包裹 標識符（库名、表名、列名、別名），避免與關鍵字衝突。
• 不用於字符串字面值（字符串用單引號 ' 或雙引號 "）。

6. 參數數量不匹配報錯

Column count doesn't match value count at row 1 → 核對 INSERT 列列表與 VALUES 值列表一一對應。

7. 當前時間獲取

使用 NOW()（返回 YYYY-MM-DD HH:MM:SS）或 CURRENT_TIMESTAMP。

完整示例：帶外鍵檢查的用戶插入

CREATE PROCEDURE `sp_insert_user`(
    IN `uid` VARCHAR(20), IN `upw` VARCHAR(32), IN `uname` VARCHAR(20),
    IN `sex` INT, IN `phone` VARCHAR(20),
    IN `u_id` INT, IN `s_id` INT, IN `j_id` INT
)
BEGIN
    DECLARE v_verifytime DATE DEFAULT '0000-00-00';
    INSERT INTO gk_user(uid, upw, uname, sex, phone, u_id, s_id, j_id, verifytime)
    VALUES(uid, upw, uname, sex, phone, u_id, s_id, j_id, v_verifytime);
    -- 自動返回影響行數
END

避坑清單

1. 版本特性差異：確認生產環境 MySQL 版本，動態 SQL、LIMIT 變量、SIGNAL/RESIGNAL 錯誤處理等特性版本依賴強。
2. 字符集統一：建庫建表指定 CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci，連接字符串指定 charset=utf8mb4。
3. 動態 SQL 注入風險：拼接 SQL 時嚴格驗證/轉義輸入，優先使用預處理 PREPARE ... USING 綁定參數。
4. 事務邊界：存儲過程不自動開啟事務，調用方需顯式控制 START TRANSACTION / COMMIT / ROLLBACK。
5. 調試困難：善用 SELECT ... INTO @var 輸出中間變量，或引入日誌表記錄關鍵步驟。