MySQL 儲存程式實戰心得：動態 SQL、編碼衝突與控制流程

在實際專案開發中，MySQL 儲存程式是封裝業務邏輯、提升效能的重要工具。本文整理六大實戰技術點，涵蓋動態 SQL、編碼處理、流程控制等高頻場景。

技術點 1：MySQL 5.1 版本 LIMIT 子句不支援變數，需用動態 SQL

MySQL 5.1 中 LIMIT 不能直接使用變數，需透過 PREPARE / EXECUTE 構建動態 SQL（5.5+ 已支援變數）。

CREATE PROCEDURE `sp_select_reply_article`(
    IN `ra_id` INT, IN `pagefrom` INT, IN `pagesize` INT
)
BEGIN
    SET @ra_id = ra_id;
    SET @pagefrom = pagefrom;
    SET @pagesize = pagesize;
    
    -- 構建動態 SQL
    SET @ssra = CONCAT('SELECT * FROM gk_article WHERE id = ? LIMIT ?, ?');
    PREPARE sqlquery FROM @ssra;
    EXECUTE sqlquery USING @ra_id, @pagefrom, @pagesize;
    DEALLOCATE PREPARE sqlquery; -- 釋放準備語句資源
END

效能提醒： 動態 SQL 無法使用查詢快取，執行計劃難以複用，效能不如靜態 SQL。能升級版本盡量升級。

技術點 2：獲取影響行數使用 ROW_COUNT()

UPDATE gk_permission SET `level` = @plevel, power = @ppower 
WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci;
SELECT ROW_COUNT(); -- 緊接在 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

或在表/欄位定義時統一指定 CHARSET utf8 COLLATE utf8_unicode_ci。

技術點 4：流程控制語句（IF/WHILE/LOOP/REPEAT/LEAVE/ITERATE）

IF (getAdmin = 1) THEN
    -- 查詢管理員資料（多表 JOIN）
    SELECT ... FROM gk_user AS us, gk_unit AS un, ... 
    WHERE us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
ELSE
    -- 查詢一般用戶資料
    SELECT ... FROM gk_user AS us, gk_unit AS un, ...
    WHERE us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
END IF;

技術點 5：參數數量不匹配錯誤

Column count doesn't match value count at row 1 通常是 INSERT 欄位與值數量不一致，或 PREPARE 佔位符與 USING 變數數量不符。仔細核對參數順序與數量。

技術點 6 & 7：常用函數與識別符

• NOW()：獲取當前日期時間
• 反引號 `：包裹資料庫、表、欄位名稱，避免與保留字衝突（如 `order`, `key`）

完整範例：帶條件查詢的儲存程式

CREATE PROCEDURE `sp_select_user_by_uid`(
    IN `uid` VARCHAR(20), IN `getAdmin` INT
)
BEGIN
    SET @uid = uid;
    
    IF (getAdmin = 1) THEN
        SELECT us.*, un.`name`, se.`name`, jo.`name`, pe.`level`, pe.power
        FROM gk_user AS us
        JOIN gk_unit AS un ON us.u_id = un.id
        JOIN gk_section AS se ON us.s_id = se.id
        JOIN gk_jobtitle AS jo ON us.j_id = jo.id
        JOIN gk_permission AS pe ON us.uid = pe.user_uid
        WHERE us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
    ELSE
        SELECT us.*, un.`name`, se.`name`, jo.`name`
        FROM gk_user AS us
        JOIN gk_unit AS un ON us.u_id = un.id
        JOIN gk_section AS se ON us.s_id = se.id
        JOIN gk_jobtitle AS jo ON us.j_id = jo.id
        WHERE us.uid = CONVERT(@uid USING utf8) COLLATE utf8_unicode_ci;
    END IF;
END

總結

儲存程式能有效減少網路往返、封裝複雜邏輯，但除錯相對困難。建立統一的命名規範、錯誤處理模板、版本控管流程，對團隊協作至關重要。