电脑小知识,值得收藏
Mycat后端通过jdbc接入oracle、db2、sqlserver、postgresql等通过以下2种方式支持分页语法
以防找不到可以先收藏哦!
1数据库原生分页直接解析
应用端可以通过直接执行原生分页sql到mycat,各数据库原生分页支持如下:
oracle支持三层嵌套和row_number两种分页,以及rownum控制最大条数
select * from ( select row_.*, rownum rownum_ from ( select sid
from 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 ROWNUM1
FROM test t
WHERE sts <> 'N'
) XX
WHERE ROWNUM1 > 5
AND ROWNUM1 <= 15;
SELECT * FROM (SELECT * FROM test t) XX WHERE ROWNUM <= 5;
db2支持rownumber分页和fetch first rows only语法
SELECT *
FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid ) AS ROWNUM
FROM test t
WHERE sts <> 'N'
) XX
WHERE ROWNUM > 5
AND ROWNUM <= 15;
SELECT sid
FROM test
ORDER BY sid desc
FETCH FIRST 15 ROWS ONLY;
Sqlserver支持row_number和row_number与top结合2种分页以及top限制最大条数
SELECT *
FROM (SELECT sid, ROW_NUMBER() OVER (ORDER BY sid DESC) AS ROWNUM
FROM test
WHERE sts <> 'N'
) XX
WHERE ROWNUM > 10
AND 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写法
2Limit语法自动转换原生分页
select sid from test order by sid desc limit 5,10;
通过将标准的limit语法自动翻译转换为各数据库的原生分页,具体可以通过explain命令查看。