仿orm自动生成分页SQL分享
先看看目前这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参数超过了总行数时，也会查询出数据。
3.拆分sql语句，参考了petapoco的部分源代码。
4.我的应用场景则是在dbhelp类，某个方法传递sql，start，limit参数即可对sql查询出来的结果进行分页。其中start：查询结果的起始行号(不包括它)，limit：需要取出的行数。如 start:0,limit:15 则是取出前15条数据。