MySQL 分頁存儲過程：動態 SQL 實現通用列表查詢並返回總記錄數

通用分頁存儲過程，接受表名、列、條件、排序、頁碼、頁大小，輸出總記錄數（OUT 參數）與當前頁數據。

完整代碼

CREATE PROCEDURE p_pageList(
  IN m_pageNo INT,
  IN m_perPageCnt INT,
  IN m_column VARCHAR(1000),
  IN m_table VARCHAR(1000),
  IN m_condition VARCHAR(1000),
  IN m_orderBy VARCHAR(200),
  OUT m_totalPageCnt INT
)
BEGIN
  DECLARE v_limitStart INT;
  DECLARE v_limitEnd INT;
  SET v_limitStart = (m_pageNo - 1) * m_perPageCnt;
  SET v_limitEnd = m_perPageCnt;

  SET @sqlCnt = CONCAT('SELECT COUNT(1) INTO @totalCnt FROM ', m_table);
  SET @sql = CONCAT('SELECT ', m_column, ' FROM ', m_table);

  IF m_condition IS NOT NULL AND m_condition <> '' THEN
    SET @sql = CONCAT(@sql, ' WHERE ', m_condition);
    SET @sqlCnt = CONCAT(@sqlCnt, ' WHERE ', m_condition);
  END IF;

  IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN
    SET @sql = CONCAT(@sql, ' ORDER BY ', m_orderBy);
  END IF;

  SET @sql = CONCAT(@sql, ' LIMIT ', v_limitStart, ',', v_limitEnd);

  PREPARE s_cnt FROM @sqlCnt;
  EXECUTE s_cnt;
  DEALLOCATE PREPARE s_cnt;

  SET m_totalPageCnt = @totalCnt;

  PREPARE s_data FROM @sql;
  EXECUTE s_data;
  DEALLOCATE PREPARE s_data;
END

調用示例

CALL p_pageList(1, 20, 'id,name,age', 'user', 'age>18', 'age DESC', @total);
SELECT @total;

關鍵點

1. COUNT(1) INTO @totalCnt 利用用戶變量跨 PREPARE 傳遞聚合結果。
2. LIMIT offset, rows 實現分頁（MySQL 專用語法）。
3. 動態表名/列名無法參數化，應用層必須白名單校驗 防注入。
4. 大表分頁深翻時 LIMIT 100000,20 性能差，建議改用 WHERE id > last_id LIMIT 20 游標分頁。

MySQL 8.0+ 現代寫法

CREATE PROCEDURE p_pageList_v2(...)
BEGIN
  SET @sql = CONCAT('SELECT ', m_column, ' FROM ', m_table,
    IFNULL(CONCAT(' WHERE ', m_condition), ''),
    IFNULL(CONCAT(' ORDER BY ', m_orderBy), ''),
    ' LIMIT ', (m_pageNo-1)*m_perPageCnt, ',', m_perPageCnt);
  PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

  SET @cntSql = CONCAT('SELECT COUNT(*) FROM ', m_table, IFNULL(CONCAT(' WHERE ', m_condition), ''));
  PREPARE c FROM @cntSql; EXECUTE c; DEALLOCATE PREPARE c;
  -- 此處需用 SELECT @cnt 獲取，或改用 OUT 參數配合 SELECT ... INTO
END