MySQL 按结果集分页

女爷i 2022-12-19 08:37 216阅读 0赞

备注:测试数据库版本为MySQL 8.0

# 一.需求 #

给结果集分页或滚动显示整个结果集

例如，先返回表emp中的前5行工资，然后是下5行，依此类推。目标是允许用户一次能够查看5个记录，每次单击"下一页"按钮都会向前桂东5个记录。

# 二.解决方案 #

在SQL中，由于没有“第一个”、“最后一个”及“下一个”的概念，所以必须对要处理的行按某种方式排序，只有如此，才会准确地返回一定范围内记录。

-- MySQL limit offset解决方案
    -- offset表示跳过几行
    select sal
    from emp 
    order by sal
    limit 5 offset 0
    ;
    
    
    select sal
    from emp 
    order by sal
    limit 5 offset 5
    ;
    
    -- MySQL 8.0 分析函数解决方案
    select sal
       from (
    select row_number() over (order by sal) as rn,
           sal
       from emp
            ) x
    where rn between 1 and 5;
    
    
    select sal
       from (
    select row_number() over (order by sal) as rn,
           sal
       from emp
            ) x
    where rn between 6 and 10;

测试记录

mysql> select sal
        -> from emp
        -> order by sal
        -> limit 5 offset 0
        -> ;
    +---------+
    | sal     |
    +---------+
    |  800.00 |
    |  950.00 |
    | 1100.00 |
    | 1250.00 |
    | 1250.00 |
    +---------+
    5 rows in set (0.00 sec)
    
    mysql> select sal
        -> from emp
        -> order by sal
        -> limit 5 offset 5
        -> ;
    +---------+
    | sal     |
    +---------+
    | 1300.00 |
    | 1500.00 |
    | 1600.00 |
    | 2450.00 |
    | 2850.00 |
    +---------+
    5 rows in set (0.00 sec)
    
    mysql>
    mysql> select sal
        ->    from (
        -> select row_number() over (order by sal) as rn,
        ->        sal
        ->    from emp
        ->         ) x
        -> where rn between 1 and 5;
    +---------+
    | sal     |
    +---------+
    |  800.00 |
    |  950.00 |
    | 1100.00 |
    | 1250.00 |
    | 1250.00 |
    +---------+
    5 rows in set (0.00 sec)
    
    mysql> select sal
        ->    from (
        -> select row_number() over (order by sal) as rn,
        ->        sal
        ->    from emp
        ->         ) x
        -> where rn between 6 and 10;
    +---------+
    | sal     |
    +---------+
    | 1300.00 |
    | 1500.00 |
    | 1600.00 |
    | 2450.00 |
    | 2850.00 |
    +---------+
    5 rows in set (0.00 sec)