仿Orm 自动生成分页SQL

来源:互联网 时间:1970-01-01

分页的写法

自从用上了Orm,分页这种事就是腰不酸腿不痛了。不过有时候想用纯粹的ado.net来操作,希望返回的数据是原生的DataTable或DbDataReader类似的东西,故研究下怎么生成分页的SQL语句。

平时接触的数据库有sql2000-2008,Oracle,SQLite 。 分页逻辑,Oracle和SQLite相对好写,就SQL事多,Sql2000下只能用top,排序2次,而Sql2005+就可以使用ROW_NUMBER()分析函数了,据说Sql2012对分页又有了改进,暂时用不上那么高的版本,所以没做。先看看目前这4种数据库的分页写法:

-- Oracle

SELECT * FROM (

SELECT ROWNUM RN, PageTab.* FROM

(

SELECT * FROM User_Tables order by id desc

) PageTab where ROWNUM <= 3010

) Where RN>= 3001

-- SQLite

select * from User_Tables order by id desc limit 3001,10

-- SQL2000

SELECT TOP 100 PERCENT * FROM (

SELECT TOP 10 * FROM (

SELECT TOP 3010 * from User_Tables order by id desc ) PageTab order by id ASC

) PageTab2 order by id desc

-- SQL2005+

Select PageTab.* from (

Select top 3010 ROW_NUMBER() over (order by id desc) RN , * from User_Tables

) PageTab Where RN >= 3001

其中针对 Oracle和Sql2005+的分页写法做个说明。

Oracle使用ROWNUM要比Row_Number()要快。sql示例中均是查询 [3001,3010] 区间的数据,在Sql语句中,尽可能在子查询中减少查询的结果集行数,然后针对排序过后的行号,在外层查询中做条件筛选。 如Oracle写法中 子查询有ROWNUM <= 3010 ,Sql2005 中有 top 3010 * 。

当然今天要讨论的问题,不是分页语句的性能问题,如果你知道更好更快的写法,欢迎交流。

上面的分页写法,基于的查询sql语句是:

select * from User_Tables order by id desc

首先要从Sql语句中分析出行为,我把该Sql拆成了n部分,然后完成了以上拼接功能。按照模子往里面套数据,难度不大。

逆序分页

我们来描述另外一种场景,刚刚演示的sql是查询 满足条件下行数在[3001,3010]之间的数据,如果说总行数仅仅只有3500行,那么结果则是需要查询出3010行数据,并取出最后10条,而前面3000条数据,是没用的。

所以借鉴以前的经验,姑且叫它 逆序分页 。在知道总行数的前提下,我们可以进行分析,是否需要逆序分页,因为逆序分页得到分页Sql语句,也是需要时间的,并非所有的情况都有必要这么做。之前有假设,数据仅仅有3500行,我们期望取出 按照id 倒叙排序后的[3001,3010]数据,换种方式理解,若按照id升序,我们期望取出的数据则是[491,500] 这个区间,然后将这个数据,再按照id倒叙排序,也就是我们需要的数据了。

理论知识差不多就说完了,需要了解更多的话,百度一下,你就知道。下面是代码,有点长,展开当心:

 public enum DBType

{

SqlServer2000,

SqlServer,

Oracle,

SQLite

}

public class Page

{

/// <summary>

/// 数据库类别

/// </summary>

public DBType dbType = DBType.Oracle;

/// <summary>

/// 逆序分页行数,总行数大于MaxRow,则会生成逆序分页SQL

/// </summary>

public int MaxRow = 1000;//临时测试,把值弄小点

/// <summary>

/// 匹配SQL语句中Select字段

/// </summary>

private Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

/// <summary>

/// 匹配SQL语句中Order By字段

/// </summary>

private Regex rxOrderBy = new Regex(@"\b(?<ordersql>ORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+)(?:\s+(?<order>ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

/// <summary>

/// 匹配SQL语句中Distinct

/// </summary>

private Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);

private string[] SplitSqlForPaging(string sql)

{

/*存储分析过的SQL信息 依次为:

* 0.countsql

* 1.pageSql(保留位置此处不做分析)

* 2.移除了select的sql

* 3.order by 字段 desc

* 4.order by 字段

* 5.desc

*/

var sqlInfo = new string[6];

// Extract the columns from "SELECT <whatever> FROM"

var m = rxColumns.Match(sql);

if (!m.Success)

return null;

// Save column list and replace with COUNT(*)

Group g = m.Groups[1];

sqlInfo[2] = sql.Substring(g.Index);

if (rxDistinct.IsMatch(sqlInfo[2]))

sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);

else

sqlInfo[0] = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);

