技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 SELECT 查询详解:筛选、排序、分组、连接与分页

MySQL8专栏503 次阅读

SELECT 是 MySQL 中最常用的查询语句,可以完成列选择、条件筛选、排序、分组聚合、表连接、子查询和分页。本文使用 sys_user、orders 等示例表,系统讲解 MySQL 8 的查询方法和常见错误。

一、SELECT 基本结构

SELECT [DISTINCT] select_list
FROM table_name
[JOIN ...]
[WHERE condition]
[GROUP BY group_expression]
[HAVING group_condition]
[ORDER BY sort_expression]
[LIMIT row_count OFFSET offset];

并不是每条查询都必须包含所有子句。最简单的 SELECT 甚至不需要数据表。

二、无表查询

查询当前时间、执行计算或调用函数:

SELECT NOW() AS current_time;
SELECT 1 + 1 AS result;
SELECT VERSION() AS mysql_version;

MySQL 允许省略 FROM DUAL。下面两种写法结果相同:

SELECT NOW();
SELECT NOW() FROM DUAL;

三、查询全部列与指定列

-- 查询全部列
SELECT *
FROM sys_user;

-- 只查询需要的列
SELECT id, username, email
FROM sys_user;

生产代码建议明确写出需要的列,而不是长期使用 SELECT *:

  • 减少不必要的数据传输。
  • 避免表结构新增字段后影响程序。
  • 更容易利用覆盖索引。
  • 查询结果的字段含义更清晰。

四、字段别名和计算列

使用 AS 为字段或表达式设置别名:

SELECT id,
       username AS user_name,
       age,
       age + 1 AS age_next_year
FROM sys_user;

AS 可以省略,但保留它通常更容易阅读。别名只改变查询结果的列名,不会修改表结构。

五、DISTINCT 去除重复结果

SELECT DISTINCT city
FROM sys_user;

多列 DISTINCT 判断的是列组合:

SELECT DISTINCT city, status
FROM sys_user;

只有 city 和 status 都相同的结果行才会被合并。不要为了掩盖错误连接产生的重复数据而盲目添加 DISTINCT。

六、WHERE 条件筛选

1. 比较运算符

SELECT id, username, age
FROM sys_user
WHERE username = 'Helen';

SELECT id, username, age
FROM sys_user
WHERE age >= 18;

常见比较运算符:

运算符含义
=等于
<> 或 !=不等于
>、>=大于、大于等于
<、<=小于、小于等于

2. BETWEEN 范围查询

SELECT id, username, age
FROM sys_user
WHERE age BETWEEN 18 AND 30;

BETWEEN 18 AND 30 包含 18 和 30。等价写法为:

SELECT id, username, age
FROM sys_user
WHERE age >= 18
  AND age <= 30;

3. IN 多值查询

SELECT id, username, age
FROM sys_user
WHERE age IN (3, 7, 18);

SELECT id, username
FROM sys_user
WHERE username IN ('Yvonne', 'Chloe', 'Maite');

原写法如果缺少 IN,例如 WHERE username (...),属于语法错误。

4. NOT BETWEEN 与 NOT IN

SELECT id, username, age
FROM sys_user
WHERE age NOT BETWEEN 0 AND 7;

SELECT id, username, age
FROM sys_user
WHERE age NOT IN (3, 7, 9);

NOT IN 列表中如果包含 NULL,可能因为 SQL 三值逻辑导致结果与直觉不同。列表来自子查询时,应明确排除 NULL,或改用适合的 NOT EXISTS。

七、AND、OR 与 NOT

SELECT id, username, age, status
FROM sys_user
WHERE status = 1
  AND age >= 18;

AND 的优先级高于 OR。混合使用时应添加括号表达真实意图:

SELECT id, username, age, status
FROM sys_user
WHERE status = 1
  AND (age < 18 OR age >= 60);

如果省略括号,查询含义可能发生变化,即使语法仍然正确。

八、LIKE 模糊查询

  • %:匹配任意数量的字符,包括零个字符。
  • _:匹配一个字符。
-- 以 A 开头
SELECT id, username
FROM sys_user
WHERE username LIKE 'A%';

-- 以“裙”结尾
SELECT id, username
FROM sys_user
WHERE username LIKE '%裙';

-- 三个字符,最后一个是“女”
SELECT id, username
FROM sys_user
WHERE username LIKE '__女';

查询值本身包含 % 或 _ 时,需要转义:

SELECT id, username
FROM sys_user
WHERE username LIKE '%10\%%' ESCAPE '\\';

以 % 开头的条件通常难以使用普通 B-Tree 索引完成前缀定位。数据量较大时,应根据业务评估全文索引、搜索引擎或其他检索方式。

九、NULL 与空字符串

NULL 表示未知或缺失,空字符串 '' 是长度为 0 的已知字符串,两者不是同一个值。

