MySQL 預存程序實戰精要：動態 SQL、字元集衝突、分頁限制與控制流

前言

預存程序將業務邏輯下沉到資料庫層，減少網路往返、提升封裝性。本文基於 MySQL 5.1/5.5 實戰案例，深度解析動態 SQL 分頁、字元集 Collation 衝突修復、ROW_COUNT 獲取影響行數、控制流語句、參數檢查等核心技術點。

核心技術點總覽

實戰案例代碼庫

1. 動態 SQL 分頁查詢 (MySQL 5.1 相容)

DROP PROCEDURE IF EXISTS sp_select_reply_article;
DELIMITER ;;
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;
  
  -- 動態構建含 LIMIT 變數的 SQL
  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;;
DELIMITER ;

關鍵：CONCAT 構建字串 → PREPARE 編譯 → EXECUTE ... USING 綁定參數 → DEALLOCATE 釋放。MySQL 5.5+ 可直接用 LIMIT pagefrom, pagesize 無需動態 SQL。

2. 更新操作返回影響行數

DROP PROCEDURE IF EXISTS sp_update_permission;
DELIMITER ;;
CREATE PROCEDURE sp_update_permission(
  IN puser_uid VARCHAR(20),
  IN plevel INT,
  IN ppower INT
)
BEGIN
  SET @puser_uid = puser_uid;
  SET @plevel = plevel;
  SET @ppower = ppower;
  
  UPDATE gk_permission 
  SET `level` = @plevel, power = @ppower 
  WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci;
  
  -- 緊接返回影響行數
  SELECT ROW_COUNT() AS affected_rows;
END;;
DELIMITER ;

3. 字元集 Collation 衝突修復

-- 錯誤：Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
-- 原因：表欄位 collation 與變數/常數 collation 不一致

-- 修復：統一轉換比較雙方
WHERE user_uid = CONVERT(@puser_uid USING utf8) COLLATE utf8_unicode_ci

-- 或在建表時統一
CREATE TABLE t (c VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci);

4. 插入數據 (含外鍵檢查、預設值)

DROP PROCEDURE IF EXISTS sp_insert_user;
DELIMITER ;;
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
  SET @uid = uid; SET @upw = upw; SET @uname = uname;
  SET @sex = sex; SET @phone = phone;
  SET @u_id = u_id; SET @s_id = s_id; SET @j_id = j_id;
  SET @verifytime = DATE('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, @verifytime);
  
  -- INSERT 會自動返回 affected_rows (客戶端可直接讀取)
END;;
DELIMITER ;

5. 根據 ID 刪除

DROP PROCEDURE IF EXISTS sp_delete_exchange_by_id;
DELIMITER ;;
CREATE PROCEDURE sp_delete_exchange_by_id(IN eid INT)
BEGIN
  SET @eid = eid;
  DELETE FROM gk_exchange WHERE id = @eid;
END;;
DELIMITER ;

6. 條件分支查詢 (IF 控制流)

DROP PROCEDURE IF EXISTS sp_select_user_by_uid;
DELIMITER ;;
CREATE PROCEDURE sp_select_user_by_uid(
  IN uid VARCHAR(20),
  IN getAdmin INT
)
BEGIN
  SET @uid = uid;
  
  IF getAdmin = 1 THEN
    -- 查詢管理員 (含權限表 JOIN)
    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;
  END IF;
  
  IF getAdmin = 0 THEN
    -- 查詢普通用戶
    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;;
DELIMITER ;

控制流語句完整語法

IF-THEN-ELSEIF-ELSE-END IF

IF condition1 THEN
  statements;
ELSEIF condition2 THEN
  statements;
ELSE
  statements;
END IF;

CASE

CASE case_value
  WHEN when_value THEN statements;
  WHEN when_value THEN statements;
  ELSE statements;
END CASE;

-- 或條件式 CASE
CASE
  WHEN condition1 THEN statements;
  WHEN condition2 THEN statements;
  ELSE statements;
END CASE;

LOOP / REPEAT / WHILE

-- LOOP (需 LEAVE 跳出)
label: LOOP
  statements;
  IF condition THEN LEAVE label; END IF;
END LOOP;

-- REPEAT (後測試)
REPEAT
  statements;
UNTIL condition
END REPEAT;

-- WHILE (前測試)
WHILE condition DO
  statements;
END WHILE;

ITERATE / LEAVE

-- ITERATE: 類似 continue，跳到迴圈開頭
-- LEAVE: 類似 break，跳出迴圈/標籤區塊

最佳實踐與避坑清單

MySQL 8.0 新增特性

• CTE 在預存程序中可用：WITH cte AS (...) SELECT ...
• 窗口函數：ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
• 隱式索引：ALTER TABLE ... ALTER INDEX ... INVISIBLE 測試索引影響
• 資源群組：SET RESOURCE GROUP 控制 CPU 佔用

總結

• 動態 SQL：解決 5.1 LIMIT 變數限制，PREPARE/EXECUTE/DEALLOCATE 三部曲
• ROW_COUNT()：寫操作後緊接調用獲取影響行數
• Collation 衝突：CONVERT(... USING charset) COLLATE collation 強制統一
• 控制流：IF/CASE/LOOP 完整支援，善用標籤管理巢狀迴圈
• 反引號：保護關鍵字識別符，養成習慣 \`table\`、\`column\`
• 版本演進：5.5+ 原生 LIMIT 變數、8.0+ CTE/窗口函數，持續現代化