MySQL 通用分頁預存程序：動態 SQL 實現靈活高效分頁查詢

前言：為什麼需要通用分頁預存程序？

業務系統普遍面臨分頁查詢需求，但每張表寫專用分頁 SQL 既繁瑣又難維護。MySQL 5.0+ 支援預存程序與動態 SQL，讓我們能封裝一個通用分頁預存程序，支援任意表、任意欄位、任意條件、任意排序、任意頁碼/頁大小。本文提供生產級完整實現與調用範例。

預存程式碼完整版（含詳細註釋）

DELIMITER $$

DROP PROCEDURE IF EXISTS `prc_page_result` $$
CREATE PROCEDURE `prc_page_result`(
    IN  `p_currpage`    INT,           -- 當前頁碼（從 1 開始）
    IN  `p_columns`     VARCHAR(500),  -- 查詢欄位，如 "id, name, created_at" 或 "*"
    IN  `p_tablename`   VARCHAR(500),  -- 目標表名
    IN  `p_condition`   VARCHAR(500),  -- WHERE 條件（不含 WHERE 關鍵字），如 "status=1 AND type=2"
    IN  `p_order_field` VARCHAR(100),  -- 排序欄位
    IN  `p_asc_field`   TINYINT,       -- 1=DESC，0=ASC
    IN  `p_primary_field` VARCHAR(100),-- 主鍵欄位（用於游標分頁）
    IN  `p_pagesize`    INT            -- 每頁筆數
)
BEGIN
    DECLARE v_sql     VARCHAR(4000);
    DECLARE v_order   VARCHAR(1000);
    DECLARE v_temp    VARCHAR(100);
    
    -- 構建 ORDER BY 子句
    IF p_asc_field = 1 THEN
        SET v_order = CONCAT(' ORDER BY ', p_order_field, ' DESC ');
        SET v_temp  = ' < (SELECT MIN ';
    ELSE
        SET v_order = CONCAT(' ORDER BY ', p_order_field, ' ASC ');
        SET v_temp  = ' > (SELECT MAX ';
    END IF;
    
    -- 第一頁：直接 LIMIT
    IF p_currpage = 1 THEN
        IF p_condition <> '' THEN
            SET v_sql = CONCAT('SELECT ', p_columns, ' FROM ', p_tablename, 
                               ' WHERE ', p_condition, v_order, ' LIMIT ?');
        ELSE
            SET v_sql = CONCAT('SELECT ', p_columns, ' FROM ', p_tablename, 
                               v_order, ' LIMIT ?');
        END IF;
    ELSE
        -- 非第一頁：游標式分頁（避免 OFFSET 深分頁效能崩塌）
        IF p_condition <> '' THEN
            SET v_sql = CONCAT('SELECT ', p_columns, ' FROM ', p_tablename,
                               ' WHERE ', p_condition,
                               ' AND ', p_primary_field, v_temp,
                               '(', p_primary_field, ') FROM (SELECT ',
                               p_primary_field, ' FROM ', p_tablename, v_order,
                               ' LIMIT ', (p_currpage-1)*p_pagesize, ') AS tmp', 
                               v_order, ' LIMIT ?');
        ELSE
            SET v_sql = CONCAT('SELECT ', p_columns, ' FROM ', p_tablename,
                               ' WHERE ', p_primary_field, v_temp,
                               '(', p_primary_field, ') FROM (SELECT ',
                               p_primary_field, ' FROM ', p_tablename, v_order,
                               ' LIMIT ', (p_currpage-1)*p_pagesize, ') AS tmp', 
                               v_order, ' LIMIT ?');
        END IF;
    END IF;
    
    -- 準備並執行動態 SQL
    SET @page_size = p_pagesize;
    SET @stmt_sql  = v_sql;
    PREPARE stmt FROM @stmt_sql;
    EXECUTE stmt USING @page_size;
    DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

核心設計亮點

1. 游標式分頁：非第一頁使用主鍵範圍查詢替代 OFFSET，百萬級資料深分頁效能不退化
2. 動態 SQL + 預處理：防 SQL 注入，執行計畫重用
3. 參數化 LIMIT：LIMIT ? 配合 USING @page_size
4. 正向/反向排序自適應：透過 MIN/MAX 子查詢自動處理

部署與調用指南

1. 部署

mysql -u root -p < paging_proc.sql

2. 調用範例

-- 第 1 頁，按 id 降序，每頁 25 筆
CALL prc_page_result(1, '*', 'articles', 'status=1', 'id', 1, 'id', 25);

-- 第 3 頁，多條件，按 created_at 升序
CALL prc_page_result(3, 'id,title,created_at', 'articles', 
                     'status=1 AND category_id=5', 'created_at', 0, 'id', 20);

3. 應用層封裝建議

public List<Map> queryPage(int page, int size, String table, 
                           String columns, String where, 
                           String orderBy, String pk, boolean desc) {
    String sql = "{CALL prc_page_result(?, ?, ?, ?, ?, ?, ?, ?)}";
    // 使用 CallableStatement 綁定參數
}

效能對比：OFFSET vs 游標分頁

注意事項與擴展方向

• 主鍵必須為單欄且有序（自增/UUID v1/時間戳），複合主鍵需擴展邏輯
• WHERE 條件字串長度受限 500 字元，複雜查詢建議改用視圖或 CTE
• MySQL 8.0+ 可改用 WITH RECURSIVE CTE 實現更優雅的游標分頁
• 生產環境建議加入執行時間監控與慢查詢日誌標記

結語

這個通用分頁預存程序以 動態 SQL + 游標分頁 + 預處理 三大技術，解決了 MySQL 分頁查詢的通用性與效能雙重難題。部署一次，全系統複用，是中大型 MySQL 應用的標準化基建組件。