[Mysql] 分页查询

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

SELECT语句返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，在MySQL数据库中使用LIMIT子句进行分页查询

LIMIT子句在查询语句最后面的位置进行编写

引入示例

导入数据

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `order_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品订单号',
  `cust_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '顾客id'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `orders` VALUES ('a0001', 'cust10');
INSERT INTO `orders` VALUES ('a0002', 'cust1');
INSERT INTO `orders` VALUES ('a0003', 'cust1');
INSERT INTO `orders` VALUES ('a0013', 'cust2');
INSERT INTO `orders` VALUES ('a0004', 'cust3');
INSERT INTO `orders` VALUES ('a0005', 'cust12');
INSERT INTO `orders` VALUES ('a0006', 'cust8');
INSERT INTO `orders` VALUES ('a0007', 'cust9');
INSERT INTO `orders` VALUES ('a0008', 'cust11');
INSERT INTO `orders` VALUES ('a0009', 'cust15');
INSERT INTO `orders` VALUES ('a0010', 'cust4');

orders表

SELECT order_num
FROM orders
LIMIT 5;

结果展示:

LIMIT 5指示MySQL返回不多于5行的指定数据

得出下一个5行,可指定要检索的开始行和行数

SELECT order_num
FROM orders
LIMIT 5,5;

结果展示:

LIMIT 5, 5指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数 

带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。带两个值的LIMIT可以指定从行号为第一个值的位置开始 

注意:MySQL分页中开始位置为0,(例如,检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行)

SELECT order_num
FROM orders
LIMIT 1,1;

结果展示:

在行数不够时，LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行(例如，给出LIMIT 10, 5，但只有11行)，MySQL将只返回它能返回的那么多行

SELECT order_num
FROM orders
LIMIT 10,5;

结果展示:

通过上面的示例讲解，我们可以了解到分页LIMIT子句:

格式: SELECT * FROM 表名 LIMIT start,count;

start: 开始位置

count: 查询数量

从start开始，获取count条数据

---

已知：每页显示m条数据，当前显示第n页，则第n页的数据的开始位置：(n-1)*m

SELECT *

FROM 表名

LIMIT (n-1)*m,m;

补充: LIMIT OFFSET 子句

分页也可通过LIMIT <M> OFFSET <N>子句实现

M: 查询数量

N: 开始位置

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0

在MySQL中，LIMIT 15 OFFSET 30 还可以简写成LIMIT 30, 15

结合上述示例数据进行讲解:

SELECT order_num
FROM orders
LIMIT 3 OFFSET 0;

结果展示:

LIMIT 3 OFFSET 0 表示对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始

LIMIT 3 表示的意思是“最多3条记录”

假如查询第二页记录: LIMIT 3 OFFSET 3

SELECT order_num
FROM orders
LIMIT 3 OFFSET 3;

结果展示:

类似的，查询第3页的时候，OFFSET应该设定为6，查询第4页的时候，OFFSET应该设定为9

分页查询需要先确定每页的数量和当前页数，然后确定LIMIT和OFFSET的值

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize，然后根据当前页的索引pageIndex(从1开始)，确定LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSize

OFFSET计算公式为pageSize * (pageIndex - 1)

例如:

上述查询第二页记录: LIMIT 3 OFFSET 3

每页显示结果数量为3,所以pageSize: 3

查询第二页记录，即当前页索引为2,pageIndex: 2

综上所述:

LIMIT的值为3

OFFSET的值为3 * (2 - 1) = 3

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集

例如:查询第5页的orders表的order_num(每页数量只有3个) ，已知该表总共有11行记录

SELECT order_num
FROM orders
LIMIT 3 OFFSET 12;

结果展示:

注意: 使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低

---

扩展练习案例

查询结果限制返回行数

导入数据

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

user_profile表

问题:现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果 

-- LIMIT n：从第0+1(m=0)条开始，取n条数据，是LIMIT 0,n的缩写
SELECT device_id
FROM user_profile
LIMIT 2;
 
-- LIMIT m,n:从第m+1条开始，取n条数据
SELECT device_id
FROM user_profile
LIMIT 0,2;
 
-- LIMIT n OFFSET m：从第m+1条开始，取n条数据
SELECT device_id
FROM user_profile
LIMIT 2 OFFSET 0;

结果展示: