MySQL 分頁儲存過程實戰：動態 SQL、預處理與參數化完整實現

分頁儲存過程核心邏輯

通過動態 SQL 組裝 LIMIT offset, count，配合 PREPARE/EXECUTE 實現通用分頁，避免應用層拼接 SQL 風險。

完整儲存過程代碼

DROP PROCEDURE IF EXISTS pr_pager;

DELIMITER //
CREATE PROCEDURE pr_pager(
    IN  p_table_name     VARCHAR(1024),   -- 表名 (可含 schema)
    IN  p_fields         VARCHAR(1024),   -- 查詢欄位，如 "id, name, email"
    IN  p_page_size      INT,             -- 每頁筆數
    IN  p_page_now       INT,             -- 當前頁碼 (1-based)
    IN  p_order_string   VARCHAR(128),    -- ORDER BY 子句，如 "ORDER BY created_at DESC"
    IN  p_where_string   VARCHAR(1024),   -- WHERE 子句，如 "WHERE status=1 AND type=2"
    OUT p_out_rows       INT              -- 輸出：總行數
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '通用分頁儲存過程'
BEGIN
    DECLARE v_offset INT DEFAULT 0;
    DECLARE v_limit_clause CHAR(64);
    
    -- 計算 OFFSET
    SET v_offset = (p_page_now - 1) * p_page_size;
    SET v_limit_clause = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    
    -- 1. 計算總行數 (動態 SQL)
    SET @count_sql = CONCAT('SELECT COUNT(*) INTO @total_rows FROM ', p_table_name, ' ', p_where_string);
    PREPARE stmt_count FROM @count_sql;
    EXECUTE stmt_count;
    DEALLOCATE PREPARE stmt_count;
    
    SET p_out_rows = @total_rows;
    
    -- 2. 查詢分頁資料 (動態 SQL)
    SET @data_sql = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, v_limit_clause);
    PREPARE stmt_data FROM @data_sql;
    EXECUTE stmt_data;
    DEALLOCATE PREPARE stmt_data;
END//
DELIMITER ;

調用範例

-- 第 1 頁，每頁 20 筆，按建立時間倒序，查詢啟用用戶
CALL pr_pager(
    'users',                    -- 表名
    'id, username, email, created_at',  -- 欄位
    20,                         -- 頁大小
    1,                          -- 第 1 頁
    'ORDER BY created_at DESC', -- 排序
    'WHERE status = 1',         -- 條件
    @total_rows                 -- 輸出總行數
);

SELECT @total_rows;  -- 獲取總筆數

關鍵技術點解析

1. 動態 SQL + 預處理 = 安全 + 靈活

• CONCAT() 組裝 SQL 字符串
• PREPARE 解析、優化、生成執行計畫
• EXECUTE 執行
• DEALLOCATE PREPARE 釋放資源
• 防注入：表名、欄位、WHERE、ORDER 由調用者控制，**調用層必須嚴格驗證/白名單**

2. 變數作用域

• DECLARE v_xxx：區域變數，僅 BEGIN...END 內可見
• @xxx：用戶會話變數，跨語句可見 (用於 PREPARE 間傳值)
• OUT p_out_rows：輸出參數，返回給調用者

3. 分頁效能優化要點

應用層調用封裝 (PHP PDO 範例)

function pager(PDO $pdo, string $table, string $fields, int $pageSize, int $pageNo, 
               string $where, string $order, array $whereParams = []): array {
    
    // 白名單驗證：防止 SQL 注入
    $allowedTables = ['users', 'orders', 'products'];
    $allowedFields = ['id', 'username', 'email', 'status', 'created_at', 'updated_at'];
    // ... 實際專案需完整白名單驗證邏輯 ...
    
    $stmt = $pdo->prepare("CALL pr_pager(?, ?, ?, ?, ?, ?, @total_rows)");
    $stmt->execute([$table, $fields, $pageSize, $pageNo, $order, $where]);
    
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    
    $total = $pdo->query("SELECT @total_rows")->fetchColumn();
    
    return [
        'data' => $data,
        'total' => (int)$total,
        'page' => $pageNo,
        'page_size' => $pageSize,
        'total_pages' => ceil($total / $pageSize)
    ];
}

安全性強化清單

1. 表名/欄位白名單：嚴格限制可傳入的識別符
2. WHERE 條件參數化：p_where_string 傳 "WHERE status=? AND type=?"，參數另行綁定 (需改造 SP 支援)
3. 權限最小化：調用帳號僅需 EXECUTE 權限，無 DROP/ALTER
4. SQL 注入審計：定期掃描調用日誌，檢測異常模式

替代方案對比

總結

MySQL 分頁儲存過程展示了 **動態 SQL + 預處理** 的經典模式。現代架構傾向將分頁邏輯下沉到應用層 (配合 ORM/Query Builder)，但理解儲存過程實現有助於維護舊系統、處理跨語言共享邏輯場景。**安全白名單、深分頁優化、游標分頁** 是三大必修課。