📖 本篇简介：函数是 MySQL 把原始数据加工成业务结果的"工具箱"，几乎每条稍复杂的 SELECT 都会用到它。本篇在前面章节学完增删改查、连接、子查询的基础上，系统梳理字符串、日期时间、数值、流程控制与加密哈希五大类常用函数。学完后你能独立完成数据清洗、格式化输出、时间差计算、空值兜底等日常开发任务。本示例基于 MySQL 8.0 语法。
目录
- 1 为什么要学MySQL函数
- 2 字符串函数
- 3 日期与时间函数
- 4 数值函数
- 5 流程控制函数
- 6 加密与哈希函数
- 7 常用函数速查表
- 8 常见坑与注意事项
- 9 练习题
1 为什么要学MySQL函数
数据库里存的往往是"半成品"：手机号是 13812345678，但页面要显示成 138****5678；下单时间是 2026-10-07 14:23:05，但报表只关心"哪一天"；金额存的是 19.9，结算要按四舍五入取整。这些加工工作，既可以在应用层用 Python/Java 做，也可以直接用 SQL 函数在数据库层完成。
把计算下推到数据库层的好处很直接：减少网络传输量（只传加工后的结果）、利用数据库已有的索引与并行能力、让口径统一（多个报表用同一条 SQL，不会各算各的）。当然也不是什么计算都该塞进 SQL——涉及复杂业务规则、需要频繁迭代的逻辑，放在应用层更好维护。函数用得好，是效率；用得滥，是灾难。
MySQL 的函数大致分五类：
| 类别 | 作用 | 典型场景 |
|---|---|---|
| 字符串函数 | 拼接、截取、替换、大小写转换 | 脱敏、格式化、清洗 |
| 日期时间函数 | 取当前时间、加减时间、格式化 | 报表按天/月统计、超时判断 |
| 数值函数 | 四舍五入、取整、求模 | 金额结算、分页 |
| 流程控制函数 | 根据条件返回不同值 | 空值兜底、分类打标 |
| 加密哈希函数 | 单向散列、对称加密 | 密码存储、敏感数据 |
下面逐类展开。所有示例基于 MySQL 8.0，可在自己的测试库中直接执行。
2 字符串函数
字符串是业务里最常被加工的数据。先看一组最常用的：
-- 字符串常用函数演示（MySQL 8.0）
SELECT
CONCAT('Hello', ' ', 'MySQL') AS s1, -- 拼接：Hello MySQL
LENGTH('abc中文') AS s2, -- 字节数：utf8mb4 下为 9（3 个字母 3 字节 + 2 个中文 6 字节）
CHAR_LENGTH('abc中文') AS s3, -- 字符数：6
UPPER('abc') AS s4, -- 转大写：ABC
LOWER('ABC') AS s5, -- 转小写：abc
TRIM(' hi ') AS s6, -- 去首尾空格：hi
SUBSTRING('hello world', 1, 5) AS s7, -- 从第 1 个字符取 5 个：hello
REPLACE('a-b-c', '-', '_') AS s8; -- 替换：a_b_c
这里有个新手最容易踩的坑：LENGTH 和 CHAR_LENGTH 的区别。LENGTH 返回字节数，CHAR_LENGTH 返回字符数。一个中文在 utf8mb4 下占 3~4 个字节，所以统计"字符串有几个字"一定要用 CHAR_LENGTH。
下面用 Python + sqlite3 实跑验证字符级截取与替换的行为（sqlite3 的 LENGTH 按字符计，正好用来理解"字符数"这个概念）：
# 用 sqlite3 实跑验证字符串函数行为（Python 3.14 + sqlite3 3.53）
import sqlite3
con = sqlite3.connect(':memory:')
cur = con.cursor()
rows = cur.execute('''
SELECT UPPER('mysql'), LOWER('MySQL'), LENGTH('abc中文'),
SUBSTR('hello world',1,5), TRIM(' hi '), REPLACE('a-b-c','-','_')
''').fetchall()
for r in rows:
print(r)
con.close()
实跑输出：
('MYSQL', 'mysql', 5, 'hello', 'hi', 'a_b_c')
注意 sqlite3 里 LENGTH('abc中文') 得到 5（按字符），而在 MySQL 里对应概念要用 CHAR_LENGTH('abc中文') 才是 5；若误用 LENGTH，MySQL 返回的是 9（字节数）。这个差异在跨数据库迁移时尤其要小心。
2.1 实用：手机号与身份证脱敏
业务里经常要对敏感信息脱敏，标准做法是"保留前几位、星号代替中间、保留后几位"：
-- 手机号脱敏：138****5678（MySQL 8.0）
SELECT
CONCAT(
SUBSTRING(phone, 1, 3), -- 取前 3 位
'****', -- 固定 4 个星号
SUBSTRING(phone, 8) -- 从第 8 位取到末尾，即后 4 位
) AS masked_phone
FROM user;
-- 身份证脱敏：保留前 6 位与后 4 位，中间用 8 个星号
SELECT CONCAT(SUBSTRING(id_card,1,6), '********', SUBSTRING(id_card,15))
FROM user;
2.2 其他常用字符串函数
| 函数 | 作用 | 示例 | 结果 |
|---|---|---|---|
| CONCAT_WS(分隔符, s1, s2...) | 带分隔符拼接 | CONCAT_WS('-','2026','10','07') | 2026-10-07 |
| LEFT(s,n) / RIGHT(s,n) | 取左/右 n 个字符 | RIGHT('abcdef',3) | def |
| LPAD(s,n,填充) / RPAD | 左/右补齐到 n 个字符 | LPAD('7',3,'0') | 007 |
| INSTR(s,子串) | 子串首次出现位置 | INSTR('hello','l') | 3 |
| LOCATE(子串,s) | 同 INSTR | LOCATE('l','hello') | 3 |
| REVERSE(s) | 反转字符串 | REVERSE('abc') | cba |
| SPACE(n) | 生成 n 个空格 | CONCAT('[',SPACE(3),']') | [ ] |
CONCAT_WS 的好处是自动跳过 NULL：CONCAT('a',NULL,'b') 结果是 NULL（任何字符串拼 NULL 都是 NULL），而 CONCAT_WS('-','a',NULL,'b') 结果是 a-b。这在拼地址、拼标签时非常实用。
2.3 实战：用字符串函数清洗脏数据
假设导入了一批用户数据，字段里混着多余空格、全半角混用、手机号带横杠。清洗思路就是"先用函数把脏数据标准化，再做查询"：
-- 脏数据清洗示例（MySQL 8.0）
-- 原始表 user_raw(name VARCHAR(50), phone VARCHAR(20), address VARCHAR(200))
SELECT
TRIM(BOTH ' ' FROM name) AS clean_name, -- 去掉首尾空格
REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone, -- 去掉横杠和空格
CONCAT_WS(' ', -- 用空格拼接非空地址段
address, '') AS addr
FROM user_raw;
常见的脏数据形态与对应函数：
| 脏数据形态 | 问题 | 清洗函数 |
|---|---|---|
| ' 张三 ' | 首尾空格 | TRIM |
| '138-1234-5678' | 含横杠 | REPLACE(phone,'-','') |
| 'ＡＢＣ' | 全角字符 | 应用层转半角后入库 |
| 'abc@EXAMPLE.com' | 大小写不一 | LOWER(email) 统一 |
| '' 空串 | 当作 NULL 处理 | NULLIF(col,'') |
这里 NULLIF(col,'') 很常用：业务里空字符串和 NULL 往往是两回事，但脏数据里经常混在一起，用 NULLIF('','') 把空串转成 NULL，后续 IFNULL、聚合统计才不会把空串算成"有值"。
3 日期与时间函数
时间是报表类业务的核心维度。先记住最常用的"取当前时间"：
-- 时间取值（MySQL 8.0）
SELECT
NOW() AS now1, -- 日期+时间：2026-10-07 14:23:05
CURDATE() AS today, -- 仅日期：2026-10-07
CURTIME() AS time1, -- 仅时间：14:23:05
CURRENT_TIMESTAMP AS now2; -- 与 NOW() 等价
3.1 日期加减与截取
DATE_ADD / DATE_SUB 做时间偏移，EXTRACT / YEAR / MONTH / DAY 做截取：
-- 日期加减与截取（MySQL 8.0）
SELECT
DATE_ADD('2026-10-07', INTERVAL 7 DAY) AS next_week, -- 一周后
DATE_SUB('2026-10-07', INTERVAL 1 MONTH) AS last_month, -- 一个月前
DATEDIFF('2026-10-07', '2026-01-01') AS diff_days, -- 相差天数：279
YEAR('2026-10-07') AS y, -- 2026
MONTH('2026-10-07') AS m, -- 10
DAY('2026-10-07') AS d, -- 7
HOUR(NOW()) AS h; -- 当前小时
DATEDIFF 只算日期部分的天数差，忽略时间；如果要精确到秒的差，用 TIMESTAMPDIFF(单位, 开始, 结束)：
-- 精确时间差（MySQL 8.0）
SELECT
TIMESTAMPDIFF(SECOND, '2026-10-07 14:00:00', '2026-10-07 14:23:05') AS sec_diff, -- 1385
TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-10-07') AS mon_diff; -- 8
3.2 日期格式化
DATE_FORMAT 把时间格式化成任意字符串，占位符对照表：
| 占位符 | 含义 | 示例 |
|---|---|---|
| %Y | 4 位年 | 2026 |
| %m | 2 位月 | 10 |
| %d | 2 位日 | 07 |
| %H | 24 小时制小时 | 14 |
| %i | 分钟 | 23 |
| %s | 秒 | 05 |
| %W | 星期名 | Wednesday |
| %Y-%m | 年月 | 2026-10 |
-- 格式化输出（MySQL 8.0）
SELECT
DATE_FORMAT(NOW(), '%Y-%m-%d') AS day1, -- 2026-10-07
DATE_FORMAT(NOW(), '%Y年%m月%d日') AS cn1, -- 2026年10月07日
DATE_FORMAT(NOW(), '%Y-%m') AS month1;-- 2026-10
报表里按"年月"分组统计时，DATE_FORMAT(create_time, '%Y-%m') 就是最常用的分组键。
3.3 实战：按时间维度做报表
日报、周报、月报是 BI 最常见的需求。核心都是"把时间字段截断成某个粒度，再分组聚合"：
-- 按年月统计订单量与金额（MySQL 8.0）
SELECT
DATE_FORMAT(create_time, '%Y-%m') AS ym, -- 年月
COUNT(*) AS order_cnt, -- 订单数
SUM(amount) AS total_amt -- 总金额
FROM orders
WHERE create_time >= '2026-01-01'
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
ORDER BY ym;
按"周"统计可以用 YEARWEEK(create_time)，它返回 202640 这样的"年+周"编号；判断"是否本周"则用 YEARWEEK(create_time) = YEARWEEK(CURDATE())。
| 报表粒度 | 分组键写法 | 结果示例 |
|---|---|---|
| 日 | DATE(create_time) | 2026-10-07 |
| 周 | YEARWEEK(create_time) | 202640 |
| 月 | DATE_FORMAT(create_time,'%Y-%m') | 2026-10 |
| 季 | QUARTER(create_time) | 4 |
| 年 | YEAR(create_time) | 2026 |
注意：YEARWEEK 默认周日为一周开始，国内习惯周一开始，可加第二参数 YEARWEEK(create_time, 3) 表示周一开头且按 ISO 周计算，跨周统计口径才不会乱。
4 数值函数
数值函数处理金额、数量、比例。最常用的是四舍五入与取整：
-- 数值函数（MySQL 8.0）
SELECT
ROUND(19.567, 2) AS r1, -- 四舍五入保留 2 位：19.57
ROUND(19.567, 0) AS r2, -- 取整：20
CEILING(19.1) AS c1, -- 向上取整：20
FLOOR(19.9) AS f1, -- 向下取整：19
TRUNCATE(19.567, 2) AS t1, -- 截断（不四舍五入）：19.56
MOD(10, 3) AS m1, -- 取模：1
ABS(-5) AS a1, -- 绝对值：5
POW(2, 10) AS p1; -- 幂：1024
注意 ROUND 与 TRUNCATE 的区别：ROUND(19.567, 2) 是 19.57（四舍五入），TRUNCATE(19.567, 2) 是 19.56（直接砍后面的小数）。金额结算该用哪个要和业务确认，别想当然。
分页场景常常用 CEILING 算总页数：
-- 计算总页数（每页 20 条）（MySQL 8.0）
SELECT CEILING(COUNT(*) / 20) AS total_pages
FROM order_table;
4.1 实战：百分比与排名计算
做转化率、占比这类指标时，数值函数配合 ROUND 控制小数位：
-- 计算各地区销售额占比（MySQL 8.0）
SELECT
region,
SUM(amount) AS total,
ROUND(SUM(amount) / (SELECT SUM(amount) FROM orders) * 100, 2) AS pct
FROM orders
GROUP BY region;
用 Python + sqlite3 实跑一遍占比计算逻辑，确认聚合后结果正确：
# sqlite3 实跑验证分组聚合与占比计算（Python 3.14 + sqlite3 3.53）
import sqlite3
con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.executescript('''
CREATE TABLE orders(region TEXT, amount INT);
INSERT INTO orders VALUES ('East',100),('East',200),('West',150),('West',50);
''')
total = cur.execute('SELECT SUM(amount) FROM orders').fetchone()[0]
for region, amt in cur.execute('SELECT region, SUM(amount) FROM orders GROUP BY region'):
print(region, amt, round(amt/total*100, 2))
con.close()
实跑输出：
East 300 60.0
West 200 40.0
对应到 MySQL，把 round(amt/total*100,2) 换成 ROUND(SUM(amount)/总金额*100,2) 即可，逻辑完全一致。
5 流程控制函数
流程控制函数让 SQL 能"根据条件返回不同值"。最常用三个：IF、IFNULL、NULLIF（CASE WHEN 更完整的写法放在下一篇专题讲）。
-- 流程控制函数（MySQL 8.0）
SELECT
IF(10 > 5, '大', '小') AS c1, -- 条件为真返回第一个：大
IFNULL(NULL, '默认值') AS c2, -- 第一个为 NULL 则返回第二个：默认值
IFNULL('实际值', '默认值') AS c3, -- 第一个非 NULL 直接返回：实际值
NULLIF('a', 'a') AS c4, -- 两值相等返回 NULL：NULL
NULLIF('a', 'b') AS c5; -- 两值不等返回第一个：a
IF(条件, a, b)：三元表达式，条件真返回 a，假返回 b。IFNULL(a, b)：a 为 NULL 时返回 b，否则返回 a。常用于"字段为空时给个兜底文案"。NULLIF(a, b)：a 等于 b 时返回 NULL，否则返回 a。常用来避免除零错误。
除零保护是 NULLIF 的经典用法：
-- 避免除零错误（MySQL 8.0）
SELECT
100 / NULLIF(0, 0) AS safe_div; -- 分母为 0 时 NULLIF 返回 NULL，整体结果为 NULL 而不是报错
5.1 实战：用 IF + IFNULL 给用户打标
把前面学的函数组合起来：按消费金额给用户分等级，空值给默认文案：
-- 用户标签输出（MySQL 8.0）
SELECT
user_id,
IFNULL(nickname, '匿名用户') AS name, -- 空昵称兜底
IF(total_amount >= 10000, 'VIP',
IF(total_amount >= 1000, '会员', '普通')) AS level, -- 多级分类
IFNULL(phone, '未绑定') AS phone_show
FROM user_stat;
这里 IF 嵌套实现了三档分类，IFNULL 负责空值兜底。注意 IF 嵌套不要超过 3 层，超过后可读性会急剧下降——更复杂的多分支应该用下一篇要讲的 CASE WHEN。
6 加密与哈希函数
这类函数用于把敏感信息变成不可逆或可逆的密文。密码绝对不能存明文，业界标准是加盐后用单向哈希存储。
-- 加密哈希函数（MySQL 8.0）
SELECT
MD5('abc123') AS h1, -- 32 位 MD5（已不安全，仅作历史兼容）
SHA1('abc123') AS h2, -- 40 位 SHA-1
SHA2('abc123', 256) AS h3, -- 64 位 SHA-256（推荐）
SHA2(CONCAT('salt_', 'abc123'), 256) AS h4; -- 加盐后再哈希
| 函数 | 输出长度 | 单向/可逆 | 安全性 |
|---|---|---|---|
| MD5(s) | 32 位十六进制 | 单向 | 弱，已被彩虹表破解，不推荐新系统 |
| SHA1(s) | 40 位十六进制 | 单向 | 一般，也有碰撞风险 |
| SHA2(s,256) | 64 位十六进制 | 单向 | 较强，可用 |
| AES_ENCRYPT(s,key) | 二进制 | 可逆 | 对称加密，需保管好密钥 |
要点：
1. 存密码用 SHA2(密码 + 随机盐, 256)，盐要每个用户随机，存到用户表里。
2. MD5 和 SHA1 都不要用于新系统的密码存储。
3. AES_ENCRYPT 是可逆的，适合需要解密还原的字段（如身份证），但密钥管理是另一个难题，通常更建议在应用层做加解密，数据库只存密文。
6.1 关于已废弃函数的提醒
MySQL 8.0 里有些老函数已经或将要被移除，看到老代码要心里有数：
| 函数 | 状态 | 替代方案 |
|---|---|---|
| PASSWORD(str) | 8.0 已移除 | 用应用层 bcrypt/Argon2 |
| ENCODE/DECODE | 已不推荐 | AES_ENCRYPT/AES_DECRYPT |
| MD5 | 保留但不推荐新用 | SHA2(s,256) |
面试或读老项目代码时，遇到 PASSWORD() 不要照着写——它在 8.0 直接报错，且设计本就不适合做业务密码存储。
7 常用函数速查表
把本篇最常用的函数浓缩成一张速查表，开发时随手查：
| 类别 | 函数 | 一句话作用 |
|---|---|---|
| 字符串 | CONCAT / CONCAT_WS | 拼接 / 带分隔符拼接 |
| 字符串 | SUBSTRING(s,pos,len) | 截取子串 |
| 字符串 | LEFT / RIGHT | 取左/右 n 字符 |
| 字符串 | UPPER / LOWER | 大小写转换 |
| 字符串 | TRIM / LTRIM / RTRIM | 去空格 |
| 字符串 | REPLACE(s,old,new) | 替换 |
| 字符串 | LENGTH / CHAR_LENGTH | 字节数 / 字符数 |
| 字符串 | LPAD / RPAD | 补齐 |
| 日期 | NOW / CURDATE / CURTIME | 当前时间 |
| 日期 | DATE_ADD / DATE_SUB | 时间加减 |
| 日期 | DATEDIFF / TIMESTAMPDIFF | 时间差 |
| 日期 | YEAR / MONTH / DAY / HOUR | 截取时间分量 |
| 日期 | DATE_FORMAT | 格式化 |
| 数值 | ROUND / TRUNCATE | 四舍五入 / 截断 |
| 数值 | CEILING / FLOOR | 向上/向下取整 |
| 数值 | MOD / ABS | 取模 / 绝对值 |
| 流程 | IF(cond,a,b) | 三元 |
| 流程 | IFNULL(a,b) | 空值兜底 |
| 流程 | NULLIF(a,b) | 相等返回 NULL |
| 加密 | MD5 / SHA1 / SHA2 | 单向哈希 |
8 常见坑与注意事项
1. `LENGTH` 当字符数用：统计"几个人名"时用了 LENGTH，结果一个中文算 3~4，长度全错。统一用 CHAR_LENGTH 数字符。
2. `CONCAT` 遇到 NULL 变 NULL：CONCAT('a', NULL, 'b') 结果是 NULL。拼接字段可能为空时用 CONCAT_WS，或先 IFNULL(字段,'') 兜底。
3. 日期格式串写错：%m 是月份、%i 才是分钟，写反会得到离谱的数字。DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') 是标准写法。
4. 在索引列上套函数导致索引失效：WHERE DATE(create_time) = '2026-10-07' 这种写法会让 create_time 上的索引用不上（后面索引篇详讲）。范围查询用 WHERE create_time >= '2026-10-07' AND create_time < '2026-10-08'。
5. `ROUND` 的银行家舍入：MySQL 的 ROUND 对 .5 是"四舍六入五成双"，金额恰好是 x.5 时结果可能和预期差 1 分，对账前要确认口径。
6. 用 MD5 存密码：MD5 太快、彩虹表泛滥，新系统一律 SHA2(密码+随机盐, 256) 起步，更稳的是应用层用 bcrypt/Argon2。
8.1 本章小结
函数不是背出来的，是用出来的。建议把速查表收藏在编辑器旁边，遇到具体需求时先想"这个加工动作属于哪一类"——是改字符串、算时间、调数值、做判断还是加密——再去查对应函数。真正写多了你会发现，日常反复用的其实就是十多个函数，其他的需要时再查即可。下一篇要讲的 CASE WHEN，是流程控制里最强大、也最能体现 SQL 表达力的写法，务必吃透。
9 练习题
练习 1：写一个手机号脱敏 SQL
已知表 user(phone VARCHAR(11))，查出所有用户手机号，显示成前 3 位 + **** + 后 4 位。
<details>
<summary>参考答案</summary>
-- 手机号脱敏（MySQL 8.0）
SELECT CONCAT(SUBSTRING(phone,1,3), '****', SUBSTRING(phone,8)) AS masked
FROM user;
</details>
练习 2：统计"今天"新增订单数
表 orders(create_time DATETIME)，统计今天（自然日）的订单条数。要求能用上索引。
<details>
<summary>参考答案</summary>
不要写 WHERE DATE(create_time) = CURDATE()（索引失效），改用范围：
-- 今天订单数（MySQL 8.0）
SELECT COUNT(*) FROM orders
WHERE create_time >= CURDATE()
AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY);
</details>
练习 3：把金额按"万"格式化
已知 amount DECIMAL(10,2)，查出金额并显示成"xx.x 万"形式（如 123456 显示 12.3万），保留 1 位小数。
<details>
<summary>参考答案</summary>
-- 金额转万元（MySQL 8.0）
SELECT CONCAT(ROUND(amount/10000, 1), '万') AS amt_wan
FROM orders;
</details>
练习 4：避免除零
写一条 SQL，计算每个部门的"人均成本" total_cost / headcount，当 headcount 为 0 时结果不要报错。
<details>
<summary>参考答案</summary>
-- 人均成本除零保护（MySQL 8.0）
SELECT dept, total_cost / NULLIF(headcount, 0) AS avg_cost
FROM dept_stat;
</details>
练习 5：拼接完整收货地址
表 addr(user_id, province, city, detail)，其中 city 可能为 NULL。要求查出完整地址，城市为空时不出现多余空格。
<details>
<summary>参考答案</summary>
用 CONCAT_WS 自动跳过 NULL：
-- 拼接收货地址（MySQL 8.0）
SELECT user_id,
CONCAT_WS(' ', province, city, detail) AS full_addr
FROM addr;
如果硬写 CONCAT(province, city, detail)，只要 city 为 NULL，整条地址都会变 NULL，这正是 CONCAT_WS 的用武之地。
</details>
下一篇预告：第 10 章 条件逻辑与CASE WHEN
文章回复
0 条公开回复