// Look for an "ORDER BY <whatever>" clause

m = rxOrderBy.Match(sqlInfo[0]);

if (!m.Success)

{

sqlInfo[3] = null;

}

else

{

g = m.Groups[0];

sqlInfo[3] = g.ToString();

//统计的SQL 移除order

sqlInfo[0] = sqlInfo[0].Substring(0, g.Index) + sqlInfo[0].Substring(g.Index + g.Length);

//存储排序信息

sqlInfo[4] = m.Groups["ordersql"].Value;//order by xxx

sqlInfo[5] = m.Groups["order"].Value;//desc

//select部分 移除order

sqlInfo[2] = sqlInfo[2].Replace(sqlInfo[3], string.Empty);

}

return sqlInfo;

}

/// <summary>

/// 生成逆序分页Sql语句

/// </summary>

/// <param name="sql"></param>

/// <param name="sqls"></param>

/// <param name="start"></param>

/// <param name="limit"></param>

/// <param name="total"></param>

public void CreatePageSqlReverse(string sql,ref string[] sqls, int start, int limit, int total = 0)

{

//如果总行数不多或分页的条数位于前半部分,没必要逆序分页

if (total < 100 || start <= total / 2)

{

return;

}

//sql正则分析过后的数组有5个值,若未分析,此处分析

if (sqls == null || sqls.Length == 6)

{

sqls = SplitSqlForPaging(sql);

if (sqls == null)

{

//无法解析的SQL语句

throw new Exception("can't parse sql to pagesql ,the sql is " + sql);

}

}

//如果未定义排序规则,则无需做逆序分页计算

if (string.IsNullOrEmpty(sqls[5]))

{

return;

}

//逆序分页检查

string sqlOrder = sqls[3];

int end = start + limit;

//获取逆序排序的sql

string sqlOrderChange = string.Compare(sqls[5], "desc", true) == 0 ?

string.Format("{0} ASC ", sqls[4]) :

string.Format("{0} DESC ", sqls[4]);

/*理论

* total:10000 start:9980 limit:10

* 则 end:9990 分页条件为 RN >= 9980+1 and RN <= 9990

* 逆序调整后

* start = total - start = 20

* end = total - end = 10

* 交换start和end,分页条件为 RN >= 10+1 and RN<= 20

*/

//重新计算start和end

start = total - start;

end = total - end;

//交换start end

start = start + end;

end = start - end;

start = start - end;

//定义分页SQL

var pageSql = new StringBuilder();

if (dbType == DBType.SqlServer2000)

{

pageSql.AppendFormat("SELECT TOP @PageLimit * FROM ( SELECT TOP @PageEnd {0} {1} ) ", sqls[2], sqlOrderChange);

}

else if (dbType == DBType.SqlServer)

{

//组织分页SQL语句

pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1} ) PageTab ",

sqlOrderChange,

sqls[2]);

//如果查询不是第一页,则需要判断起始行号

if (start > 1)

{

pageSql.Append("Where RN >= :PageStart ");

}

}

else if (dbType == DBType.Oracle)

{

pageSql.AppendFormat("SELECT ROWNUM RN, PageTab.* FROM ( Select {0} {1} ) PageTab where ROWNUM <= :PageEnd ", sqls[2], sqlOrderChange);

//如果查询不是第一页,则需要判断起始行号

if (start > 1)

{

pageSql.Insert(0, "SELECT * FROM ( ");

pageSql.Append(" ) ");

pageSql.Append(" WHERE RN>= :PageStart ");

}

}

else if (dbType == DBType.SQLite)

{

pageSql.AppendFormat("SELECT * FROM ( SELECT {0} {1} limit @PageStart,@PageLimit ) PageTab ", sqls[2], sqlOrderChange);

}

