多数据库学习之常见数据库分页查询SQL语法

前言

• 分页原理

  客户端通过传递 start(页码)，pageSize(每页显示的条数) 两个参数去分页查询数据库表中的数据。一般数据库提供的分页函数用法和实际需求不切合(如：MySQL limit m,n)，所以就需要根据实际情况去改写适合分页的语句。

• 计算公式

  offset = (start-1)*pageSize

  limit = pageSize

• PageBean

  
  
      package com.nbsp.common.bean;
  
      import lombok.Data;
  
      import java.io.Serializable;
      import java.util.List;
  
      @Data
      public class PageBean<T> implements Serializable {
  
          private int pageNum; //当前页数
          private int pageSize; //每页显示数
          private int totalPage; //总页数
          private int totalRecord; //总的记录数
          private List<T> data; //当前页面的数据集合
          private int start;
          private int end;
  
          public PageBean() {
          }
  
          public PageBean(int pageNum, int pageSize, int totalRecord) {
              this.pageNum = pageNum;
              this.pageSize = pageSize;
  
              //计算总页数
              this.totalPage=totalRecord%pageSize==0?(totalRecord/pageSize):(totalRecord/pageSize+1);
  
              //计算每页的起始下标
              this.start=(pageNum-1)*pageSize;
              this.end=this.start+pageSize;
          }
      }
  
  
  
  
  

MySQL

• 概述

  OceanBase 和 MariaDB 与MySQL类似

• SQL样例

  分页 sql 格式是：

  
      select * from (%s) temp limit (start-1)*pageSize,pageSize 
  
  
      -- 查询第11条到第20条的数据 
      select * from table limit 10,10;
  
      SELECT * FROM (SELECT t.table_schema, t.table_name AS name, t.table_comment AS comment FROM information_schema.tables t WHERE t.table_type = 'BASE TABLE' AND t.table_schema IN ('xxxx')) tt LIMIT ?, 10000
  
  

Oracle

• 概述

  DM 和 Oracle 类似，但DM的分页也支持MySQL语法

• SQL样例

  分页 sql 格式是：

  
      SELECT * FROM (SELECT TMP.*, ROWNUM ROW_ID FROM ( %s ) TMP WHERE ROWNUM <= limit ) WHERE ROW_ID > offset
  
  

  其中：ROWNUM 是从1开始计数

  
  
      -- 查询第1条数据到10000条数据
      SELECT *
      FROM (SELECT TMP.*, ROWNUM ROW_ID
          FROM (SELECT t.owner table_schema, t.table_name name, tc.comments "COMMENT"
                  FROM all_tables t
                          LEFT JOIN all_tab_comments tc ON t.owner = tc.owner AND t.table_name = tc.table_name
                  WHERE 1 = 1
                  AND t.owner IN ('xxxx')) TMP
          WHERE ROWNUM <= 10000)
      WHERE ROW_ID > 0
  
  
  

SQL Server

• 概述

  SQL Server是多DB多Schema

• SQL样例

  分页格式：

  
      -- 格式一：第一个?(offset从0开始)，第二个?(值固定end = limit)
      %s ORDER BY 1 OFFSET ? ROWS
      FETCH NEXT ? ROWS ONLY;
  
  
      -- 格式二: 第一个?(offset从1开始)，第二个?(值不固定end=offset + limit)
      SELECT *
      FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rowNum
            FROM (%s) as __v) AS subQuery
      WHERE rowNum BETWEEN ? AND ?
  
  
  

  其中：

  ORDER BY 是必须的，一般后面跟查询的columns列表，不排序采用 ORDER BY 1 或者 ORDER BY (SELECT NULL)

  
      
      -- 查询第1条到第10000条的数据 
      SELECT SCHEMA_NAME(t.schema_id) table_schema, t.name, f.value comment
      FROM sys.objects t
              LEFT JOIN sys.extended_properties f ON t.object_id = f.major_id AND f.minor_id = 0
      WHERE t.type = 'U'
      AND SCHEMA_NAME(t.schema_id) IN ('xxx') ORDER BY 1 OFFSET 0 ROWS
      FETCH NEXT 10000 ROWS ONLY;
  
  

DB2

• 概述

• SQL样例

  分页格式：

  
      -- 格式        
      SELECT * FROM (SELECT TMP_PAGE.*,ROWNUMBER() OVER() AS ROW_ID FROM ( %s ) AS TMP_PAGE) TMP WHERE ROW_ID BETWEEN ? AND ?
  
  
  

  其中：

  第一个?：start = offset + 1，从1开始
  第二个?：end = offset + limit，累加值

  样例：

  
  
    
    SELECT *
    FROM (SELECT TMP_PAGE.*, ROWNUMBER() OVER () AS ROW_ID
          FROM (SELECT TRIM(t.TABSCHEMA) table_schema, t.TABNAME name, t.REMARKS comment
                FROM SYSCAT.TABLES t
                WHERE t.TYPE = 'T'
                  AND t.TABSCHEMA IN ('xxx')) AS TMP_PAGE) TMP
    WHERE ROW_ID BETWEEN 1 AND 10;  
    
  
  
  

PostgreSQL

• 概述

  Kingbase与PostgreSQL类似，都是多db多schema架构

• SQL样例

  
    SELECT * FROM (%s) tt LIMIT ? OFFSET ?
  
    
  
  

  其中：

  第一个?：是指批次分页数量，固定值
  第二个?：偏移量，从0开始

  
    -- 查询第1条到第10条的数据 
    SELECT *
    FROM (SELECT t.nspname table_schema, c.relname name, cast(obj_description(c.relfilenode, 'pg_class') AS varchar) comment
          FROM pg_class c
                  LEFT JOIN pg_namespace t ON c.relnamespace = t.oid
          WHERE c.relkind = 'r'
            AND t.nspname IN ('xxx')) tt
    LIMIT 10 OFFSET 0;
  
  
  

GBase8S(待补充)

Hana

• 概述

  内存型数据库，实测发现分页越往后，查询效率基本一致

• SQL样例

  分页 sql 格式是：

  
      select * from (%s) temp limit n offset m 
  
  
      -- 查询第1条到第10000条的数据 
      SELECT *
      FROM (SELECT t.SCHEMA_NAME AS table_schema,
                  t.TABLE_NAME  AS name,
                  t.COMMENTS    AS comment,
                  t.IS_TEMPORARY,
                  t.HAS_PRIMARY_KEY
          FROM SYS.TABLES t
          WHERE 1 = 1
              AND t.schema_name IN ('DB_SOURCE')) tt LIMIT 10000
      OFFSET 0
  
  
  

Hive(待补充)

Kudu(待补充)

参考连接

• Java-常用实现分页查询