先问你一个问题：写 SQL 的时候，有没有过这种念头——"要是这个日期能直接加七天就好了""要是能把两列拼成一个字段就好了""要是能按分数分个档就好了"？

答案其实无处不在。MySQL 早就给你准备好了一大堆函数，你唯一要做的，就是学会在正确的场合挑出正确的那一个。

先别急着担心函数背不过来。所谓函数，通俗地讲，就是"把数据塞进去，经过一段预定义的处理，再吐出一个结果"的黑盒子。SQL 里的函数和你在任何一门编程语言里见过的函数是同一个思路：有输入参数，有返回结果，只不过它是用 SQL 语法来书写的。MySQL 内置了海量的函数，从日期、字符串、数值，到聚合、加密、JSON、流程控制，一应俱全。这一篇我们就把它最常用、也最容易被坑到的一批，掰开揉碎地讲一遍。每讲完一类，我会留一两道思考题，并且当场给出带详解的答案——你不需要去别处找答案。

在开始之前，建议你的 MySQL 是 5.7 或 8.0 的版本。文中的例子大多能在两个版本上跑通；个别在 8.0 有变化的，我会专门标出来提醒你。

日期时间函数

日期是家里永远的那位"老大哥"——处处要用，但处处有坑。MySQL 里的日期时间函数，几乎都围绕着一个核心需求：拿到当下的时间、在时间上做加减、计算两个时间之间的差、以及从完整的时间里抽出某一部分。

拿到"现在"：三个 current 家族成员

MySQL 提供了三兄弟，分别回答三个不同的"现在"：

  • CURRENT_DATE()（可简写 CURDATE()）：只关心年月日；
  • CURRENT_TIME()（可简写 CURTIME()）：只关心时分秒；
  • CURRENT_TIMESTAMP()（也叫 NOW()）：年月日时分秒全都要。
-- current_date() 只返回日期 'YYYY-MM-DD'
SELECT CURRENT_DATE();
-- 结果（当天日期会随机器时间变化）：
-- +----------------+
-- | CURRENT_DATE() |
-- +----------------+
-- | 2026-08-23     |
-- +----------------+
 
-- current_time() 只返回时间 'HH:MM:SS'
SELECT CURRENT_TIME();
-- 结果：
-- +----------------+
-- | CURRENT_TIME() |
-- +----------------+
-- | 13:51:21       |
-- +----------------+
 
-- current_timestamp() 返回完整日期时间，等价 NOW()
SELECT CURRENT_TIMESTAMP();
-- 结果：
-- +---------------------+
-- | CURRENT_TIMESTAMP() |
-- +---------------------+
-- | 2026-08-23 13:51:48 |
-- +---------------------+

注意，这三个都是"返回函数，括号不能省"——哪怕不带参数也要写上 ()。这是 SQL 里容易踩的一个雷：在 MySQL 里写 CURRENT_DATE（不带括号）也能过，但容易造成人与代码的误解，规范写法是永远带上 ()。

关于 NOW() 和 SYSDATE()，还有一个很多老手都会记岔的区别。NOW() 返回的是这一条语句开始执行的那一刻的时间；而 SYSDATE() 返回的是它这句函数真正执行到的那一刻的时间。换句话说，NOW() 在同一条语句里无论出现多少次、被算多久，得到的结果都一模一样；SYSDATE() 则可能因为语句执行耗时，在语句里前后取值不同。理想情况下两者相等，但在一条跑得很慢、涉及大量数据的大查询里，SYSDATE() 就可能"漂移"。所以日常业务里优先用 NOW()，它行为更可预期、也方便和索引竞争（能走索引，性能更好）；SYSDATE() 属于"你知道有它、但别乱用"的选手。

在日期上做加减：DATE_ADD 与 DATE_SUB

假设你现在存了一个日期 '2026-08-23'，想算出"10 天之后是哪天""2 个月之前是哪天"，DATE_ADD 和 DATE_SUB 就是干这个的。它们的核心是INTERVAL 关键字 + 一个数字 + 一个单位，单位可以是 DAY、MONTH、YEAR、HOUR、MINUTE、SECOND、WEEK 等等。

-- 在 2017-10-28 的基础上加 10 天，结果 2017-11-07
SELECT DATE_ADD('2017-10-28', INTERVAL 10 DAY);
-- 结果：
-- +-----------------------------------------+
-- | DATE_ADD('2017-10-28', INTERVAL 10 DAY) |
-- +-----------------------------------------+
-- | 2017-11-07                               |
-- +-----------------------------------------+
 
-- 在 2017-10-1 的基础上减 2 天，结果 2017-09-29
SELECT DATE_SUB('2017-10-1', INTERVAL 2 DAY);
-- 结果：
-- +---------------------------------------+
-- | DATE_SUB('2017-10-1', INTERVAL 2 DAY) |
-- +---------------------------------------+
-- | 2017-09-29                             |
-- +---------------------------------------+
 
-- 加 1 小时 30 分钟（可以叠加写法，这里展示单元如何搭配）
SELECT DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);
-- 在"现在"上加了 1 小时后得到的完整时间

这里要重点讲一个 月末溢出的坑：如果加减后跨越了那个月不存在的日子，MySQL 不会报错，而是把这个日子裁剪回该月的最后一天。比如 '2028-01-31' 加 1 个月，2 月本没有 31 号，结果会被自动调整为 '2028-02-29'（2028 是闰年，2 月有 29 天）。你要是想当然以为"日期 + 1 个月 = 天数原封不动往前翻一个月"，就会在月底这几天踩坑。对业务来说这通常是符合直觉的（"1 月 31 号往后推一个月，就该是 2 月月底"），但你心里得有数，它跟"天数直接逐月 +30"完全是两回事。

计算两个日期相差几天：DATEDIFF

DATEDIFF(date1, date2) 返回的是 date1 减去 date2 得到的天数（date1 - date2，单位是"天"）。顺序千万要注意：是"第一个参数减第二个参数"，不是你读起来怎么顺口怎么来。

-- 计算 2017-10-10 和 2016-9-1 之间相差多少天
SELECT DATEDIFF('2017-10-10', '2016-09-01');
-- 结果：
-- +------------------------------------+
-- | DATEDIFF('2017-10-10', '2016-09-01') |
-- +------------------------------------+
-- |                                404 |
-- +------------------------------------+

