MySQL 排序与分页

ORDER BY/LIMIT/OFFSET 控制结果输出 · 难度：入门 · +15XP

排序与分页

ORDER BY — 排序

SELECT * FROM products ORDER BY price ASC;        -- 升序（默认）
SELECT * FROM products ORDER BY price DESC;       -- 降序
SELECT * FROM products ORDER BY category, price;  -- 多列排序：先分类后价格

LIMIT + OFFSET — 分页

SELECT * FROM articles ORDER BY created_at DESC LIMIT 20;         -- 前20条
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10 OFFSET 0;  -- 第1页
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10 OFFSET 10; -- 第2页

分页公式

第 N 页，每页 M 条：LIMIT M OFFSET (N-1) * M

⚠️ 大 OFFSET 性能差（如 OFFSET 100000）。用游标分页：WHERE id > last_id ORDER BY id LIMIT 20。

实际应用

-- 销量 Top 10
SELECT name, sales FROM products ORDER BY sales DESC LIMIT 10;
-- 最新 5 个用户
SELECT name, created_at FROM users ORDER BY created_at DESC LIMIT 5;
-- 最便宜的 3 个商品（排除 0 元）
SELECT name, price FROM products WHERE price > 0 ORDER BY price ASC LIMIT 3;