MySQL 系列 #12：MySQL SQL 函数与查询技巧

汇总 MySQL 常用内置函数（数学/字符串/时间/条件判断）和高级查询技巧（窗口函数、CTE、EXPLAIN 分析），是日常 SQL 开发的实战速查手册。

MySQL 系列：MySQL 简介 · MySQL 索引原理与优化 · MySQL 实战技巧与常见陷阱

章节	说明
WHERE vs HAVING	过滤时机不同，用错报错或性能差
EXISTS vs IN	执行逻辑差异、性能选择、NOT IN 的 NULL 陷阱
聚合函数	COUNT/SUM/AVG/MAX/MIN
数学函数	取整、绝对值、求余
字符串函数	拼接、截取、长度、替换
时间函数	提取、格式化、计算
条件判断函数	IF、CASE WHEN、IFNULL/COALESCE
行转列与列转行	CASE WHEN 聚合、UNION ALL 展开
窗口函数（MySQL 8+）	分组排名、滑动计算、取 top-1
公用表表达式 CTE（MySQL 8+）	WITH 子句，替代子查询
EXPLAIN 执行计划分析	读懂执行计划，定位慢查询根因

WHERE vs HAVING

对比	WHERE	HAVING
执行时机	分组前过滤（逐行）	分组后过滤（逐组）
能否用聚合函数	❌ 不能	✅ 可以
能否用别名	❌ 不能（别名在 SELECT 后才确定）	✅ 部分数据库支持
性能	更好（减少参与分组的行数）	较差（先分组再过滤）

-- ❌ 错误：WHERE 里不能用聚合函数
SELECT city, COUNT(*) FROM orders WHERE COUNT(*) > 10 GROUP BY city;

-- ✅ 正确：聚合条件用 HAVING
SELECT city, COUNT(*) AS cnt
FROM orders
GROUP BY city
HAVING cnt > 10;

-- ✅ 最优：先用 WHERE 缩小范围，再用 HAVING 过滤聚合结果
SELECT city, COUNT(*) AS cnt
FROM orders
WHERE status = 'paid'       -- 先过滤，减少参与聚合的行数
GROUP BY city
HAVING cnt > 10;            -- 再过滤聚合结果

EXISTS vs IN

两者都用于子查询过滤，但执行逻辑不同，NULL 处理更是面试高频考点。

执行逻辑对比

IN：先执行子查询得到值集合，再对主查询每行做集合匹配。

SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE city = '北京'
);
-- 执行顺序：子查询先跑 → 得到 id 列表 → 主查询过滤

EXISTS：对主查询每一行，执行子查询判断是否有匹配行，找到一行即停止。

SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM users u
  WHERE u.id = o.user_id AND u.city = '北京'
);
-- 执行顺序：主查询取一行 → 检查子查询是否有返回 → 有则保留

性能选择

场景	推荐	原因
子查询结果集小，主表大	`IN`	子查询只执行一次，得到小集合后快速比对
子查询结果集大，主表小	`EXISTS`	找到匹配即停，避免构建大集合
子查询 join 字段有索引	`EXISTS` 略优	每次关联走索引，命中快

MySQL 8.0+ 优化器会对 IN 做半连接（semi-join）优化，很多情况下会自动转成等价的 EXISTS，实际差距已很小。索引是否命中才是关键。

最重要的陷阱：NOT IN 遇到 NULL

-- 假设 users 表有一行 id = NULL 的数据（或子查询返回 NULL）
SELECT * FROM orders
WHERE user_id NOT IN (
  SELECT id FROM users WHERE city != '北京'
  -- 子查询返回 (1, 2, NULL, ...)
);
-- 结果：空！
-- 原因：任何值 NOT IN (..., NULL) → NULL（未知）→ WHERE 条件为 false → 没有行输出

NULL 传播规则：5 NOT IN (1, 2, NULL) → 5 <> 1 AND 5 <> 2 AND 5 <> NULL → TRUE AND TRUE AND NULL → NULL，条件不成立。

NOT EXISTS 不受 NULL 影响，是安全写法：

SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM users u
  WHERE u.id = o.user_id AND u.city != '北京'
);
-- 正确！EXISTS 只判断"是否有行"，不做值比较，NULL 不影响结果