翻过来写，DATEDIFF('2016-09-01', '2017-10-10') 就会得到 -404——负的天数，表示"前者在后者的前面"。所以判断"距今天数"时，请把"将来"放在第一个参数、把"过去"放在第二个参数，这样正数才表示"相隔多少天"。

从完整时间里抽出一部分：DATE()

很多时候你存的是一个 DATETIME（比如 2026-08-23 13:51:48），但用户只关心"这是哪天"。用 DATE(datetime) 就能把后面的时分秒剥掉，只留下日期部分。它和 NOW() 家族正好是逆向的关系：一个是"由完整到局部"，一个是"直接取当下"。

两个实战小案例：生日表与留言表

**案例一：一张记录生日的表，插入"今天"作为生日。**这里就顺理成章用到了 CURRENT_DATE()：

-- 创建一张表，id 自增作为主键，birthday 存日期
CREATE TABLE tmp (
  id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键，自增
  birthday DATE                         -- 生日，只存年月日
);
 
-- 插入当前日期作为生日
INSERT INTO tmp(birthday) VALUES (CURRENT_DATE());
 
-- 查看整张表
SELECT * FROM tmp;
-- 结果：
-- +----+------------+
-- | id | birthday   |
-- +----+------------+
-- |  1 | 2026-08-23 |
-- +----+------------+

**案例二：一张留言表。**存留言内容和发送时间，然后做两个查询需求：第一，显示所有留言，但发布日期只显示日期、不把时间连累出来；第二，找出"2 分钟内刚发布"的帖子。

-- 创建留言表
CREATE TABLE msg (
  id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键自增
  content VARCHAR(30) NOT NULL,        -- 留言内容，非空
  sendtime DATETIME                    -- 发送时间
);
 
-- 插入两条留言，时间用 now()（等价 current_timestamp）
INSERT INTO msg(content, sendtime) VALUES ('hello1', NOW());
INSERT INTO msg(content, sendtime) VALUES ('hello2', NOW());
 
-- 查看整张表
SELECT * FROM msg;
-- 结果（两条差不多同时插入，时间会非常接近）：
-- +----+---------+---------------------+
-- | id | content | sendtime            |
-- +----+---------+---------------------+
-- |  1 | hello1  | 2026-08-23 14:12:20 |
-- |  2 | hello2  | 2026-08-23 14:13:21 |
-- +----+---------+---------------------+
 
-- 需求一：只显示日期部分，不显示时间，用 DATE(sendtime)
SELECT content, DATE(sendtime) FROM msg;
-- 结果：
-- +---------+----------------+
-- | content | DATE(sendtime) |
-- +---------+----------------+
-- | hello1  | 2026-08-23     |
-- | hello2  | 2026-08-23     |
-- +---------+----------------+
 
-- 需求二：找出 2 分钟内刚发布的帖子
SELECT * FROM msg WHERE DATE_ADD(sendtime, INTERVAL 2 MINUTE) > NOW();
-- 结果：两条几乎同一时刻插入的留言通常都会被选出来

这最后一条条件 DATE_ADD(sendtime, INTERVAL 2 MINUTE) > NOW() 值得用一张时间轴想清楚：

----------------------|-----------|------------------------------------
                sendtime        NOW()          sendtime + 2 分钟

它在判断"发帖时间往后推 2 分钟，仍然晚于现在"——换句话说，从发送到现在还没超过 2 分钟。看起来绕，但它和另一种更直接的写法完全等价：

-- 等价写法：发送时间要晚于"现在往前推 2 分钟"
SELECT * FROM msg WHERE sendtime > DATE_SUB(NOW(), INTERVAL 2 MINUTE);
-- 结果：同上，选出 2 分钟内发布的帖子

两种写法都能选出"最近 2 分钟内发的帖子"，想清楚一条，另外一条自然就懂了。

思考题（日期）：NOW() 和 SYSDATE() 到底差在哪？

答：NOW() 返回的是当前这条语句开始执行的那一刻；SYSDATE() 返回的是它这句函数实际被评估执行的那一刻。在同一条语句里，NOW() 无论写几次、结果都一样；而 SYSDATE() 会因为语句真的花了时间，而在语句首尾取到不同的值。日常业务请优先 NOW()——它不仅行为可预期，还能更好地配合索引查询。

课后练习（附答案）：SELECT DATE_ADD('2028-01-31', INTERVAL 1 MONTH); 结果是多少？为什么？

答：结果是 2028-02-29。因为 2 月没有 31 号，MySQL 在加减日期遇到"目标月份里不存在那一天"时，会把天数裁剪回该月的最后一天（不报错）。2028 年是闰年，2 月的最后一天是 29 号。如果你的逻辑是"31 天逐月平移"，这里就会和你预想的不一致——这就是月底附近最容易踩的坑。

字符串函数

说完了日期，轮到字符串——这是 MySQL 函数里成员最多的家族之一，也是新手最容易在"到底按字节还是按字符算"上翻车的地方。我们先把最常用的几个过一遍。

查字符集：CHARSET()

同一个字符串，在不同字符集下占用的字节数完全不同。CHARSET(字符串) 能告诉你某个字符串（或字段值）当前用的是哪种字符集。

-- 查看 emp 表中 ename 列用的是哪个字符集
-- （需要你本地有 emp 这张表；没有的话可以查任意字符串）
SELECT CHARSET('中文');
-- 结果（默认 utf8mb4 下）：
-- +-----------------+
-- | CHARSET('中文') |
-- +-----------------+
-- | utf8mb4         |
-- +-----------------+

它主要是排查"我的中文为什么存出来是乱码 / 字节数不对"这类问题的侦查工具，本身不常用，但要知道有它。

字符串拼接：CONCAT 与 CONCAT_WS

你经常要把几段文本拼在一起，比如"张三的语文是90分，数学是95分"。这就要用 CONCAT 了。字符串拼接（concatenation）这个词，指的就是把两个或多个字符串按顺序首尾相接、合成一个更长的字符串。

-- 把竖线标记的几段内容拼起来
SELECT CONCAT('hello', ' ', 'world');
-- 结果：
-- +----------------------------+
-- | CONCAT('hello', ' ', 'world') |
-- +----------------------------+
-- | hello world                |
-- +----------------------------+

