mysql
limit
SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 即 6,7,8,9,10,11,12,13,14,15 共10条记录
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
SELECT * FROM table LIMIT 5; //检索前 5 个记录行
offset
SELECT * FROM table LIMIT 5,10; 检索记录行 6-15
SELECT * FROM table LIMIT 10 offset 5 即表示检索记录行 6-15 与上面的互换位置
pageIndex,pageSize
(pageIndex-1)*pageSize
pageSize
String originSql,int pageIndex ,int pageSize
StringBuilder sb = new StringBuilder();
sb.append(originSql);
sb.append(" limit ");
sb.append(pageSize);
sb.append(" offset ");
sb.append( (pageIndex-1)*pageSize );
sb.toString();
分页优化主键优先原则
- 查询100001条id limit 100000,1
- 查询100001条id后的第100条id
id>=(select id from xxx limit 1000,1) limit 100 - 例子主键子查询
select * from order where id>=(
select id from orders limit 10000,1
) limit 100
SELECT * FROM articles WHERE id >=
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10
- 例子主键方式
select * from order where id>=1000001 limit 100;
oracle
String originSql,int pageIndex ,int pageSize
StringBuilder sb= new StringBuilder();
sb.append("select * from (select tmp_tb.*,ROWNUM row_id from (");
sb.append(originSql);
sb.append(") tmp_tb where ROWNUM<=");
sb.append(pageIndex*pageSize);
sb.append(") where row_id>");
sb.append((pageIndex-1)*pageSize);
sb.toString();
db2
String originSql,int pageIndex ,int pageSize
StringBuilder sb= new StringBuilder();
sb.append("SELECT * FROM (SELECT TEMP_TAB.*,ROWNUMBER() OVER() AS IDX FROM (");
sb.append(originalSql);
sb.append(" ) AS TEMP_TAB) AS TEMP_TAB_WITH_IDX WHERE TEMP_TAB_WITH_IDX.IDX> ");
sb.append((pageIndex-1)*pageSize);
sb.append(" AND TEMP_TAB_WITH_IDX.IDX<=");
sb.append(pageIndex*pageSize);
sb.toString();