内置函数

这一课你将能做到

1. 用 CONCAT_WS、SUBSTRING_INDEX、UPPER 等字符串函数在 SQL 层完成文本拼装与提取
2. 用 CASE WHEN 表达式实现业务分支分类，用 COALESCE/NULLIF 正确兜底空值
3. 用 DATE_FORMAT、DATEDIFF、TIMESTAMPDIFF、DATE_ADD 完成日报、月报与时间间隔计算

为什么要学

数据在入库时是「原料」（原始状态码、UTC 时间、零散文本），出报表时要的是「成品」（中文标签、按月分组、可读的时长）。如果这些变换都拉回应用层做，一条 SQL 能搞定的事会被拆成几百次循环。函数是 SQL 表达能力的核心，也是面试笔试题的常客：「写 SQL 把状态翻译成中文」「统计每个月的提交量」全是函数应用。

人话：函数就是 SQL 的「变形金刚配件」——同一个表，配上不同函数，就能变出标签、日期、百分比各种形态的报表。

先建立图像

本课函数地图（按用途分五类，够用 90% 的场景）：

类别	高频函数	一句话用途
字符串	CONCAT_WS、SUBSTRING_INDEX、UPPER/LOWER、TRIM	拼接、截取、大小写、去空格
条件分支	CASE WHEN、IF、IFNULL、COALESCE、NULLIF	分类打标、空值兜底
日期时间	DATE_FORMAT、DATE_ADD/SUB、DATEDIFF、TIMESTAMPDIFF	格式化、推算、间隔
数值	ROUND、FLOOR/CEIL、MOD、LEAST/GREATEST	取整、取余、极值
聚合	SUM、AVG、COUNT、GROUP_CONCAT	多行折叠成一行

原料（表里的行）                    成品（查询结果）
status = 'accepted'   ──CASE──►   判题结果 = 通过
submitted_at          ──DATE_FORMAT──►  2025-06
runtime_ms            ──ROUND(AVG())──► 平均耗时 16.7ms

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据（6 用户、6 题、12 提交）

步骤 1：字符串函数——用户名片视图

SELECT
  username,
  CONCAT_WS(' / ', username, email)                       AS name_card,
  UPPER(username)                                         AS uname_upper,
  SUBSTRING_INDEX(email, '@', 1)                          AS email_local,
  SUBSTRING_INDEX(email, '@', -1)                         AS email_domain,
  CHAR_LENGTH(username)                                   AS name_chars,
  LENGTH(username)                                        AS name_bytes
FROM cf_user;

预期要点：SUBSTRING_INDEX(email,'@',1) 取 @ 前段、-1 取后段；CHAR_LENGTH('码')=1 而 LENGTH('码')=3（utf8mb4 一个汉字 3 字节）；CONCAT_WS 用分隔符拼接且自动跳过 NULL（普通 CONCAT 遇 NULL 整体变 NULL）。

步骤 2：CASE WHEN——状态翻译与分类统计

SELECT
  s.status,
  CASE s.status
    WHEN 'accepted'      THEN '通过'
    WHEN 'wrong_answer'  THEN '答案错误'
    WHEN 'runtime_error' THEN '运行时错误'
    WHEN 'time_limit'    THEN '超时'
  END AS status_cn,
  COUNT(*) AS cnt
FROM cf_submission s
GROUP BY s.status, status_cn
ORDER BY cnt DESC;

预期：答案错误 3、通过 6、超时 1、运行时错误 1、空值（runtime_error 组的中文）1——注意最后一个组：CASE 没写 ELSE 时未命中返回 NULL。加上 ELSE '其他' 才是完整写法。搜索型 CASE（不带初始表达式）还能做区间分类：

SELECT
  CASE
    WHEN runtime_ms >= 1000 THEN '超慢'
    WHEN runtime_ms >= 50   THEN '偏慢'
    ELSE '正常'
  END AS speed_tier,
  COUNT(*) AS cnt
FROM cf_submission
GROUP BY speed_tier;
-- 预期：超慢 1（2000ms）、正常 11

步骤 3：空值三件套

-- 空集聚合返回 NULL，用 COALESCE 兜底
SELECT COALESCE(SUM(runtime_ms), 0) AS total_runtime
FROM cf_submission WHERE user_id = 6;   -- qian 无提交，结果 0 而不是 NULL

-- IFNULL 是两参数版 COALESCE 的 MySQL 简写
SELECT IFNULL(NULL, '默认值') AS a, COALESCE(NULL, NULL, '兜底') AS b;

-- NULLIF(a, b)：相等则返回 NULL，常用于防除零
SELECT 100 / NULLIF(0, 0) AS safe_div;  -- NULL 而不是报错

步骤 4：日期函数——日报与月报

-- 按月统计
SELECT DATE_FORMAT(submitted_at, '%Y-%m') AS ym, COUNT(*) AS cnt
FROM cf_submission
GROUP BY DATE_FORMAT(submitted_at, '%Y-%m');
-- 预期：2025-06 → 12

-- 按天统计 + 两个间隔函数
SELECT
  DATE(submitted_at)                    AS d,
  COUNT(*)                              AS cnt,
  DATEDIFF('2025-06-08', DATE(submitted_at))      AS days_before_0608,
  TIMESTAMPDIFF(HOUR, submitted_at, '2025-06-08 00:00:00') AS hours_before
FROM cf_submission
GROUP BY DATE(submitted_at)
ORDER BY d;

预期每日 cnt 依次为 2、1、2、1、2、2、2。DATEDIFF 只算到「天」（后者减前者），TIMESTAMPDIFF 支持年月日时分秒且带符号，跨月统计用它。

步骤 5：综合日报——函数组合拳

SELECT
  DATE_FORMAT(submitted_at, '%Y-%m-%d')                    AS d,
  COUNT(*)                                                 AS total_sub,
  SUM(status = 'accepted')                                 AS ac_cnt,
  ROUND(SUM(status = 'accepted') / COUNT(*) * 100, 1)      AS ac_rate,
  ROUND(AVG(runtime_ms), 1)                                AS avg_ms
FROM cf_submission
GROUP BY DATE_FORMAT(submitted_at, '%Y-%m-%d')
ORDER BY d;

预期第一行：2025-06-01、2、1、50.0、21.0（12 与 30 的均值）。这一条 SQL 就是一张运营日报——所有变换在数据库一次完成。

讲清楚要点

1. CASE 是表达式不是语句：它可以出现在 SELECT、WHERE、ORDER BY、GROUP BY 任何需要值的地方。写 CASE 有两条铁律：分支从上到下短路匹配，区间条件要从大到小（或从小到大）排好序；最后一定写 ELSE，否则未命中返回 NULL。
2. NULL 的传染性：CONCAT('a', NULL) 是 NULL、NULL + 1 是 NULL、WHERE col != 'x' 会漏掉 NULL 行（NULL 参与比较结果不是 TRUE）。所以输出层用 COALESCE 兜底、过滤层想清楚 NULL 语义，是两个必须养成的习惯。
3. 对索引列套函数 = 索引失效：WHERE DATE(created_at) = '2025-06-01' 会让索引废掉，必须改写成范围条件 created_at >= '2025-06-01' AND created_at < '2025-06-02'。函数管输出、条件用范围，是性能上的分工原则（第 11 课展开）。
4. DATE_FORMAT 的格式符要背常用的：%Y 四位年、%m 两位月、%d 日、%H:%i:%s 时分秒。它只用于展示输出；分组用它可以，但大表上配合不了索引，预聚合报表建议落表或用生成列。
5. DATEDIFF vs TIMESTAMPDIFF：前者参数顺序是「终点, 起点」且只算整天；后者是「单位, 起点, 终点」，支持 MONTH/YEAR 等单位。算年龄、算账龄用 TIMESTAMPDIFF。
6. GROUP_CONCAT 能把组内值拼成列表（如某个用户提交过的所有语言），默认长度上限 1024 字节，超长被截断，需要时调 SET SESSION group_concat_max_len = 102400;。

你可能会懵的地方