在实际场景里，它经常用于把一张表的多个字段拼成一句人话。比如有一张学生成绩表（student，字段有 name、chinese、math），想得到"XXX 的语文是 XX 分，数学是 XX 分"：

-- 把姓名和两门课成绩拼成一句完整描述
SELECT CONCAT(name, '的语文是', chinese, '分，数学是', math, '分') AS '分数'
FROM student;
-- 假设 student 里有张三/李四两行，结果大致如下：
-- +-------------------------------------------+
-- | 分数                                      |
-- +-------------------------------------------+
-- | 张三的语文是90分，数学是95分                |
-- | 李四的语文是88分，数学是92分                |
-- +-------------------------------------------+

这里立刻要引入一个贯穿整篇的最重要坑之一——NULL 传染。CONCAT 只要任意一个参数是 NULL，整个结果就是 NULL。比如上面如果张三的 math 是 NULL，那这条的 '分数' 就直接变成 NULL，而不是"某某的语文是90分，数学是NULL分"。解决办法有两个：一是用 IFNULL(math, 0) 把空值先垫底（IFNULL 后面会专门讲），二是用 CONCAT_WS（WS = With Separator，带分隔符拼接），它遇到 NULL 段会自动跳过而不是传染：

-- CONCAT_WS 第一个参数是分隔符，后面的参数中 NULL 会被自动跳过
SELECT CONCAT_WS('-', 'a', NULL, 'b', 'c');
-- 结果：
-- +----------------------------------+
-- | CONCAT_WS('-', 'a', NULL, 'b', 'c') |
-- +----------------------------------+
-- | a-b-c                            |
-- +----------------------------------+

看到没有，中间的 NULL 被悄悄跳过了，分隔符没有连排出现。这就是 CONCAT_WS 在拼接"可能含空值的字段"时好用的原因。

字符串长度：LENGTH 与 CHAR_LENGTH

这是整个字符串家族最容易让人懵的一对。记住一句口诀：LENGTH 算的是字节数，CHAR_LENGTH 算的是字符个数。

之所以要区分，是因为"一个字符占几个字节"取决于字符集。在 utf8/utf8mb4 字符集下，英文字母、数字、英文标点是一字节，而一个汉字占三字节。所以：

-- 全英文：length 和 char_length 结果一样，都是 5
SELECT LENGTH('hello'), CHAR_LENGTH('hello');
-- 结果：
-- +-----------------+----------------------+
-- | LENGTH('hello') | CHAR_LENGTH('hello') |
-- +-----------------+----------------------+
-- |               5 |                    5 |
-- +-----------------+----------------------+
 
-- 两个汉字：length 按字节算得 6，char_length 按字符算得 2
SELECT LENGTH('你好'), CHAR_LENGTH('你好');
-- 结果（utf8mb4 下）：
-- +-----------------+----------------------+
-- | LENGTH('你好')  | CHAR_LENGTH('你好')  |
-- +-----------------+----------------------+
-- |               6 |                    2 |
-- +-----------------+----------------------+

所以当你要回答"学生姓名占多少字节"时，用 LENGTH；当你要回答"姓名有几个字"时，用 CHAR_LENGTH。如果你存的中文，length 得到的数据大概率比你预期的"字数"要大——这在算 varchar 的容量、或者做页面字数统计时是隐藏的开销来源，也是网上"为什么我的字数是 2，算出来却是 6"的经典疑惑。

字符串替换：REPLACE

REPLACE(str, from_str, to_str) 做的事是：在 str 里把所有出现的 from_str 都替换成 to_str。注意是"所有出现"，不是只替换第一个。

-- 把 EMP 表中所有名字里出现的 'S' 替换成 '上海'（原值用 ename 列同时展示）
SELECT REPLACE(ename, 'S', '上海') AS 替换后, ename AS 原名 FROM EMP;
-- 假设 EMP 有 SMITH、KING 等行，结果大致：
-- +--------------+-------+
-- | 替换后       | 原名   |
-- +--------------+-------+
-- | 上海MITH     | SMITH  |
-- | KING         | KING   |
-- +--------------+-------+

（上面的 EMP 是你本地车位教材里的雇员表。如果没有，把 FROM EMP 换成 FROM student 或者干脆不接表，用 SELECT REPLACE('MYSQL', 'S', '上海'); 体会效果也一样。）

字符串截取：SUBSTRING

SUBSTRING(str, start, len) 从 str 里自 start 位置起截取 len 个字符。这里有个和几乎所有编程语言都不一样的关键点：MySQL 里的位置从 1 开始，不是 0。

-- 从 ename 的第 2 个字符开始，截 2 个字符
SELECT SUBSTRING(ename, 2, 2) AS 截取, ename AS 原名 FROM EMP;
-- 比如 SMITH、KING：
-- +--------+-------+
-- | 截取   | 原名   |
-- +--------+-------+
-- | MI     | SMITH  |
-- | IN     | KING   |
-- +--------+-------+
 
-- 只留前 3 个字符
SELECT SUBSTRING('ABCDEFGH', 1, 3);
-- 结果：
-- +--------------------------+
-- | SUBSTRING('ABCDEFGH',1,3) |
-- +--------------------------+
-- | ABC                      |
-- +--------------------------+

更"灵活"的是，SUBSTRING 里的 start 还可以是负数，表示从右边倒数第几个字符开始取。比如 SUBSTRING('ABCDEF', -2, 2) 从右数第 2 个字符（E）开始取 2 个，得到 'EF'。它是 substring 家族里相当实用的小技巧。

大小写转换：LCASE / UCASE

LCASE(str)（等于 LOWER）把字符串转成全小写；UCASE(str)（等于 UPPER）把字符串转成全大写。注意这两个都是"全量转换"，没有"只转首字母"这种现成函数——首字母小写这种需求往往要靠小写函数和截取函数组合出来。

-- 以首字母小写、其余不变的方式，重新显示所有员工姓名
-- 思路：取第1个字符转小写，再拼接上"从第2个字符起剩下的全部"
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS 首字母小写
FROM EMP;
-- 比如 SMITH -> sMITH，KING -> kING：
-- +----------------+
-- | 首字母小写     |
-- +----------------+
-- | sMITH          |
-- | kING           |
-- +----------------+

