各数据库分页语法支持

Mycat后端通过jdbc接入oracle、db2、sqlserver、postgresql等通过以下2种方式支持分页语法

数据库原生分页直接解析

应用端可以通过直接执行原生分页sql到mycat，各数据库原生分页支持如下：

oracle支持三层嵌套和row_number两种分页，以及rownum控制最大条数

select * from ( select row_.*, rownum rownum_ from ( select sidfrom test where sts<>'N' order by sid desc ) row_ where rownum<= 15) where rownum_ > 5;

SELECT *FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid ) AS ROWNUM1FROM test tWHERE sts<> 'N') XXWHERE ROWNUM1> 5AND ROWNUM1<= 15;SELECT  * FROM (SELECT * FROM test t) XXWHERE    ROWNUM <= 5;

db2支持rownumber分页和fetch first rows only语法

SELECT *FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid ) AS ROWNUMFROM test tWHERE sts<> 'N') XXWHERE ROWNUM > 5AND ROWNUM <= 15;

SELECT sidFROM test ORDER BY sid descFETCH FIRST 15 ROWS ONLY;

Sqlserver支持row_number和row_number与top结合2种分页以及top限制最大条数

SELECT *FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid DESC) AS ROWNUMFROM testWHERE sts<> 'N') XXWHERE ROWNUM > 10AND ROWNUM <= 25

select * from (select row_number() over(order by tempColumn) tempRowNumber,* from (select top 30 tempColumn=0,sid from test where sts<>'N'order by sid)t)tt where tempRowNumber>20;

postgresql分页

select sid from test order by sid desc limit 10 offset 5;

等价于mysql的

select sid from test order by sid desc limit 5,10;

其实mysql也兼容limit offset写法

Limit语法自动转换原生分页

select sid from test order by sid desc limit 5,10;

通过将标准的limit语法自动翻译转换为各数据库的原生分页，具体可以通过explain命令查看。