5.10 分页语法

  1. 准备分页(v2.0.12)
    mysql> /*paging-prepare=true*/select id from test order by id limit 100,1000; -- 如果不带 offset,则offset 为0, 老版本不支持offset
    +----------------------------------------------------------------+
    | paging_id                                                      |
    +----------------------------------------------------------------+
    | 20180419160719030040042072033030006020000000000001000000001000 |
    +----------------------------------------------------------------+
    1 row in set (0.05 sec)
    
  2. 使用paging_id取数据

    mysql> /*paging-id=20180419160719030040042072033030006020000000000001000000001000*/select id from test order by id limit 500,1;
    +-----+
    | id  |
    +-----+
    | 501 |
    +-----+
    1 row in set (0.01 sec)
    
  3. 使用paging-id=xxx, paging-rows=true取条数

mysql> /*paging-rows=true,paging-id=20180419160719030040042072033030006020000000000001000000001000*/select id from test order by id limit 500,1;
+-----+
| id  |
+-----+
| 9999 |
+-----+
1 row in set (0.01 sec)

分页说明

  1. 第一次执行分页结果取回,会触发分页结果(如100W行)做全局缓存。
  2. 缓冲容量,单后端server 默认1G内存,单query默认最大限制100W行,可调整。
  3. MySQL客户端连接HDB,使用分页功能时,需增加-c参数(mysql -uxxx -pyyy -c),否则/*page-prepare=true*/,/*paging-id=xxxx*/不会生效。

results matching ""

    No results matching ""