注意我这里 SUBSTRING(ename, 2) 只给了两个参数（省略了长度），它的含义就是"从第 2 个字符一直取到字符串末尾"。这是 substring 的常见简写用法，务必记牢——它省得你去数长度。

思考题（字符串）：length 和 char_length 的区别？

答：length 以字节为单位统计字符串长度；char_length（别名 character_length）以字符为单位。在 utf8mb4 字符集下，英文字母/数字/英文标点占 1 字节，一个汉字占 3 字节。所以 length('你好') 是 6，char_length('你好') 是 2。要数"字"用 char_length，要算"字节/占用空间"用 length。

**课后练习（附答案）：**如果 student.name 里存的是中文姓名，SELECT LENGTH(name), CHAR_LENGTH(name), name FROM student; 假设 name='张三'，三列分别是什么？如果换成同时有英文名 Tom，这两列又是什么？

**答：**对 '张三'：length 是 6（3 字节/字 × 2 字），char_length 是 2。对 'Tom'：length 是 3，char_length 也是 3（全单字节字符）。只要一笔数据里混着中文和英文，这两列就会呈现"凌乱"的差异，这正是它俩"一个算字节、一个算字符"的直观体现。

数学函数

日期和字符串都讲过了，接下来这几个负责"跟数字打交道"。它们大多名字直白，真正容易错的点全藏在"向上/向下取整的方向"和"保留小数的形式"里。

绝对值：ABS

ABS(x) 返回 x 的绝对值（去掉负号）：

SELECT ABS(-100.2);
-- 结果：
-- +-------------+
-- | ABS(-100.2) |
-- +-------------+
-- |       100.2 |
-- +-------------+

向上取整与向下取整：CEILING 与 FLOOR

CEILING(x)（可简写 CEIL）向上取整，取"不小于 x 的最小整数"——即朝着 正无穷方向 取；FLOOR(x) 向下取整，取"不大于 x 的最大整数"——即朝着 负无穷方向 取。

SELECT CEILING(23.04);   -- 不小于 23.04 的最小整数，结果 24
SELECT CEILING(-23.04);  -- 不小于 -23.04 的最小整数，结果 -23（向正无穷）
SELECT FLOOR(23.7);      -- 不大于 23.7 的最大整数，结果 23
SELECT FLOOR(-23.7);     -- 不大于 -23.7 的最大整数，结果 -24（向负无穷）

看到负数这里了吗？这就是最容易翻车的坑：很多人凭直觉以为 FLOOR(-23.7) 会在"绝对值方向"取整得到 -23，实际上它是向负无穷走，得到 -24；而 CEILING(-23.04) 是向正无穷走，得到 -23。一句话记住：CEILING 是"往大了（正方向）凑整数"，FLOOR 是"往小了（负方向）凑整数"，无论正负都往这个方向的一侧走。

保留小数和控制精度：FORMAT / ROUND / TRUNCATE

真正的"大魔王"在这里。要"保留 2 位小数"，MySQL 给了你至少三个长相类似的函数，但它们的脾气完全不同：

  • FORMAT(x, n)：把 x 四舍五入保留 n 位小数，返回的是字符串，而且超过 3 位会插入千分位逗号（比如 12345.68 → '12,345.68'）。它本质是"给展示/报表用的"。
  • ROUND(x, n)：把 x 四舍五入保留 n 位小数，返回的是数值，不带千分位逗号。
  • TRUNCATE(x, n)：把 x 直接截断到第 n 位，不四舍五入，后面的小数位直接砍掉。
-- format 返回字符串，且带千分位逗号
SELECT FORMAT(12345.678, 2);
-- 结果：
-- +----------------------+
-- | FORMAT(12345.678, 2) |
-- +----------------------+
-- | 12,345.68            |
-- +----------------------+
 
-- round 返回数值，四舍五入但不加千分位
SELECT ROUND(12345.678, 2);
-- 结果：
-- +---------------------+
-- | ROUND(12345.678, 2) |
-- +---------------------+
-- |            12345.68 |
-- +---------------------+
 
-- truncate 直接截断，不四舍五入，后面砍掉
SELECT TRUNCATE(12345.678, 2);
-- 结果：
-- +------------------------+
-- | TRUNCATE(12345.678, 2) |
-- +------------------------+
-- |               12345.67 |
-- +------------------------+

看清楚三者差异了吗？同一个 12345.678：

  • FORMAT(...,2) → '12,345.68'（字符串 + 逗号 + 四舍五入）
  • ROUND(...,2) → 12345.68（数值 + 不加逗号 + 四舍五入）
  • TRUNCATE(...,2) → 12345.67（数值 + 直接截断 + 不四舍五入）

如果你把 FORMAT 的结果 '12,345.68' 当成数值去继续参与计算（比如再乘个系数），很可能会因为那个千分位逗号而报错或行为异常——因为它已经是字符串了。算钱、算账这类要参与后续运算的，请用 ROUND；只是给人看的报表文本，才用 FORMAT。

产生随机数：RAND

RAND() 返回一个 [0, 1) 之间的小数（含 0，不含 1）。注意它不会返回负数，也不会等于 1。要得到整数范围，就得和 FLOOR 搭配来"封装"：

SELECT RAND();
-- 结果（每次都不一样）：
-- +---------------------+
-- | RAND()              |
-- +---------------------+
-- | 0.39428371644662091 |
-- +---------------------+
 
-- 生成 [1, 100] 之间的随机整数：rand()*100 得到 [0,100)，floor 取整到 [0,99]，再 +1
SELECT FLOOR(RAND() * 100) + 1;
-- 结果：1 到 100 之间的某个整数，比如 63

RAND(n) 传一个固定的种子参数时，会得到"可复现"的随机序列——同一批次调试需要稳定结果时很实用，但一般业务场景直接 RAND() 就行。

思考题（数学）：取整到底分几种，各有何不同？

**答：**常见取整有四种语义：

  1. CEILING/CEIL 向上取整：朝着正无穷方向，取不小于 x 的整数。注意 CEILING(-23.04) 是 -23。
  2. FLOOR 向下取整：朝着负无穷方向，取不大于 x 的整数。注意 FLOOR(-23.7) 是 -24。
  3. ROUND 四舍五入，保数值类型、可指定小数位。
  4. TRUNCATE 直接截断，绝不进位。