结论：子查询可能含 NULL 时，用 NOT EXISTS 替代 NOT IN。

聚合函数

SELECT
  COUNT(*)                 AS total_rows,     -- 计所有行（包括 NULL）
  COUNT(field)             AS non_null_count, -- 计非 NULL 行
  COUNT(DISTINCT field)    AS distinct_count, -- 计去重值
  SUM(amount)              AS total,
  AVG(amount)              AS average,
  MAX(amount)              AS max_val,
  MIN(amount)              AS min_val,
  GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') AS names  -- 拼接字符串
FROM orders
GROUP BY category;

COUNT(*) 效率最优（不需要判断 NULL），详见性能排查笔记。

数学函数

-- 取整
SELECT FLOOR(1.9),   -- 1   向下取整（会员积分常用）
       CEIL(1.1),    -- 2   向上取整
       ROUND(1.55, 1), -- 1.6  四舍五入，保留1位小数
       ROUND(1.45, 1); -- 1.5

-- 其他
SELECT ABS(-5),       -- 5    绝对值
       MOD(10, 3),    -- 1    取余数（等同于 10 % 3）
       POWER(2, 10),  -- 1024  幂运算
       SQRT(16);      -- 4    平方根

积分场景：消费一元积一分，不满一元不积分 → 用 FLOOR(amount)。

字符串函数

-- 拼接
SELECT CONCAT('Hello', ', ', 'World');        -- Hello, World
SELECT CONCAT_WS('-', '2024', '01', '15');    -- 2024-01-15（用分隔符拼接）

-- 长度
SELECT LENGTH('hello'),   -- 5   字节长度（中文 UTF8MB4 每字3字节）
       CHAR_LENGTH('你好'); -- 2   字符个数（更常用）

-- 截取
SELECT LEFT('abcdef', 3),             -- abc
       RIGHT('abcdef', 3),            -- def
       SUBSTRING('abcdef', 2, 3),     -- bcd（从第2位取3个字符，位置从1开始）
       SUBSTR('abcdef', -3);          -- def（负数从末尾算）

-- 查找与替换
SELECT LOCATE('bc', 'abcdef'),        -- 2  查找子串位置，不存在返回0
       REPLACE('hello world', 'world', 'MySQL'); -- hello MySQL

-- 格式化
SELECT LPAD('5', 3, '0'),   -- 005  左填充
       RPAD('5', 3, '0'),   -- 500  右填充
       TRIM('  hello  '),   -- hello  去除首尾空格
       UPPER('hello'),      -- HELLO
       LOWER('HELLO');      -- hello

时间函数

-- 获取当前时间
SELECT NOW(),            -- 2024-01-15 14:30:00  当前日期时间
       CURDATE(),        -- 2024-01-15           当前日期
       CURTIME();        -- 14:30:00             当前时间

-- 提取组成部分
SELECT YEAR('2024-01-15'),        -- 2024
       MONTH('2024-01-15'),       -- 1
       DAY('2024-01-15'),         -- 15
       HOUR('14:30:00'),          -- 14
       MINUTE('14:30:00'),        -- 30
       DAYOFWEEK('2024-01-15'),   -- 2（1=周日，2=周一，...7=周六）
       WEEKDAY('2024-01-15');     -- 0（0=周一，...6=周日）

-- 格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'); -- 2024-01-15 14:30:00
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');       -- 2024年01月15日

-- 日期计算
SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY),      -- 2024-01-22
       DATE_SUB('2024-01-15', INTERVAL 1 MONTH),     -- 2023-12-15
       DATEDIFF('2024-01-15', '2024-01-01'),          -- 14  两日期相差天数
       TIMESTAMPDIFF(HOUR, '2024-01-15 08:00:00', '2024-01-15 14:30:00'); -- 6

索引失效警告：对时间字段使用函数（如 YEAR(created_at) = 2024）会导致索引失效。改写为范围查询：created_at >= '2024-01-01' AND created_at < '2025-01-01'。

条件判断函数

IF

-- IF(条件, 为真的值, 为假的值)
SELECT name,
       IF(score >= 60, '及格', '不及格') AS result
FROM student;

CASE WHEN（多分支）

