MySQL 儲存程序實作分頁查詢：支援總筆數輸出的通用方案

背景動機

近期使用 MySQL + ASP.NET 開發網站，MySQL 5.0+ 已支援儲存程序。分頁查詢是高頻需求，但網路上常見範例多無法回傳「總筆數」，導致前端無法計算總頁數。經研究整理，完成一個可輸出總筆數的通用分頁儲存程序，記錄備忘。

儲存程序完整代碼

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 關鍵字）
    IN  m_orderBy      VARCHAR(200),  -- ORDER BY 子句（不含 ORDER BY 關鍵字）
    OUT m_totalPageCnt INT           -- 輸出：總頁數
)
BEGIN
    DECLARE v_totalRows INT DEFAULT 0;
    DECLARE v_limitStart INT;
    DECLARE v_limitEnd   INT;
    DECLARE v_sqlCnt     TEXT;
    DECLARE v_sql        TEXT;

    -- 計算 LIMIT 參數
    SET v_limitStart = (m_pageNo - 1) * m_perPageCnt;
    SET v_limitEnd   = m_perPageCnt;

    -- 動態組裝 COUNT 查詢（關鍵：取得總筆數）
    SET v_sqlCnt = CONCAT('SELECT COUNT(1) INTO @totalRows FROM ', m_table);
    SET v_sql    = CONCAT('SELECT ', m_column, ' FROM ', m_table);

    -- 附加條件
    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;

    -- 附加排序
    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);

    -- 執行 COUNT 查詢
    PREPARE stmt_cnt FROM v_sqlCnt;
    EXECUTE stmt_cnt;
    DEALLOCATE PREPARE stmt_cnt;

    -- 將總筆數轉為總頁數（向上取整）
    SET m_totalPageCnt = CEIL(@totalRows / m_perPageCnt);

    -- 執行分頁查詢
    PREPARE stmt_data FROM v_sql;
    EXECUTE stmt_data;
    DEALLOCATE PREPARE stmt_data;
END

關鍵技術點解析

1. 動態 SQL + Prepared Statement：使用 CONCAT 組裝查詢字串，再透過 PREPARE/EXECUTE/DEALLOCATE PREPARE 執行，實現表名、欄位、條件的參數化。
2. 用戶變數 @totalRows 接收 COUNT 結果：SELECT COUNT(1) INTO @totalRows 將聚合結果存入會話層級變數，再由外層讀取計算總頁數。
3. OUT 參數回傳總頁數：呼叫端可直接取得 m_totalPageCnt，無需額外查詢。
4. 防注入提醒：此範例為教學示範，實際生產環境請對輸入參數做嚴格驗證或改用應用層 ORM 參數化查詢。

呼叫範例

CALL p_pageList(2, 20, 'id, title, created_at', 'articles', 'status=1', 'created_at DESC', @totalPages);
SELECT @totalPages; -- 取得總頁數

效能考量

• COUNT(1) 在大表上可能較慢，若極度追求效能可考慮維護計數表或使用近似值（EXPLAIN rows）。
• 確保 WHERE 與 ORDER BY 欄位有適當索引。
• 避免 SELECT *，僅查詢必要欄位減少網路傳輸。