MariaDB 教程 / 用 ORDER BY 和 LIMIT 排序并分页查询
ORDER BY 决定查询结果的顺序,LIMIT 决定最多返回多少行以及从哪里开始。若价格等排序值可能相同,还应添加唯一的次级排序键;这样在数据不变时,排行榜和分页边界才有明确的结果。
同时定义排序规则和返回数量
“价格最高的三件商品”包含两个要求:按价格降序排列,以及只取前三行。单独写 LIMIT 3 只限制数量,并没有定义哪三件商品应当排在最前面。排序改变的是这次查询的结果顺序,不会把表中保存的记录重新排列。
| 写法 | 含义 | 本例中的用途 |
|---|---|---|
| ASC | 升序,也是默认方向 | 同价商品按编号从小到大排列 |
| DESC | 降序 | 价格较高的商品排在前面 |
| LIMIT 3 | 最多返回三行 | 取出前三件商品 |
| LIMIT 2 OFFSET 2 | 跳过两行,再取最多两行 | 每页两条记录时的第二页 |
每个排序表达式都有自己的方向。在 ORDER BY price DESC, product_id ASC 中,价格降序、编号升序;前面的 DESC 不会同时作用于编号。看起来像数字的字符串也不一定按数值大小排列,应核对列的数据类型与比较规则。
需要先排除不符合要求的商品时,先使用 WHERE 条件与运算符 定义输入行,再对筛选后的结果排序。本节使用的基本语法可对照 MariaDB 官方的 ORDER BY 文档与 LIMIT 文档。
准备包含同价商品的独立练习表
以学习账号 course_user 连接练习服务器,并选择 shopcourse 数据库。下面是完整的建表与数据准备代码,应在 MariaDB 客户端中执行,不是在操作系统命令提示符中执行。也可以按照 命令行与 UTF-8 SQL 文件教程保存文件,再运行 SOURCE C:/mariadb-lab/lesson.sql;;文件路径要换成客户端电脑上的实际位置。
执行前检查 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。products_17_lab 仅供本节使用;如果同名表已经存在,先查看表定义与记录,确认是否为可丢弃的练习对象。不要为了运行示例覆盖业务表,也不要把名称相似当成可以删除的依据。
USE shopcourse;
CREATE TABLE products_17_lab (
product_id INT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
note VARCHAR(100) NULL DEFAULT NULL,
PRIMARY KEY (product_id),
CONSTRAINT ck_p17_price CHECK (price >= 0),
CONSTRAINT ck_p17_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_17_lab
(product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
(2, '볼펜', '문구', 1500.00, 0, ''),
(3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
(4, '스탠드', '생활', 25000.00, 5, NULL),
(5, '파일', '문구', 1500.00, 50, ''),
(6, '케이블', '전자', 8000.00, 0, '호환 확인');
执行后应有六件商品。原始示例保留韩文商品名称和分类,以便不同语言的教程使用相同数据:编号 4 是台灯,编号 3 是马克杯,编号 6 是线缆。其中 3 和 6 的价格都是 8000,编号 2 和 5 的价格都是 1500,正好用于观察相同排序值的处理。编号 1、4 的 note 为 NULL,编号 2、5 则是空字符串。
用唯一的次级键确定同价商品的先后
SELECT product_id, product_name, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 3;
| 位置 | 商品编号 | 商品含义 | 价格 |
|---|---|---|---|
| 1 | 4 | 台灯 | 25000.00 |
| 2 | 3 | 马克杯 | 8000.00 |
| 3 | 6 | 线缆 | 8000.00 |
数据库先比较 price,价格相同时再比较 product_id。这里主键唯一,因此同价记录之间也有明确的先后。只把编号方向从 ASC 改为 DESC,其余部分不动,前三个编号就应变成 4、6、3。核对差异后恢复 ASC,再确认结果回到 4、3、6。
不要因为当前样本恰好没有并列值就省略这个规则。以后插入同价商品时,只有 ORDER BY price DESC 无法规定它们之间的先后;索引或某次执行看起来稳定的顺序,也不能代替查询中的完整排序条件。
理解 OFFSET 计数与数据变化造成的边界移动
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 0;
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 2;
第一条查询取第一页,编号为 4、3;第二条取第二页,编号为 6、1。OFFSET 表示跳过的行数,不是页码。每页两行时,第三页的偏移量是 4,而不是 3。若符合条件的行不足两行,实际返回数量会少于 LIMIT 指定值。
LIMIT 2, 2 也能表达这里的第二页,不过逗号前是偏移量、后是数量。使用 LIMIT 数量 OFFSET 偏移量 可以让两个参数的角色更直观,避免把它们传反。
唯一的次级键只为同一组数据确定顺序,不会冻结两次请求之间的数据。其他请求插入更高价商品、删除前面的记录或更新价格,都可能使页边界移动。例如在当前数据之前增加一件更贵的商品后,同样跳过两行,第二页会从编号 3 开始。需要跨请求保持固定列表时,还要设计快照或其他一致性策略。
此外,返回两行不代表服务器只处理了两行。很大的 OFFSET 可能需要先处理并跳过许多结果;实际成本取决于筛选、排序、索引和执行计划,不能仅凭 LIMIT 较小就断定查询很快。
按上一页的末尾值继续查询,并明确 NULL 的位置
若上一页最后一条是价格 8000、编号 3,接下来应取价格更低的商品,或者价格仍为 8000 但编号更大的商品。筛选条件必须同时对应“价格降序、编号升序”这两个方向:
SELECT product_id, price FROM products_17_lab
WHERE price < 8000 OR (price = 8000 AND product_id > 3)
ORDER BY price DESC, product_id ASC LIMIT 2;
在本例数据不变时,这条查询也返回 6、1,与第二页 OFFSET 查询相同。这种方法称为键集分页(keyset pagination):应用保存上一页末尾的排序值,再以它们作为下一次查询的游标。它适合按当前位置继续读取,但与直接跳到任意页码的需求不同。如果排序字段会变化,也仍需考虑跨请求的一致性。
当可空列参与排序时,还应决定缺失值放在哪里。普通升序排序把 NULL 视为较低的值;下面先对“是否为 NULL”的真假值升序排列,再按备注和编号排列:
SELECT product_id, note FROM products_17_lab
ORDER BY note IS NULL ASC, note ASC, product_id ASC;
非 NULL 备注对应 0,排在对应 1 的 NULL 备注前面。空字符串并不是 NULL,因此编号 2、5 在非 NULL 组内;编号 1、4 被放到最后,并由编号确定次序。非空文本之间的具体先后还受排序规则影响。若结果不符合预期,可以同时显示 note IS NULL 和原始 note,区分空字符串与缺失值;NULL 比较与 COALESCE 教程解释了两者的差异。
排查排序问题并限制应用允许的选项
结果不符合预期时,暂时移除 LIMIT,并把主键以及所有排序表达式一起放到查询结果中。依次检查以下内容:
- 每个表达式是否指定了正确的 ASC 或 DESC,是否误以为一个方向会作用于后面的所有列。
- 价格等数据是否使用了合适的类型,显示的别名是否与实际排序表达式一致。
- 相同值之间是否有唯一的次级键,过滤条件是否选中了预期的输入行。
- 分页请求之间的数据或排序选项是否发生变化,游标是否保存了全部排序值。
如果应用允许用户选择排序方式,应把选择映射到预先允许的列名和方向。值参数占位符通常不能代替任意列名或 SQL 关键字;不要把未经检查的排序文本直接拼进 SQL。分页数量也应验证为有上限的整数,以免一次请求返回过多数据。
重新练习前,确认数据库、表定义和现有记录,并备份需要保留的数据。下面的清理语句只针对本节独立练习表,会同时删除表定义和全部行,普通 ROLLBACK 无法恢复。若没有确认它可以丢弃,就保留该表并改用其他练习名称;不要在事务回滚实验中混入这类清理操作。
DROP TABLE shopcourse.products_17_lab;