• NULL = '' 成立吗？ 不成立，结果是 NULL 而不是 FALSE。判断空值只能用 IS NULL / IS NOT NULL。这就是「空字符串和 NULL 是两回事」的具体表现。

• NOW()、CURDATE()、SYSDATE() 有什么区别？ NOW() 返回语句开始执行的时间（同一语句内不变），CURDATE() 是它的日期部分，SYSDATE() 返回真实当前时间（语句执行到它那一刻）。绝大多数场景用 NOW()。

• IFNULL 和 COALESCE 到底用哪个？ 两参数时等价。COALESCE 是 SQL 标准、支持任意多参数；IFNULL 是 MySQL 方言。跨库项目写 COALESCE。

• 为什么 TRIM 之后长度没变？ TRIM 默认只去空格，不去全角空格、制表符。TRIM(BOTH ' ' FROM col) 处理半角空格，特殊空白需要 REPLACE 配合。

• 把时间转成字符串再 GROUP BY，性能有问题吗？ 有。字符串化破坏了索引使用，大表按天/月分组应优先用范围条件预过滤（WHERE限定月份区间）再分组，或用 DATE() 而不是长格式 %Y-%m-%d %H:%i:%s。

错法对照

错法	问题	正解
CASE 不写 ELSE	未命中输出 NULL，报表出现「空标签」	总是写 ELSE，或 COALESCE 包一层
CONCAT(name, email) 遇 NULL	整个结果变 NULL	CONCAT_WS('', name, email)
WHERE YEAR(t)=2025	列上套函数，索引失效	WHERE t >= '2025-01-01' AND t < '2026-01-01'
SUM(...) 空集直接输出	报表出现 NULL 令人困惑	COALESCE(SUM(...), 0)
INT/VARCHAR 存手机号	丢了前导 0、无法格式化	VARCHAR(20) 存手机号

卡点急救

1. 插入/查询中文变问号或乱码：检查连接字符集。执行 SHOW VARIABLES LIKE 'character%';，客户端与连接层统一为 utf8mb4；JDBC 连接串加 characterEncoding=utf8mb4（8.0.13+ 可省略，自动协商）。
2. GROUP_CONCAT 结果被截断：调大 group_concat_max_len，这是会话级变量，应用连接里每次设置。
3. 时间差了 8 小时：TIMESTAMP 列受会话时区影响。执行 SET time_zone = '+08:00'; 或统一约定全库 DATETIME + 应用层管时区。

面试常问

• NULL 参与运算和聚合的行为？ 算术/比较运算带 NULL 结果是 NULL；聚合函数（SUM/AVG/MAX/MIN）忽略 NULL，作用于空集返回 NULL；COUNT(*) 数行不忽略 NULL 行，COUNT(列) 忽略。判断只能用 IS NULL。

• 写 SQL 统计「每个用户的连续登录天数」，函数层面怎么准备？ 核心是 DATE(时间) 归一化日期，再用「日期减去按日期排序的行号」做连续段分组（差值相同即连续）。函数是积木，第 7、12 课会把完整解法拼出来。

• COALESCE(a, b, c) 的求值顺序？ 从左到右返回第一个非 NULL 的值，全 NULL 返回 NULL。利用这个特性可以做「优先取值」逻辑，如 COALESCE(昵称, 用户名, '匿名用户')。

自测三问

1. CONCAT 和 CONCAT_WS 在遇到 NULL 时的行为差异是什么？
2. 为什么 WHERE DATE(created_at) = CURDATE() 是性能反模式？怎么改写？
3. SUM(x) 在没有匹配行时返回什么？如何让报表输出 0？

小结一句话

内置函数让数据变换留在数据库一层完成——CASE 管分支、COALESCE 管 NULL、日期函数管时间维度，但对索引列套函数是性能红线。

延伸

• MySQL 8.0 Reference Manual — String Functions
• MySQL 8.0 Reference Manual — Date and Time Functions
• 下一课：子查询——标量、列、表三类子查询与 IN/EXISTS 的选择，以及 NOT IN 的 NULL 陷阱。