-- 查询 NULL
SELECT id, username, email
FROM sys_user
WHERE email IS NULL;

-- 查询非 NULL
SELECT id, username, email
FROM sys_user
WHERE email IS NOT NULL;

-- 查询空字符串
SELECT id, username, email
FROM sys_user
WHERE email = '';

不要写成:

-- 错误示例:不会按预期匹配 NULL
SELECT *
FROM sys_user
WHERE email = NULL;

任何普通比较只要涉及 NULL,结果通常也是 NULL,而不是 true。MySQL 还提供 NULL 安全等号 <=>:

SELECT NULL <=> NULL AS both_null,
       email <=> NULL AS email_is_null
FROM sys_user;

COUNT(*) 与 COUNT(column)

SELECT COUNT(*) AS total_rows,
       COUNT(email) AS non_null_emails
FROM sys_user;

COUNT(*) 统计行数,COUNT(email) 忽略 email IS NULL 的记录,但会统计空字符串。

十、ORDER BY 排序

-- 年龄升序,ASC 可以省略
SELECT id, username, age
FROM sys_user
ORDER BY age ASC;

-- 年龄降序
SELECT id, username, age
FROM sys_user
ORDER BY age DESC;

多列排序按从左到右依次处理:

SELECT id, username, age
FROM sys_user
ORDER BY age ASC, id ASC;

当多个用户年龄相同时,再按 id 升序排列。加入唯一列作为最后排序条件,可获得稳定、可重复的顺序。

十一、LIMIT 与分页

查询前 10 条:

SELECT id, username, age
FROM sys_user
ORDER BY id ASC
LIMIT 10;

跳过前 5 条,再返回 10 条:

SELECT id, username, age
FROM sys_user
ORDER BY id ASC
LIMIT 10 OFFSET 5;

MySQL 也支持 LIMIT 5, 10,其中第一个参数是偏移量,第二个参数是返回数量。但 LIMIT row_count OFFSET offset 更容易阅读。

分页必须有稳定排序

如果只按非唯一字段排序,相同值之间的顺序不确定,翻页时可能出现重复或遗漏。应增加唯一列:

SELECT id, username, created_at
FROM sys_user
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

深分页优化

偏移量很大时,MySQL 仍需扫描并丢弃前面的记录。可以使用上一次结果的排序键进行游标分页:

SELECT id, username, created_at
FROM sys_user
WHERE (created_at, id) < ('2026-11-14 10:00:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

该方式要求排序字段和比较条件保持一致,并建立合适的联合索引。

十二、聚合函数

常用聚合函数包括 COUNT()、SUM()、AVG()、MIN() 和 MAX():

SELECT COUNT(*) AS user_count,
       MIN(age) AS min_age,
       MAX(age) AS max_age,
       AVG(age) AS average_age
FROM sys_user
WHERE status = 1;

除 COUNT(*) 外,多数聚合函数会忽略表达式中的 NULL。

十三、GROUP BY 分组

统计每个状态的用户数:

SELECT status,
       COUNT(*) AS user_count
FROM sys_user
GROUP BY status
ORDER BY status;

多字段分组:

SELECT city,
       status,
       COUNT(*) AS user_count
FROM sys_user
GROUP BY city, status
ORDER BY city, status;

ONLY_FULL_GROUP_BY

MySQL 8 默认 SQL 模式通常包含 ONLY_FULL_GROUP_BY。查询列必须是聚合表达式、分组列,或能由分组列函数依赖确定。

-- 不规范:username 既没有聚合,也不在 GROUP BY 中
SELECT status, username, COUNT(*)
FROM sys_user
GROUP BY status;

不要通过关闭 ONLY_FULL_GROUP_BY 掩盖含义不明确的查询,应明确需要哪一行数据或使用适当的聚合方式。

十四、HAVING 分组后筛选

WHERE 在分组前过滤原始记录,HAVING 在聚合后过滤结果:

SELECT city,
       COUNT(*) AS user_count,
       ROUND(AVG(age), 2) AS average_age
FROM sys_user
WHERE status = 1
GROUP BY city
HAVING COUNT(*) >= 10
ORDER BY user_count DESC;

HAVING 常与 GROUP BY 配合,但不能简单说没有 GROUP BY 就绝对不能使用。对于普通行级条件,应优先放在 WHERE,让数据库尽早减少参与分组的数据。

十五、表连接 JOIN

INNER JOIN

只返回两张表能够匹配的记录:

SELECT o.id,
       o.order_no,
       u.username,
       o.amount
FROM orders AS o
INNER JOIN sys_user AS u
  ON u.id = o.user_id
WHERE o.status = 1
ORDER BY o.id DESC;

LEFT JOIN

保留左表全部记录,右表无法匹配时返回 NULL:

SELECT u.id,
       u.username,
       COUNT(o.id) AS order_count
FROM sys_user AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
GROUP BY u.id, u.username
ORDER BY u.id;

ON 与 WHERE 的区别

对 LEFT JOIN 右表的过滤条件放在 WHERE 中,可能把没有匹配行的结果过滤掉,使查询效果接近内连接:

SELECT u.id, u.username, o.id AS order_id
FROM sys_user AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id
 AND o.status = 1;

上面把订单状态放在 ON 中,仍会保留没有有效订单的用户。

十六、子查询与 EXISTS

查询存在有效订单的用户:

SELECT u.id, u.username
FROM sys_user AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
    AND o.status = 1
);