最关键的记忆点：CEILING 和 FLOOR 在负数上的方向和直觉相反（CEILING(-23.04)= -23，FLOOR(-23.7)= -24）；而 FORMAT 虽也是四舍五入保留小数，但返回字符串且带千分位逗号，别拿去参与数值运算。

课后练习（附答案）：SELECT FORMAT(1234567.891, 2), ROUND(1234567.891, 2), TRUNCATE(1234567.891, 2); 结果分别是什么？

答：

  • FORMAT(1234567.891, 2) → '1,234,567.89'（字符串，加了千分位，四舍五入到 89）
  • ROUND(1234567.891, 2) → 1234567.89（数值，无千分位）——注意第三位小数是 1，不进位，所以还是 .89
  • TRUNCATE(1234567.891, 2) → 1234567.89（数值，直接砍掉第三位以后，同样是 .89）

再用一个第三位小数 ≥5 的数（比如 1234567.895）试试三者的差别，会更直观：ROUND 会进到 .90，TRUNCATE 保持 .89，FORMAT 输出 '1,234,567.90'。

聚合函数

前面几节全是"一行算一行"的标量函数，你给它一条数据，它吐一条结果。但从这一节开始，我们要进入一个完全不同的家族——聚合函数。

先解释术语。聚合（aggregation），就是把"很多行"的数据汇总成"一个"结果。聚合函数接受的是一整个列（多行值），最后吐出的是一个单一的值。最常见的五个聚合函数是：COUNT（计数）、SUM（求和）、AVG（平均）、MAX（最大）、MIN（最小）。而"分组之后各自汇总"的 GROUP BY，则是让聚合函数真正大放异彩的舞台——这里先聚焦函数本身，GROUP BY 语法我们后续专题再展开。

为了讲得具体，我们先造一张小小的员工表存点数据：

-- 造一张演示用的员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键自增
  dept VARCHAR(10),                    -- 部门
  ename VARCHAR(20),                   -- 姓名
  salary DECIMAL(10,2)                 -- 薪资（允许为空）
);
 
-- 插入几行，故意让其中一个人的薪资为 NULL，用来说明聚合函数对空值的处理
INSERT INTO emp(dept, ename, salary) VALUES
  ('开发部', '张三', 12000.00),
  ('开发部', '李四', 15000.00),
  ('开发部', '王五', NULL),
  ('测试部', '赵六',  9000.00);

然后逐个看这几个聚合函数怎么用，以及它们面对 NULL 时的态度。

COUNT：计数，但有两种脾气

COUNT(*) 数的是"一共有多少行"，不管这些行的某个具体字段是不是空；COUNT(列名) 数的是"这一列里有多少个非 NULL 的值"。

-- 数总行数：4 行
SELECT COUNT(*) FROM emp;
-- 结果：
-- +----------+
-- | COUNT(*) |
-- +----------+
-- |        4 |
-- +----------+
 
-- 数 salary 列里非 NULL 的个数：王五的 salary 是 NULL，所以只有 3
SELECT COUNT(salary) FROM emp;
-- 结果：
-- +---------------+
-- | COUNT(salary) |
-- +---------------+
-- |             3 |
-- +---------------+

**这是聚合函数里第一个必须刻进脑子里的差别：COUNT(*) 不忽略整行；COUNT(具体列) 会忽略该列为 NULL 的那些行。**想知道"到底有几条数据"，用 COUNT(*)；想知道"有几个提供了薪资"，用 COUNT(salary)。

SUM、AVG、MAX、MIN：都忽略 NULL

其余四个聚合函数有一个共同原则：计算时自动忽略 NULL 的行（就像那行压根不存在）。

-- 求和：NULL 被忽略，只加 12000 + 15000 + 9000 = 36000
SELECT SUM(salary) AS 工资总和 FROM emp;
-- 结果：
-- +--------------+
-- | 工资总和     |
-- +--------------+
-- |        36000 |
-- +--------------+
 
-- 平均值：也是忽略 NULL，除以的是 3 而不是 4
SELECT AVG(salary) AS 平均工资 FROM emp;
-- 结果：
-- +--------------+
-- | 平均工资     |
-- +--------------+
-- |  12000.0000  |
-- +--------------+
 
-- 最大、最小：忽略 NULL 参与比较
SELECT MAX(salary) AS 最高工资, MIN(salary) AS 最低工资 FROM emp;
-- 结果：
-- +-------------+-------------+
-- | 最高工资    | 最低工资    |
-- +-------------+-------------+
-- |    15000.00 |     9000.00 |
-- +-------------+-------------+

注意 AVG 结果的位数——在 DECIMAL 列上算平均，MySQL 会返回小数位数较多的精确值（这里是 12000.0000），需要几位小数就去 ROUND(AVG(salary), 2) 精修一下，这是工程里最常见的搭配之一。

**这里又回到那一句"NULL 传染"的反面：**之前我们说算术运算里 NULL 会"传染"成整条 NULL；而聚合函数恰好相反，它是"吞掉 NULL"——除了 COUNT(*)。这两个方向一定要分清，否则你会对"为什么平均工资少了个人""为什么 sum 看起来不对"百思不得其解。

结合前面学的，再看一个"既要多行汇总多个字段、又要分档显示"的复合例子：

-- 统计工资总和，并把它用 format 美化成人看的报表文本
SELECT FORMAT(SUM(salary), 2) AS 工资总计 FROM emp;
-- 结果：
-- +--------------+
-- | 工资总计     |
-- +--------------+
-- | 36,000.00    |
-- +--------------+

思考题（聚合）：count(*) 和 count(某列) 的区别？

答：COUNT(*) 统计行数，一行都不落（即使这行里很多字段是 NULL）；COUNT(某列) 只统计该列非 NULL 的取值个数。对应到上面的 emp 表，COUNT(*) 是 4，而 COUNT(salary) 是 3（因为王五的 salary 是 NULL）。需要"行数"就用 COUNT(*)，需要"某个字段到底填了几个值"就用 COUNT(列名)。

**课后练习（附答案）：**基于上面的 emp 表，SELECT AVG(salary) FROM emp; 和王五的 NULL 有什么关系？如果想要"所有人（含王五）都按 0 参与平均"，该怎么写？