-- 简单 CASE
SELECT name,
       CASE grade
           WHEN 'A' THEN '优秀'
           WHEN 'B' THEN '良好'
           WHEN 'C' THEN '及格'
           ELSE '不及格'
       END AS grade_desc
FROM student;

-- 搜索 CASE（更灵活）
SELECT name, score,
       CASE
           WHEN score >= 90 THEN '优秀'
           WHEN score >= 75 THEN '良好'
           WHEN score >= 60 THEN '及格'
           ELSE '不及格'
       END AS level
FROM student;

NULL 处理

-- IFNULL(字段, 默认值)：字段为 NULL 时返回默认值
SELECT IFNULL(address, '地址未填写') FROM user;

-- COALESCE(v1, v2, v3, ...)：返回第一个非 NULL 值（推荐，更通用）
SELECT COALESCE(nickname, username, '匿名用户') AS display_name FROM user;

-- NULLIF(v1, v2)：v1 = v2 时返回 NULL，否则返回 v1（用于避免除零错误）
SELECT sales / NULLIF(cost, 0) AS margin FROM product;

行转列与列转行

建表与初始化数据

-- 学生成绩表（行格式：每门课一行）
CREATE TABLE student_score (
  student VARCHAR(20),
  subject VARCHAR(20),
  score   INT
);

INSERT INTO student_score VALUES
('张三', '数学', 90), ('张三', '语文', 85), ('张三', '英语', 92),
('李四', '数学', 78), ('李四', '语文', 88), ('李四', '英语', 76),
('王五', '数学', 95), ('王五', '语文', 70), ('王五', '英语', 83);

行转列（Pivot）

把多行合并成一行的多列，常用 CASE WHEN + 聚合实现：

SELECT
  student,
  MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
  MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
  MAX(CASE WHEN subject = '英语' THEN score END) AS 英语
FROM student_score
GROUP BY student;

student	数学	语文	英语
张三	90	85	92
李四	78	88	76
王五	95	70	83

用 MAX 是为了把同一分组里 CASE WHEN 的多个 NULL 聚合成唯一的非 NULL 值；SUM 也可以，但要注意多行时会累加。

列转行（Unpivot）

把一行的多列展开成多行，用 UNION ALL 实现：

-- 宽表（列格式）
CREATE TABLE student_wide (
  student VARCHAR(20),
  math    INT,
  chinese INT,
  english INT
);

INSERT INTO student_wide VALUES
('张三', 90, 85, 92),
('李四', 78, 88, 76);

-- 转成行格式
SELECT student, '数学' AS subject, math    AS score FROM student_wide
UNION ALL
SELECT student, '语文' AS subject, chinese AS score FROM student_wide
UNION ALL
SELECT student, '英语' AS subject, english AS score FROM student_wide
ORDER BY student, subject;

student	subject	score
张三	数学	90
张三	英语	92
张三	语文	85
李四	数学	78
李四	英语	76
李四	语文	88

窗口函数（MySQL 8+）

核心概念

窗口函数 vs 聚合函数的区别：

聚合函数：分组后每组返回一行结果
窗口函数：每行都保留，同时在每行上附加窗口计算结果

-- 语法
函数名 OVER (
  [PARTITION BY 分组字段]    -- 按哪个字段分窗口（不加则整张表是一个窗口）
  [ORDER BY 排序字段]         -- 窗口内排序
  [ROWS/RANGE 范围子句]       -- 滑动窗口范围（累计/滑动平均等）
)

建表与初始化数据

CREATE TABLE order_detail (
  id        INT PRIMARY KEY AUTO_INCREMENT,
  user_id   INT            NOT NULL,
  product   VARCHAR(50)    NOT NULL,
  amount    DECIMAL(10,2)  NOT NULL,
  order_at  DATETIME       NOT NULL
);

INSERT INTO order_detail (user_id, product, amount, order_at) VALUES
(1, '手机',  3999.00, '2024-01-05 10:00:00'),
(1, '耳机',   299.00, '2024-01-10 14:00:00'),
(1, '充电器',  99.00, '2024-01-15 09:00:00'),
(2, '笔记本', 5999.00, '2024-01-03 16:00:00'),
(2, '鼠标',   199.00, '2024-01-08 11:00:00'),
(3, '键盘',   399.00, '2024-01-12 13:00:00'),
(3, '显示器', 2499.00, '2024-01-20 15:00:00'),
(3, '音箱',   599.00, '2024-01-22 10:30:00');

