当数据表中的记录越来越多的时候,可以通过分页查询的方法,对数据进行筛选,仅返回当前页面所需的数据内容。Limit是MySQL中的一个特殊关键字。Limit子句可以对查询结果的记录条数进行限定,控制它输出的行数。
在MySQL数据库中创建用户信息表(tb_user),并添加数据用于测试使用。
-- 判断数据表是否存在,存在则删除
DROP TABLE IF EXISTS tb_user;
-- 创建“用户信息”数据表
CREATE TABLE IF NOT EXISTS tb_user
(
user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
user_name VARCHAR(50) NOT NULL COMMENT '用户姓名',
province VARCHAR(50) NOT NULL COMMENT '省份'
) COMMENT = '用户信息表';
-- 添加数据
INSERT INTO tb_user(user_name,province) VALUES
('pan_junbiao的博客_01','广东省'),('pan_junbiao的博客_02','黑龙江省'),('pan_junbiao的博客_03','山东省'),('pan_junbiao的博客_04','安徽省'),('pan_junbiao的博客_05','黑龙江省'),
('pan_junbiao的博客_06','江苏省'),('pan_junbiao的博客_07','黑龙江省'),('pan_junbiao的博客_08','广东省'),('pan_junbiao的博客_09','陕西省'),('pan_junbiao的博客_10','广东省'),
('pan_junbiao的博客_11','广东省'),('pan_junbiao的博客_12','江苏省'),('pan_junbiao的博客_13','陕西省'),('pan_junbiao的博客_14','安徽省'),('pan_junbiao的博客_15','山东省'),
('pan_junbiao的博客_16','陕西省'),('pan_junbiao的博客_17','安徽省'),('pan_junbiao的博客_18','江苏省'),('pan_junbiao的博客_19','黑龙江省'),('pan_junbiao的博客_20','安徽省'),
('pan_junbiao的博客_21','江苏省'),('pan_junbiao的博客_22','广东省'),('pan_junbiao的博客_23','安徽省'),('pan_junbiao的博客_24','陕西省'),('pan_junbiao的博客_25','广东省'),
('pan_junbiao的博客_26','广东省'),('pan_junbiao的博客_27','安徽省'),('pan_junbiao的博客_28','山东省'),('pan_junbiao的博客_29','山东省'),('pan_junbiao的博客_30','黑龙江省'),
('pan_junbiao的博客_31','广东省'),('pan_junbiao的博客_32','江苏省'),('pan_junbiao的博客_33','陕西省'),('pan_junbiao的博客_34','安徽省'),('pan_junbiao的博客_35','山东省');
基本语法格式:
LIMIT m;
m:表示查询多少条记录。
【示例】查询用户信息表(tb_user),按照 user_id 编号进行升序排列,显示前5条记录。
SELECT * FROM tb_user
ORDER BY user_id
LIMIT 5
执行结果:
1、使用一般分页查询(不推荐)
语法格式:
LIMIT m , n;
m:表示开始查询的第一条记录的编号(注意:在查询结果中,第一个结果的记录编号是0,而不是1)。
n:表示查询多少条记录。
DELIMITER $$
-- 方式一:使用一般分页查询(不推荐)
DROP PROCEDURE IF EXISTS proc_user_page$$
CREATE PROCEDURE proc_user_page(IN page_index INT,IN page_size INT)
BEGIN
DECLARE begin_no INT;
SET begin_no = (page_index-1)*page_size;
SELECT * FROM tb_user
ORDER BY user_id ASC
LIMIT begin_no,page_size;
END$$
DELIMITER ;
调用存储过程:查询第2页,每页5条记录:
-- 调用存储过程:查询第2页,每页5条记录
CALL proc_user_page(2,5);
执行结果:
弊端:方式一虽然实现了分页功能,但随着查询偏移的增大,尤其查询偏移大于10万以后,查询时间将急剧增加。这种分页查询方式会从数据库第一条记录开始扫描,所以越往后,查询速度越慢,而且查询的数据越多,也会拖慢总查询速度。
2、使用子句优化查询(推荐)
DELIMITER $$
-- 方式二:使用子句优化查询(推荐)
DROP PROCEDURE IF EXISTS proc_optimize$$
CREATE PROCEDURE proc_optimize(IN page_index INT,IN page_size INT)
BEGIN
DECLARE begin_no INT;
SET begin_no = (page_index-1)*page_size;
SELECT * FROM tb_user
WHERE user_id >= (
SELECT user_id FROM tb_user
ORDER BY user_id ASC
LIMIT begin_no,1
)
ORDER BY user_id ASC
LIMIT page_size;
END$$
DELIMITER ;
调用存储过程:查询第3页,每页5条记录。
-- 调用存储过程:查询第3页,每页5条记录
CALL proc_optimize(3,5);
执行结果:
优点:方式二适合数据表的id是连续递增的,则可以根据查询的页数和查询的记录数可以算出查询的id的范围。这种查询方式能够极大地优化查询速度,基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况,不过一般建立表的时候,都会添加基本的自增的主键id字段,这为分页查询带来很多便利。