分页存储过程 多条件

分页存储过程与多条件查询的实现与优化

在数据库开发中，分页查询和多条件筛选是常见需求，通过存储过程实现分页与多条件组合查询，既能提升性能，又能增强代码复用性，以下从原理、实现到优化进行全面解析。

分页查询的核心原理

分页的本质是通过限制返回数据的范围,常见两种方式：

1. OFFSET + FETCH（适用于SQL Server、PostgreSQL等）

   • 语法：SELECT FROM Table ORDER BY [col] OFFSET @StartRow ROWS FETCH NEXT @PageSize ROWS ONLY
   • 优点：简单直观，适合小数据量分页。
   • 缺点：大数据量时效率低（需扫描前@StartRow行）。

2. ROW_NUMBER() 窗口函数（推荐方式）

   • 语法：

     SELECT  FROM (
       SELECT , ROW_NUMBER() OVER (ORDER BY [col]) AS RowNum
       FROM Table
       WHERE [conditions]
     ) AS T
     WHERE T.RowNum BETWEEN @StartRow AND @EndRow

   • 优点：性能更优，适合大数据量分页。
   • 缺点：需排序字段,且窗口函数可能增加计算开销。

多条件查询的动态处理

多条件查询需根据用户输入动态拼接WHERE条件,常见实现方式：

1. 动态SQL拼接

   • 根据传入参数是否为空，动态生成WHERE子句。
   • 示例（SQL Server）：

     DECLARE @SQL NVARCHAR(MAX) = 'SELECT  FROM Table WHERE 1=1'
     IF @Condition1 IS NOT NULL SET @SQL += ' AND Col1 = @Condition1'
     IF @Condition2 IS NOT NULL SET @SQL += ' AND Col2 LIKE ''%''+@Condition2+''%'''
     EXEC sp_executesql @SQL, N'@Condition1 INT, @Condition2 VARCHAR(50)', @Condition1, @Condition2

2. 可选参数处理

   • 存储过程参数允许NULL值，通过判断参数是否为空决定是否应用条件。
   • 示例：

     WHERE (@StartDate IS NULL OR DateCol >= @StartDate)
     AND (@EndDate IS NULL OR DateCol <= @EndDate)

分页与多条件结合的存储过程实现

以下以SQL Server为例，实现支持多条件+分页的通用存储过程：

CREATE PROCEDURE GetPagedData
  @PageNumber INT,
  @PageSize INT,
  -多条件参数
  @Condition1 VARCHAR(50) = NULL,
  @Condition2 DATE = NULL,
  @SortField VARCHAR(50) = 'ID', -默认排序字段
  @SortDirection VARCHAR(10) = 'ASC' -ASC/DESC
AS
BEGIN
  DECLARE @StartRow INT = (@PageNumber 1)  @PageSize + 1;
  DECLARE @EndRow INT = @PageNumber  @PageSize;
  -动态构建SQL
  DECLARE @SQL NVARCHAR(MAX) = 
    'SELECT  FROM (
      SELECT , ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@SortField) + ' ' + 
        CASE WHEN @SortDirection = 'ASC' THEN ''ASC'' ELSE ''DESC'' END + ') AS RowNum
      FROM TableName
      WHERE 1=1' +
    CASE WHEN @Condition1 IS NOT NULL THEN ' AND Col1 LIKE ''%''+@Condition1+''%''' ELSE '' END +
    CASE WHEN @Condition2 IS NOT NULL THEN ' AND DateCol = @Condition2' ELSE '' END +
    ') AS T
    WHERE T.RowNum BETWEEN @StartRow AND @EndRow';
  EXEC sp_executesql @SQL, 
    N'@StartRow INT, @EndRow INT, @Condition1 VARCHAR(50), @Condition2 DATE',
    @StartRow, @EndRow, @Condition1, @Condition2;
END

关键参数与逻辑说明

逻辑流程：

1. 计算分页范围（@StartRow到@EndRow）。
2. 根据非空条件动态拼接WHERE子句。
3. 使用ROW_NUMBER()生成行号,最终过滤行号范围。

性能优化建议

1. 索引优化：

   • 对排序字段（如@SortField）和条件字段（如Col1、DateCol）建立索引。
   • 避免在WHERE条件中对字段使用函数（如LIKE '%value%'可能导致全表扫描）。

2. 参数化查询：

   • 使用sp_executesql而非直接拼接SQL,防止SQL注入并提升执行计划重用率。

3. 分页方式选择：

   • 大数据量优先使用ROW_NUMBER()，小数据量可用OFFSET+FETCH。

FAQs

Q1：如何测试存储过程的分页功能？
A1：可通过插入模拟数据（如使用INSERT INTO TableName VALUES (...)生成大量记录），然后调用存储过程并逐步调整@PageNumber和@PageSize，验证返回结果是否符合预期。

EXEC GetPagedData @PageNumber=1, @PageSize=10, @Condition1='test', @SortField='CreateTime';

Q2：如果传入的条件参数为NULL，存储过程会如何处理？
A2：存储过程中对每个条件参数均做了NULL判断。@Condition1为NULL时，动态SQL不会拼接AND Col1 LIKE条件，相当于忽略该筛选规则，其他参数同理，确保