聚合窗口函数

-- 每行同时显示"该用户的消费总额"和"全部用户消费总额"（不聚合成一行）
SELECT
  user_id, product, amount,
  SUM(amount) OVER(PARTITION BY user_id)  AS user_total,
  ROUND(amount / SUM(amount) OVER(PARTITION BY user_id) * 100, 1) AS pct_of_user,
  SUM(amount) OVER()                      AS grand_total
FROM order_detail
ORDER BY user_id, amount DESC;

user_id	product	amount	user_total	pct_of_user	grand_total
1	手机	3999.00	4397.00	90.9	14093.00
1	耳机	299.00	4397.00	6.8	14093.00
1	充电器	99.00	4397.00	2.3	14093.00

排名函数

-- 每个用户的订单按金额排名
SELECT
  user_id, product, amount,
  RANK()       OVER(PARTITION BY user_id ORDER BY amount DESC) AS rnk,       -- 并列跳号（1,1,3）
  DENSE_RANK() OVER(PARTITION BY user_id ORDER BY amount DESC) AS dense_rnk, -- 并列不跳号（1,1,2）
  ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS row_num     -- 每行唯一（1,2,3）
FROM order_detail;

user_id	product	amount	rnk	dense_rnk	row_num
1	手机	3999.00	1	1	1
1	耳机	299.00	2	2	2
1	充电器	99.00	3	3	3
2	笔记本	5999.00	1	1	1

取每组 top-1（窗口排序后取一条）

取每个用户金额最大的那笔订单：

-- 方法：用 ROW_NUMBER 排名，再取 rn=1
SELECT user_id, product, amount, order_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rn
  FROM order_detail
) t
WHERE rn = 1;

取每个用户最早下单（最小 order_at）：

SELECT user_id, product, amount, order_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_at ASC) AS rn
  FROM order_detail
) t
WHERE rn = 1;

取每个用户随机一笔订单（面试常考）：

SELECT user_id, product, amount, order_at
FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY RAND()) AS rn
  FROM order_detail
) t
WHERE rn = 1;
-- 注意：RAND() 在窗口函数中每行独立生成随机数，实现随机抽取

RANK vs ROW_NUMBER 的区别： 取 top-1 时，若有并列最大值：

ROW_NUMBER → 只取一行（任意选一个）

RANK = 1 → 取所有并列第一的行

移动窗口（累计/滑动平均）

