ASP.NET 2.0 調用 MySQL 分頁存儲過程：動態 SQL 拼接與 PREPARE 實戰範例

MySQL 存儲過程實現通用分頁：動態 SQL + PREPARE/EXECUTE 模式

MySQL LIMIT offset, rows 語法極簡，配合動態 SQL 可封裝通用分頁過程，供 ASP.NET 2.0 直接調用。

存儲過程完整代碼

DELIMITER $$

DROP PROCEDURE IF EXISTS `mytest`.`MyPage` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPage`(
  IN tableName VARCHAR(100),
  IN fieldsName VARCHAR(100),
  IN pageIndex INT,
  IN pageSize INT,
  IN sortName VARCHAR(500),
  IN strWhere VARCHAR(500)
)
BEGIN
  DECLARE fieldlist VARCHAR(200);
  
  -- 欄位列表處理
  IF fieldsName = '' OR fieldsName IS NULL THEN
    SET fieldlist = '*';
  ELSE
    SET fieldlist = fieldsName;
  END IF;
  
  -- 動態構建 SQL
  SET @strSQL = CONCAT('SELECT ', fieldlist, ' FROM ', tableName);
  
  IF strWhere != '' AND strWhere IS NOT NULL THEN
    SET @strSQL = CONCAT(@strSQL, ' WHERE ', strWhere);
  END IF;
  
  IF sortName != '' AND sortName IS NOT NULL THEN
    SET @strSQL = CONCAT(@strSQL, ' ORDER BY ', sortName);
  END IF;
  
  SET @strSQL = CONCAT(@strSQL, ' LIMIT ', (pageIndex - 1) * pageSize, ',', pageSize);
  
  -- 準備並執行
  PREPARE stmt FROM @strSQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$

DELIMITER ;

ASP.NET 2.0 調用代碼

using MySql.Data.MySqlClient;

public DataTable GetPage(string table, string fields, int pageIndex, int pageSize, string sort, string where) {
    using (MySqlConnection conn = new MySqlConnection(connStr)) {
        using (MySqlCommand cmd = new MySqlCommand("MyPage", conn)) {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("tableName", table);
            cmd.Parameters.AddWithValue("fieldsName", fields);
            cmd.Parameters.AddWithValue("pageIndex", pageIndex);
            cmd.Parameters.AddWithValue("pageSize", pageSize);
            cmd.Parameters.AddWithValue("sortName", sort);
            cmd.Parameters.AddWithValue("strWhere", where);
            
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) {
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }
}

// 使用範例
var dt = GetPage("orders", "id,order_no,amount,created_at", 2, 20, "created_at DESC", "status=1 AND user_id=100");

關鍵技術點

1. PREPARE stmt FROM @user_var：MySQL 動態 SQL 必須通過用戶變量 (@var) 傳遞，局部變量 (DECLARE) 不可用。
2. DEALLOCATE PREPARE：釋放預處理語句資源，防止洩漏。
3. CONCAT 拼接時注意空格、引號轉義，建議用 QUOTE() 包裹字串參數。
4. 注入風險：tableName/fieldsName/sortName/strWhere 直接拼接，**必須在應用層嚴格驗證白名單**，或改用條件構建器生成安全 SQL。

安全加固版 (白名單驗證)

-- 過程內部驗證表名/欄位名
IF tableName NOT IN ('orders','users','products') THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid table name';
END IF;
-- 欄位名正則驗證 ^[a-zA-Z0-9_,]+$
-- 排序欄位白名單

性能提示

• 大偏移量 LIMIT 100000, 20 效能差，改用「游標分頁」：WHERE id > last_id ORDER BY id LIMIT 20。
• 覆蓋索引包含 WHERE/ORDER BY/SELECT 欄位，避免回表。