MySQL 儲存過程實現通用分頁：支援動態表、欄位、條件、排序並輸出總筆數

MySQL 5.0+ 支援儲存過程，將高頻「分頁查詢」封裝為可復用單元，減少應用層拼接 SQL 風險，並能單次呼叫同時回傳資料集與總筆數（OUT 參數），避免雙查詢往返。本文提供一個通用分頁儲存過程範本，支援動態指定表、欄位、條件、排序。

儲存過程完整定義

DELIMITER //

CREATE PROCEDURE `p_pageList` (
    IN  `m_pageNo`      INT,          -- 頁碼（從 1 開始）
    IN  `m_perPageCnt`  INT,          -- 每頁筆數
    IN  `m_column`      VARCHAR(1000),-- 查詢欄位，如 'id, name, created_at'
    IN  `m_table`       VARCHAR(1000),-- 表名
    IN  `m_condition`   VARCHAR(1000),-- WHERE 條件（不含 WHERE 關鍵字），如 'status=1 AND type=2'
    IN  `m_orderBy`     VARCHAR(200), -- ORDER BY 子句（不含 ORDER BY 關鍵字），如 'id DESC'
    OUT `m_totalPageCnt` INT          -- 輸出：總筆數
)
BEGIN
    DECLARE v_limitStart INT;
    DECLARE v_limitEnd   INT;
    DECLARE v_sqlCnt     TEXT;
    DECLARE v_sql        TEXT;

    SET v_limitStart = (m_pageNo - 1) * m_perPageCnt;
    SET v_limitEnd   = m_perPageCnt;

    -- 總筆數查詢
    SET v_sqlCnt = CONCAT('SELECT COUNT(1) INTO @pageCnt FROM ', m_table);
    -- 資料查詢
    SET v_sql = CONCAT('SELECT ', m_column, ' FROM ', m_table);

    -- 動態拼接 WHERE
    IF m_condition IS NOT NULL AND m_condition <> '' THEN
        SET v_sql  = CONCAT(v_sql,  ' WHERE ', m_condition);
        SET v_sqlCnt = CONCAT(v_sqlCnt, ' WHERE ', m_condition);
    END IF;

    -- 動態拼接 ORDER BY
    IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
        SET v_sql = CONCAT(v_sql, ' ORDER BY ', m_orderBy);
    END IF;

    -- LIMIT 子句
    SET v_sql = CONCAT(v_sql, ' LIMIT ', v_limitStart, ',', v_limitEnd);

    -- 執行總筆數查詢
    PREPARE stmt_cnt FROM v_sqlCnt;
    EXECUTE stmt_cnt;
    DEALLOCATE PREPARE stmt_cnt;

    -- 將總筆數賦給 OUT 參數
    SET m_totalPageCnt = @pageCnt;

    -- 執行資料查詢並回傳結果集
    PREPARE stmt_data FROM v_sql;
    EXECUTE stmt_data;
    DEALLOCATE PREPARE stmt_data;
END //

DELIMITER ;

呼叫範例

-- 查詢 users 表第 3 頁，每頁 20 筆，條件 status=1，按 id 降序
CALL p_pageList(3, 20, 'id, username, email', 'users', 'status=1', 'id DESC', @total);
SELECT @total; -- 獲取總筆數

關鍵技術點解析

• 動態 SQL + Prepared Statement：PREPARE / EXECUTE / DEALLOCATE PREPARE 實現運行時組裝 SQL，防止注入（參數化僅限值，識別符仍需拼接，需應用層白名單驗證表名/欄位名）。
• 用戶變數 @pageCnt 傳遞 COUNT 結果：SELECT COUNT(1) INTO @pageCnt 將聚合結果存入會話變數，再賦給 OUT 參數。
• 單次呼叫雙重收益：應用層一次 CALL 即拿到結果集與總筆數，省去第二次 SELECT COUNT(*) 往返。

安全與效能強化建議（生產環境必做）

1. 識別符白名單驗證：在儲存過程內或應用層，對 m_table、m_column、m_orderBy 做白名單比對，防 SQL 注入。
2. 避免 SELECT *：強制呼叫端傳具體欄位，減少網路與 I/O。
3. 大表深分頁優化：LIMIT 100000, 20 效能差。改用游標分頁（WHERE id > last_id ORDER BY id LIMIT 20）或覆蓋索引（SELECT id FROM ... ORDER BY ... LIMIT ... 回表）。
4. 統計資訊準確：定期 ANALYZE TABLE，確保優化器對 COUNT(*) 與範圍查詢估算準確。
5. 權限最小化：僅授予執行該儲存過程的 EXECUTE 權限，不給直連表權限。

適用場景與替代方案