EXISTS 只关心子查询是否返回记录,SELECT 1 不表示只查询 ID 为 1 的记录。

查询没有订单的用户:

SELECT u.id, u.username
FROM sys_user AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

当子查询字段可能包含 NULL 时,NOT EXISTS 往往比 NOT IN 更不容易产生三值逻辑问题。

十七、SELECT 的逻辑处理顺序

便于理解的逻辑顺序通常是:

FROM / JOIN
→ WHERE
→ GROUP BY
→ HAVING
→ SELECT
→ DISTINCT
→ ORDER BY
→ LIMIT

这解释了为什么 WHERE 通常不能直接引用同级 SELECT 中刚定义的别名,而 ORDER BY 可以使用结果列别名。

SELECT age + 1 AS next_age
FROM sys_user
WHERE age + 1 >= 18
ORDER BY next_age;

实际执行计划由优化器决定,不会机械地按文本顺序逐步执行。逻辑顺序用于理解查询语义,性能分析应查看执行计划。

十八、使用 EXPLAIN 查看执行计划

EXPLAIN
SELECT id, username, age
FROM sys_user
WHERE status = 1
  AND age >= 18
ORDER BY age, id
LIMIT 20;

MySQL 8 还可使用 EXPLAIN ANALYZE 实际执行查询并显示运行统计:

EXPLAIN ANALYZE
SELECT id, username, age
FROM sys_user
WHERE status = 1
  AND age >= 18
ORDER BY age, id
LIMIT 20;

EXPLAIN ANALYZE 会真实执行语句,生产环境使用前要评估查询开销。

十九、应用程序中的安全查询

不要把用户输入直接拼接进 SQL:

// 错误示例
$sql = "SELECT * FROM sys_user WHERE username = '" . $_GET['name'] . "'";

应使用预处理参数:

$stmt = $pdo->prepare(
    'SELECT id, username, email
     FROM sys_user
     WHERE username = ?'
);
$stmt->execute([$username]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

参数绑定只能绑定值,不能直接绑定表名、列名或排序方向。动态排序字段应在服务器端白名单中选择。

二十、常见错误

  • 长期使用 SELECT *,读取大量不需要的列。
  • 使用 = NULL 或 <> NULL 判断空值。
  • AND、OR 混用时不加括号。
  • 使用 LIMIT 分页却没有稳定的 ORDER BY。
  • 深分页使用非常大的 OFFSET,没有考虑游标分页。
  • 在 GROUP BY 查询中选择含义不明确的非聚合列。
  • 把 LEFT JOIN 右表条件错误地放进 WHERE。
  • 用 DISTINCT 掩盖错误连接产生的重复行。
  • 把用户输入直接拼接进 SQL。
  • 只凭感觉添加索引,不查看执行计划和真实数据分布。

二十一、完整查询示例

查询有效用户的订单统计,只显示订单数不少于 3 的用户,按消费总额降序分页:

SELECT u.id,
       u.username,
       COUNT(o.id) AS order_count,
       ROUND(SUM(o.amount), 2) AS total_amount,
       MAX(o.created_at) AS latest_order_time
FROM sys_user AS u
INNER JOIN orders AS o
  ON o.user_id = u.id
 AND o.status = 1
WHERE u.status = 1
GROUP BY u.id, u.username
HAVING COUNT(o.id) >= 3
ORDER BY total_amount DESC, u.id DESC
LIMIT 20 OFFSET 0;

这个示例包含:

  • JOIN:关联用户和订单。
  • ON:限定有效订单。
  • WHERE:分组前筛选有效用户。
  • GROUP BY:按用户聚合。
  • HAVING:过滤订单数不足的分组。
  • ORDER BY:按总额和唯一 ID 稳定排序。
  • LIMIT:限制当前页数量。

二十二、官方文档

二十三、总结

SELECT 的核心流程是确定数据来源、筛选记录、分组聚合、筛选分组、排序并限制结果。写查询时应明确列、正确处理 NULL、保持分页排序稳定,并通过参数化查询和执行计划兼顾安全与性能。

读书和赚钱都是一个人最好的修行,前者使人不惑,后者使人不屈,二者结合,便可不困于世,不流于俗

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读