答：AVG(salary) 会忽略 salary 为 NULL 的王五，于是分母是 3（只对张三/李四/赵六平均），所以结果是 (12000+15000+9000)/3 = 12000。如果你希望王五以 0 参与平均、即分子加上 0、分母变成 4，可以先把 NULL 用 0 兜底再求和、再除以总人数，比如 SUM(IFNULL(salary,0)) / COUNT(*)——IFNULL 把王五的 NULL 替换成 0，COUNT(*) 保证分母是全部 4 行。结果就会是 (12000+15000+9000+0)/4 = 9000。这里就顺带预告了下面要讲的 IFNULL。

加密函数与 JSON 函数

数字、字符串、聚合都过完了，这一节聊两个"进阶但实用"的家族：加密和 JSON。它们看起来风马牛不相及，但都常见于真实业务。

加密函数：MD5、SHA1/SHA2，以及那个被移除了的 PASSWORD

MD5 是一种摘要算法：输入任意长度的字符串，输出一个固定 32 位的十六进制字符串。它被用来给数据（尤其是密码）做"指纹"。要注意：MD5 是单向、不可逆的哈希，不是可逆加密——你不能从那 32 位反推出原文。

-- 对 'admin' 做 md5 摘要，得到固定 32 位字符串
SELECT MD5('admin');
-- 结果：
-- +----------------------------------+
-- | MD5('admin')                     |
-- +----------------------------------+
-- | 21232f297a57a5a743894a0e4a801fc3 |
-- +----------------------------------+

同一个明文，MD5 结果永远一样，这就是它"指纹"的含义——但也正因如此，纯 MD5 存密码是不安全的（容易被彩虹表反查），现代做法往往要加盐（salt）或改用更强的摘要。

**这里必须给你提个醒，涉及一个被历史淘汰的函数 PASSWORD()。**在早期的 MySQL（5.6 及更早）里，常拿 PASSWORD('root') 这类函数去给用户密码做内部加密，返回一个以 * 开头、41 位左右的 SHA1 哈希串：

-- 早期 MySQL 版本：password() 对字符串加密，返回 41 位的 SHA1 风格哈希
SELECT PASSWORD('root');
-- 老版本 5.6 之类的结果大致：
-- +-------------------------------------------+
-- | PASSWORD('root')                          |
-- +-------------------------------------------+
-- | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
-- +-------------------------------------------+

**但请注意：PASSWORD() 在 MySQL 5.7.6 被标记为废弃（deprecated），在 MySQL 8.0 里已被彻底移除。**你如果在 8.0 里执行上面这条，会直接报 ERROR 1305 (42000): FUNCTION ... PASSWORD does not exist。这是版本演进带来的大坑——网上大量老教程还在一本正经地教 password()，你在 8.0 上照抄必挂。8.0 的正确姿势是用 CREATE USER ... IDENTIFIED BY 或 ALTER USER ... IDENTIFIED BY 语句来管理用户密码，任何一种靠手写 password() 往 mysql.user 表里灌哈希的做法都已过时。作为应用开发者，你更该用 sha2 这类现代、仍在维护的哈希函数来做摘要：

-- 现代替代：SHA2(str, 位数)，位数取 224/256/384/512，返回对应长度的十六进制摘要
SELECT SHA2('admin', 256);
-- 结果（256 位 → 64 个十六进制字符）：
-- +------------------------------------------------------------------+
-- | SHA2('admin', 256)                                               |
-- +------------------------------------------------------------------+
-- | 8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918 |
-- +------------------------------------------------------------------+

小结一句：摘要算法（不信我就记一句"明文进、固定长度指纹出"）还在用的有 MD5、SHA1、SHA2；PASSWORD() 只在老版本存在，8.0 已废除，别去踩雷。

JSON 函数：让你在数据库里直接刨 JSON

MySQL 5.7 起有了原生的 JSON 类型，它把一段 JSON 以经过验证和优化的二进制格式存下来；到 8.0 更是被大规模补齐、支持多值索引等特性。配合它的是一整套 JSON 函数，最常用的有这几个：

  • JSON_EXTRACT(json, path)：按 JSON 路径把里面的值取出来（等价于 -> 运算符，结果带引号）；
  • -> 与 ->> 运算符：-> 取出的仍是带引号的 JSON 值，->> 取出的是去掉了引号的纯文本（等价于 JSON_UNQUOTE(JSON_EXTRACT(...))）；
  • JSON_TYPE(json)：告诉你这段 JSON 的类型（OBJECT / ARRAY / INTEGER / STRING 等）；
  • JSON_VALID(str)：判断一段文本是不是合法 JSON，返回 1（合法）/ 0（不合法）；
  • JSON_ARRAYAGG / JSON_OBJECTAGG：配合 GROUP BY，把多行的值聚合成一个 JSON 数组 / JSON 对象。

JSON 路径（path）统一以 $ 代表文档根：$.name 取顶层键 name；$.a[0] 取顶层键 a 的数组第 0 个元素；$..x 做递归查找。直接看例子最清楚：

-- 用 JSON_OBJECT 造一段 JSON 文档（{"name":"Tom","age":18}）
SET @j = JSON_OBJECT('name', 'Tom', 'age', 18);
 
-- JSON_EXTRACT 取出 name，注意结果仍是带引号的 JSON 字符串
SELECT JSON_EXTRACT(@j, '$.name');
-- 结果：
-- +----------------------------+
-- | JSON_EXTRACT(@j, '$.name') |
-- +----------------------------+
-- | "Tom"                      |
-- +----------------------------+
 
-- ->> 运算符取出 name，得到去引号后的纯文本
SELECT @j ->> '$.name';
-- 结果：
-- +-----------------+
-- | @j ->> '$.name' |
-- +-----------------+
-- | Tom             |
-- +-----------------+
 
-- 判断类型：这段 JSON 是 OBJECT
SELECT JSON_TYPE(@j);
-- 结果：
-- +---------------+
-- | JSON_TYPE(@j) |
-- +---------------+ 
-- | OBJECT        |
-- +---------------+

用 JSON 时最容易栽的坑，是 -> 和 ->> 的区别。当你拿 JSON 里的字符串去跟普通 SQL 字符串做 WHERE 比较时，如果用 ->，得到的是 'Tom'（带引号），拿它等于 'Tom'（不带引号）会匹配不上——这就是很多人"明明存了 Tom 却查不到"的原因。要跟文本比较，请一律用 ->>（去引号）。第 5 章实战里我还会再演示一次它的用法。