-- 按下单时间累计每个用户的消费额
SELECT
  user_id, product, amount, order_at,
  SUM(amount) OVER(
    PARTITION BY user_id
    ORDER BY order_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_amount
FROM order_detail;

-- 全局按日期滑动（若有 daily_report 表）：过去 7 天滑动平均
-- AVG(daily_sales) OVER(ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

user_id	product	amount	cumulative_amount
1	手机	3999.00	3999.00
1	耳机	299.00	4298.00
1	充电器	99.00	4397.00

公用表表达式 CTE（MySQL 8+）

语法

WITH cte_name AS (
  SELECT ...  -- CTE 查询
)
SELECT * FROM cte_name WHERE ...;

对比子查询

-- ❌ 传统：嵌套子查询，可读性差
SELECT *
FROM (
  SELECT city, SUM(sales) AS total FROM orders GROUP BY city
) city_sales
WHERE total > 10000;

-- ✅ CTE：语义清晰
WITH city_sales AS (
  SELECT city, SUM(sales) AS total FROM orders GROUP BY city
)
SELECT * FROM city_sales WHERE total > 10000;

多个 CTE（链式）

WITH
  paid_orders AS (
    SELECT * FROM orders WHERE status = 'paid'
  ),
  city_summary AS (
    SELECT city, SUM(amount) AS total
    FROM paid_orders
    GROUP BY city
  )
SELECT * FROM city_summary WHERE total > 100000;

递归 CTE（层级数据）

-- 组织架构树：找某员工的所有下属（递归）
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employee
  WHERE id = 1  -- 从 CEO 开始
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employee e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

EXPLAIN 执行计划分析

EXPLAIN SELECT * FROM orders WHERE user_id = 100;

关键字段解读

字段	含义	需关注的值
`type`	连接类型，性能从好到差	见下表
`key`	实际使用的索引	NULL = 全表扫描
`rows`	预计扫描行数	越小越好
`filtered`	过滤后剩余比例	越高越好
`Extra`	附加信息	见下表

`type` 性能排序

system > const > eq_ref > ref > range > index > ALL
   好                                          差

type	说明
`const`	主键或唯一索引等值查询，最多 1 行
`eq_ref`	join 时被驱动表用主键/唯一索引，每次 1 行
`ref`	非唯一索引等值查询
`range`	索引范围查询
`index`	全索引扫描（比 ALL 好一点，只扫索引树）
`ALL`	全表扫描，最差

`Extra` 常见值

Extra	含义	是否需要优化
`Using index`	覆盖索引，无需回表	✅ 最优
`Using where`	在 Server 层过滤	视情况
`Using index condition`	索引下推（ICP）	✅ 较好
`Using filesort`	需要额外排序	⚠️ 关注
`Using temporary`	使用临时表	⚠️ 关注
`Using join buffer`	BNL 算法，无索引 join	❌ 需优化

慢查询诊断步骤

-- 1. 开启慢查询日志（生产需谨慎）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 2. 用 EXPLAIN 分析具体语句
EXPLAIN SELECT ...;

-- 3. 根据 type 和 key 判断是否缺索引
-- 4. 根据 Extra 判断是否有 filesort / temporary
-- 5. 加索引后验证 type 是否改善
ALTER TABLE orders ADD INDEX idx_user_id(user_id);
ANALYZE TABLE orders;  -- 更新统计信息

参考资料

《MySQL 必知必会》— 第 07 讲：WHERE 与 HAVING 有什么不同？

《MySQL 必知必会》— 第 08 讲：聚合函数：怎么高效地进行分组统计？

《MySQL 必知必会》— 第 09 讲：时间函数：时间类数据，MySQL 是怎么处理的？

《MySQL 必知必会》— 第 10 讲：如何进行数学计算、字符串处理和条件判断？

《MySQL 必知必会》— 第 24 讲：查询有点慢，语句该如何写？

《MySQL 必知必会》— 特别放送（三）：MySQL 8 都有哪些新特征？

MySQL 8.0 - Window Functions

MySQL 8.0 - WITH (Common Table Expressions)

MySQL 系列 #12：MySQL SQL 函数与查询技巧

目录

WHERE vs HAVING

EXISTS vs IN

执行逻辑对比

性能选择

最重要的陷阱：NOT IN 遇到 NULL

聚合函数

数学函数

字符串函数

时间函数

条件判断函数

IF

CASE WHEN（多分支）

NULL 处理

行转列与列转行

建表与初始化数据

行转列（Pivot）

列转行（Unpivot）

窗口函数（MySQL 8+）

核心概念

建表与初始化数据

聚合窗口函数

排名函数

取每组 top-1（窗口排序后取一条）

移动窗口（累计/滑动平均）

公用表表达式 CTE（MySQL 8+）

语法

对比子查询

多个 CTE（链式）

递归 CTE（层级数据）

EXPLAIN 执行计划分析

关键字段解读

`type` 性能排序

`Extra` 常见值

慢查询诊断步骤

评论 (0)

发表评论

MySQL 系列 #12：MySQL SQL 函数与查询技巧

目录

WHERE vs HAVING

EXISTS vs IN

执行逻辑对比

性能选择

最重要的陷阱：NOT IN 遇到 NULL

聚合函数

数学函数

字符串函数

时间函数

条件判断函数

IF

CASE WHEN（多分支）

NULL 处理

行转列与列转行

建表与初始化数据

行转列（Pivot）

列转行（Unpivot）

窗口函数（MySQL 8+）

核心概念

建表与初始化数据

聚合窗口函数

排名函数

取每组 top-1（窗口排序后取一条）

移动窗口（累计/滑动平均）

公用表表达式 CTE（MySQL 8+）

语法

对比子查询

多个 CTE（链式）

递归 CTE（层级数据）

EXPLAIN 执行计划分析

关键字段解读

type 性能排序

Extra 常见值

慢查询诊断步骤

评论 (0)

发表评论

`type` 性能排序

`Extra` 常见值