//恢复排序

pageSql.Append(sqlOrder);

//存储生成的分页SQL语句

sqls[1] = pageSql.ToString();

//临时测试

sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");

Console.WriteLine("【count】{0}", sqls[0]);

Console.WriteLine("【page】{0}", sqls[1]);

Console.WriteLine();

}

/// <summary>

/// 生成常规Sql语句

/// </summary>

/// <param name="sql"></param>

/// <param name="sqls"></param>

/// <param name="start"></param>

/// <param name="limit"></param>

/// <param name="createCount"></param>

public void CreatePageSql(string sql, out string[] sqls, int start, int limit, bool createCount = false)

{

//需要输出的sql数组

sqls = null;

//生成count的SQL语句 SqlServer生成分页,必须通过正则拆分

if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)

{

sqls = SplitSqlForPaging(sql);

if (sqls == null)

{

//无法解析的SQL语句

throw new Exception("can't parse sql to pagesql ,the sql is " + sql);

}

}

else

{

sqls = new string[2];

}

//组织分页SQL语句

var pageSql = new StringBuilder();

var end = start + limit;

if (dbType == DBType.SqlServer2000)

{

pageSql.AppendFormat("SELECT TOP @PageEnd {0} {1}", sqls[2], sqls[3]);

if (start > 1)

{

var orderChange = string.IsNullOrEmpty(sqls[5]) ? null :

string.Compare(sqls[5], "desc", true) == 0 ?

string.Format("{0} ASC ", sqls[4]) :

string.Format("{0} DESC ", sqls[4]);

pageSql.Insert(0, "SELECT TOP 100 PERCENT * FROM (SELECT TOP @PageLimit * FROM ( ");

pageSql.AppendFormat(" ) PageTab {0} ) PageTab2 {1}", orderChange, sqls[3]);

}

}

else if (dbType == DBType.SqlServer)

{

pageSql.AppendFormat(" Select top @PageEnd ROW_NUMBER() over ({0}) RN , {1}",

string.IsNullOrEmpty(sqls[3]) ? "ORDER BY (SELECT NULL)" : sqls[3],

sqls[2]);

//如果查询不是第一页,则需要判断起始行号

if (start > 1)

{

pageSql.Insert(0, "Select PageTab.* from ( ");

pageSql.Append(" ) PageTab Where RN >= @PageStart");

}

}

else if (dbType == DBType.Oracle)

{

pageSql.Append("select ROWNUM RN, PageTab.* from ");

pageSql.AppendFormat(" ( {0} ) PageTab ", sql);

pageSql.Append(" where ROWNUM <= :PageEnd ");

//如果查询不是第一页,则需要判断起始行号

if (start > 1)

{

pageSql.Insert(0, "select * from ( ");

pageSql.Append(" ) Where RN>= :PageStart ");

}

}

else if (dbType == DBType.SQLite)

{

pageSql.AppendFormat("{0} limit @PageStart,@PageLimit", sql, start, limit);

}

//存储生成的分页SQL语句

sqls[1] = pageSql.ToString();

//临时测试

sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");

Console.WriteLine("【count】{0}", sqls[0]);

Console.WriteLine("【page】{0}", sqls[1]);

Console.WriteLine();

}

}

具体实现

其他

1.交换2个整数用了这样的算法。交换a和b,a=a+b;b=a-b;b=a-b;这是原来找工作的时候被考到的,如果在不使用第三方变量的情况下交换2个整数。

2.Sql2000下由于是使用top进行分页,除非条件一条数据都查不到,否则在分页start和limit参数超过了总行数时,也会查询出数据。另外,在使用Top时,若想使用top @ncount ,必须写成 top (@ncount) ,目测sql2005+支持,sql2000下未知,没有测试环境。

3.拆分Sql语句,参考了PetaPoco的部分源代码。

4.我的应用场景则是在dbhelp类,某个方法传递sql,start,limit参数即可对sql查询出来的结果进行分页。其中start:查询结果的起始行号(不包括它),limit:需要取出的行数。如 start:0,limit:15 则是取出前15条数据。

 

测试代码下载


相关阅读:
Top