思考题（加密/JSON）：password() 还能用吗？JSON 比较为什么查不到？

答：PASSWORD() 在 MySQL 8.0 已被移除，调用会报 ERROR 1305 FUNCTION ... does not exist，不能再用；用户密码管理请用 CREATE USER / ALTER USER ... IDENTIFIED BY。而 JSON 查不到，多是因为用 -> 取出的值是带引号的 JSON 字符串（如 'Tom'），拿它跟无引号的普通字符串 'Tom' 比较当然不相等——改用 ->> 取出纯文本后再比较即可。

课后练习（附答案）：SELECT JSON_EXTRACT('{"a":[1,2,3]}', '$.a[1]'); 返回什么？

**答：**返回 JSON 值 2。路径 $.a[1] 表示取顶层键 a 这个数组的第 1 个元素，数组下标从 0 起，所以第 1 个元素是 2（对应 [1,2,3] 里下标 1 的位置）。如果要当普通数字用、进一步参与数值运算，可加 CAST(... AS SIGNED) 或直接用 ->> 转成无引号形式。

流程控制函数

终于到了压轴戏：让 SQL 学会"判断"。MySQL 给了我们几个做条件分支的利器——IF、IFNULL、以及万能的 CASE ... WHEN。它们共同的目标是："根据某个条件的真假，返回不同的值。"

二选一：IF 函数

IF(条件, 值1, 值2) 是个标准的三元判断：条件为真（非 0），返回 值1；否则返回 值2。它和大多数语言里的 三元运算符 一个气质。

-- 条件 1>0 为真，返回 '对'
SELECT IF(1 > 0, '对', '错');
-- 结果：
-- +----------------------+
-- | IF(1 > 0, '对', '错') |
-- +----------------------+
-- | 对                   |
-- +----------------------+
 
-- 条件 1>2 为假，返回 '错'
SELECT IF(1 > 2, '对', '错');
-- 结果：
-- +----------------------+
-- | IF(1 > 2, '对', '错') |
-- +----------------------+
-- | 错                   |
-- +----------------------+

兜底空值：IFNULL

IFNULL(v1, v2) 是一个更专精的判断：**如果 v1 是 NULL，返回 v2；否则返回 v1 本身。**它就是专门为"处理空值、给个兜底"而生的。

-- v1 不是 NULL，原样返回 v1
SELECT IFNULL('abc', '123');
-- 结果：
-- +----------------------+
-- | IFNULL('abc', '123') |
-- +----------------------+
-- | abc                  |
-- +----------------------+
 
-- v1 是 NULL，返回兜底值 '123'
SELECT IFNULL(NULL, '123');
-- 结果：
-- +---------------------+
-- | IFNULL(NULL, '123') |
-- +---------------------+
-- | 123                 |
-- +---------------------+

这里埋着一个高频误解要专门拆掉：IFNULL(0, 'x') 返回什么？很多人看见数字 0 就本能地当成"空/假"，以为会返回 'x'。错了！IFNULL 判断的标准只有一条：第一个参数是不是 NULL。0 不是 NULL，所以返回的就是 0。只要记住"IFNULL 只跟 NULL 杠，不跟 0 或空串杠"，你就不会在这上面翻车。

IFNULL 最常见的用法，就是把"可能为 NULL 的字段/表达式"垫上一个默认值，避免它们一路传染成整条结果的 NULL（还记得第 2 章 CONCAT 的 NULL 传染吗？）。比如：

-- 工资可能为 NULL，用 IFNULL 兜底成 0 再求总和
SELECT SUM(IFNULL(salary, 0)) AS 含空工资总和 FROM emp;
-- 结果：把王五的 NULL 当作 0 参与求和
-- +------------------+
-- | 含空工资总和     |
-- +------------------+
-- |           36000  |
-- +------------------+

多分支之王：CASE ... WHEN

IF 只够处理一个条件的二选一；一但你要根据多种情况走多条路，就得请出 CASE ... WHEN。它是整个流程控制里最通用、也最重要的一员。

CASE 有两种形态，功能一样，写法不同：

形态一：简单 CASE。后面跟一个表达式，然后逐条比较它等于哪个值：

-- 简单 CASE：用一个值去逐条比对
SELECT
  CASE dept
    WHEN '开发部' THEN '这是开发部'
    WHEN '测试部' THEN '这是测试部'
    ELSE '未知部门'
  END AS 部门说明
FROM emp;
-- 结果（emp 表里只有开发部/测试部）：
-- +----------------+
-- | 部门说明       |
-- +----------------+
-- | 这是开发部     |
-- | 这是开发部     |
-- | 这是开发部     |
-- | 这是测试部     |
-- +----------------+

形态二：搜索 CASE。WHEN 后面直接跟一个条件表达式（可以是 > < = 等各种比较），从上往下逐条判断，命中第一条就把对应的 THEN 值返回。这是最灵活的写法，适合"成绩分档""价格区间"这类判断：

-- 搜索 CASE：把学生成绩按档次分类
SELECT
  name,
  CASE
    WHEN chinese >= 90 THEN '优秀'
    WHEN chinese >= 75 THEN '良好'
    WHEN chinese >= 60 THEN '及格'
    ELSE '不及格'
  END AS 语文档次
FROM student;
-- 假设张三语文 90、李四语文 88：
-- +------+--------------+
-- | name | 语文档次      |
-- +------+--------------+
-- | 张三 | 优秀         |
-- | 李四 | 良好         |
-- +------+--------------+

注意搜索 CASE 的判断是从上到下、命中即停的。所以上面的顺序很讲究：必须先写 >=90 再写 >=75，否则写成反过来，所有 90 分以上的人都会先撞上 >=75 而被误判成"良好"。区间型判断务必从小到大（或从大到小）排好顺序，这是写搜索 CASE 的黄金法则。

另外别忘了：CASE 结束要写 END；ELSE 是兜底分支，当所有 WHEN 都不命中时返回它。如果既没有命中的 WHEN、又没写 ELSE，CASE 整体返回 NULL——别漏了这个可能性。

CASE 与 IF 的区别（重点）

很多人会问：有 IF 就够了，为什么还造 CASE？这两者的区别值得专门划重点：

  1. 分支数量。 IF 是单条件二分支（要么 A、要么 B）；CASE ... WHEN 天生支持多分支（一个条件、两分支、三分支……想写几个写几个）。要判断两档以上，CASE 就是天然的正解。
  2. 可读性。 三路以上嵌套 IF(..., IF(..., IF(...,...))) 会层层套娃、难以阅读；换成 CASE ... WHEN 拍平，一目了然。
  3. 适用位置。 二者都能出现在 SELECT 的结果列里；但 CASE 作为"表达式"还能用在 WHERE、ORDER BY、GROUP BY 等更多需要"按结果排序/过滤/分组"的地方，地位比 IF 更"通用"。
  4. IF 还有多重身份。 注意别混淆：IF 既是一个函数（IF(条件, a, b)，本节我们讲的），又是一个语句（存储过程里的 IF ... THEN ... ELSE ... END IF，用于流程控制，这里不展开）。函数版的 IF 是表达式，直接在一个 SQL 里出结果。

一句话总结心里的模型："两档用 IF，多档/区间用 CASE；IF 管快，CASE 管全。"

思考题（流程控制）：IF 和 CASE 什么时候用谁？

答：IF(条件, A, B) 适合"一个条件、二选一"的最简场景；一旦判断存在两个以上分支，或者涉及区间、范围（比如 0–59 不及格 / 60–89 良好 / 90–100 优秀），就该用 CASE ... WHEN，可读性和扩展性都更好，而且 CASE 还能出现在 ORDER BY、GROUP BY 等更多位置。另外记牢：IFNULL(0, 'x') 返回 0——IFNULL 只看第一个参数是不是 NULL，跟"0/空串/假"毫无关系。

**课后练习（附答案）：**用 CASE ... WHEN，给 student 的 math 成绩分档：90 及以上为 A，80–89 为 B，60–79 为 C，60 以下为 D。写出完整 SQL 并说明写的顺序为什么这样。

答：

-- 顺序从高到底写，保证"命中即停"不会误判
SELECT
  name,
  CASE
    WHEN math >= 90 THEN 'A'     -- 先拦最高的档
    WHEN math >= 80 THEN 'B'     -- 再拦次高档
    WHEN math >= 60 THEN 'C'     -- 60-79
    ELSE 'D'                     -- 剩下全是 <60
  END AS 数学等级
FROM student;

因为搜索 CASE 是自上而下、命中即停，所以必须从高档写到低档。若先写 WHEN math >= 60 THEN 'C'，所有 80、90 分的人会先在 >=60 处被拦下，直接被误判为 C。区间判断时"高档在前"是铁律。（这里 90 分落在 A 还是 B，取决于你设定的开闭边界；实现档次重叠时，顺序比你想象的重要。）

实战：用函数解决一道数据库题

函数学到手，不实战不说服力。这里我们用一道经典的牛客网题目来收个尾、把所有章节串起来。

**题目：**查询字符串 '10,A,B' 中逗号 ',' 出现的次数 cnt。

思路拆解："统计某个字符在一段字符串里出现了几次"，SQL 里最优雅、也是被我反复推荐的套路是：用"原字符串的长度"减去"去掉该字符后字符串的长度"。

  • 原字符串 '10,A,B' 有 6 个字符：'1' '0' ',' 'A' ',' 'B'，其中逗号有 2 个。
  • 如果用 REPLACE 把逗号全删掉，得到 '10AB'，长度变成 4。
  • 两者相减：6 - 4 = 2，正好就是逗号的个数。
-- 用"原长度 - 去掉逗号后的长度"得到逗号出现的次数
SELECT
  LENGTH('10,A,B')                            -- 原字符串长度 6
  - LENGTH(REPLACE('10,A,B', ',', ''))        -- 去掉逗号后长度 4，二者相减得 2
  AS cnt;                                     -- 最终别名 cnt = 2
-- 结果：
-- +------+
-- | cnt  |
-- +------+
-- |    2 |
-- +------+

为什么这里用 LENGTH（字节）而不是 CHAR_LENGTH（字符）？因为逗号是单字节的 ASCII 字符，LENGTH 减去 REPLACE 后长度得到的差值，正好等于逗号个数，两者在这个例子里结果一致。但如果要把统计目标换成多字节字符（比如中文字符），就必须换成 CHAR_LENGTH 相减才准确——因为你"想去掉、去统计"的是字符个数，不是字节个数。养成"统计字符次数用 CHAR_LENGTH、统计单字节字符次数两者皆可、但统计字节用 LENGTH"的区分意识，是这道题真正的考点。

到这里，我们把 MySQL 内置函数里最核心、也最容易踩坑的一批过了一遍：日期时间函数解决了"现在/加减/相差/抽刀"四大问，还附赠了 now() 与 sysdate()、月末溢出的暗雷；字符串函数聊透了拼接、字节与字符的差异、替换、截取和大小写，重点标记了 CONCAT 的 NULL 传染；数学函数讲清了向上/向下取整的方向和 FORMAT/ROUND/TRUNCATE 三种精度的天壤之别；聚合函数点破了 count(*) 与 count(列) 的空值态度，以及 sum/avg/max/min 一律自动忽略 NULL 的脾气；加密与 JSON 提醒你 PASSWORD() 已随 MySQL 8.0 退役，并演示了用 ->> 提取 JSON 值的正确姿势；最后 IF / IFNULL / CASE ... WHEN 构成了 SQL 里的判断中枢，而 IF 与 CASE 的选择、搜索 CASE 的命中即停顺序，则是这里最能体现功力的细节。

函数看着多，但别忘了我们在开头建立的心智：它就是一个"数据进，结果出"的黑盒子。学函数最忌讳死背，最好的办法永远是在终端里亲手多敲几条、故意把参数写错几回——NULL 传染、月末裁剪、千分位逗号、-> 带引号，这些坑只有亲手踩过一遍，才能在面试和项目里一眼识破。这堂"函数课"讲完了，下一堂，我们该去认识一下那个让函数真正发挥威力、也最考验内功的语法——分组与 GROUP BY。到时候你回头看这篇里的聚合函数，会像看到老朋友那样亲切。