三、MySQL函数

一、什么是函数

你可以把函数想象成 MySQL 内置的“加工厂”或“小工具”。它是预先定义好的代码逻辑，你只需要把原始数据（参数）丢进去，它就会按照设定的规则进行处理，然后返回一个结果。这就像榨汁机，你放进去水果（参数），它就能榨出果汁（返回值），省去了你自己动手切水果的麻烦。

使用函数的主要优势：

• 提升效率：将复杂计算逻辑封装在数据库层执行，减少应用层代码量。
• 保证一致性：相同的业务规则（如日期格式化、金额舍入）在 SQL 中统一处理，避免不同应用实现不一致。
• 简化查询：让 SQL 语句更清晰、更易读。

二、函数的分类

按照功能，MySQL 的内置函数主要分为以下几大类：

1. 字符串函数

字符串函数是日常开发中最常用的，主要用于数据清洗、格式化展示等场景。

2. 数值函数

数值函数用于直接在查询中执行数学计算，提升效率，避免把数据取出来再用后端代码计算。

3. 日期函数

日期函数是构建时效性业务（如会员有效期、活动倒计时、报表统计）的基石。

4. 流程控制函数

这类函数赋予了 SQL 强大的分支逻辑表达能力，让你可以在查询时直接根据条件返回不同的结果，避免在应用层写大量的 if-else。

5. 聚合函数

聚合函数通常与 GROUP BY 搭配使用，用来对多行数据进行统计。它是生成各种数据报表的核心。

三、字符串函数

字符串函数是日常开发中最常用的，主要用于数据清洗、格式化展示等场景。

1. CONCAT(str1, str2, …)：拼接字符串。

场景说明：在电商系统中，我们通常会把用户的“姓”和“名”分开存储，但在前端展示时需要连在一起。

— 示例
SELECT CONCAT('张', '三');
— 返回：张三

— 实际表查询示例
SELECT CONCAT(last_name, first_name) AS full_name FROM users;

2. SUBSTRING(str, start, len)：截取子串。

场景说明：处理身份证号或手机号时，出于隐私保护，我们可能需要截取部分字符进行脱敏显示。

— 示例：截取手机号前3位
SELECT SUBSTRING('13812345678', 1, 3);
— 返回：138

— 示例：身份证号脱敏，显示前6位和后4位
SELECT CONCAT(SUBSTRING(id_card, 1, 6), '****', SUBSTRING(id_card, –4)) AS masked_id FROM customers;

3. REPLACE(str, from_str, to_str)：替换字符串。

场景说明：用户输入的评论中可能包含敏感词，我们需要将其替换为星号。

— 示例
SELECT REPLACE('你好傻瓜', '傻瓜', '**');
— 返回：你好**

— 实际应用：批量更新评论中的敏感词
UPDATE comments SET content = REPLACE(content, '敏感词', '***');

4. TRIM(str)：去除首尾空格。

场景说明：用户在注册时输入用户名，可能不小心多打了空格，用 TRIM 可以清洗数据。

— 示例
SELECT TRIM(' hello ');
— 返回：hello

— 插入数据时自动清洗
INSERT INTO users (username) VALUES (TRIM(' new_user '));

其他常用字符串函数速查：

• LENGTH(str) / CHAR_LENGTH(str)：返回字符串的字节长度 / 字符长度。
• UPPER(str) / LOWER(str)：将字符串转换为大写 / 小写。
• LEFT(str, len) / RIGHT(str, len)：从左边 / 右边开始截取指定长度的字符串。
• LOCATE(substr, str)：返回子串在字符串中第一次出现的位置。

四、数值函数

数值函数用于直接在查询中执行数学计算，提升效率，避免把数据取出来再用后端代码计算。

1. ROUND(x, d)：四舍五入，保留 d 位小数。

场景说明：计算商品打折后的价格，财务上通常只保留两位小数。

— 示例
SELECT ROUND(99.876, 2);
— 返回：99.88

— 实际计算商品折后价
SELECT product_name, price, ROUND(price * 0.88, 2) AS discounted_price FROM products;

2. CEIL(x) / FLOOR(x)：向上取整 / 向下取整。

场景说明：做分页功能时，计算总页数。比如 11 条数据，每页 10 条，总页数应该是 2 页，这就需要向上取整。

— 示例：计算总页数
SELECT CEIL(11 / 10);
— 返回：2

— 示例：价格按“百”向上取整报价
SELECT product_name, price, CEIL(price / 100) * 100 AS quoted_price FROM products;

3. ABS(x)：求绝对值。

场景说明：计算两个温度之间的温差，结果不应为负数。

— 示例
SELECT ABS(–10);
— 返回：10

— 计算温度波动范围
SELECT MAX(temp) – MIN(temp) AS temp_range, ABS(MAX(temp) – MIN(temp)) AS abs_range FROM temperature_log;

4. MOD(x, y)：求余数（取模）。

场景说明：判断一个订单号是奇数还是偶数，或者用于数据库分库分表的哈希路由。

— 示例
SELECT MOD(10, 3);
— 返回：1

— 判断订单奇偶性
SELECT order_id, IF(MOD(order_id, 2) = 0, '偶数订单', '奇数订单') AS parity FROM orders;

其他常用数值函数速查：

• POW(x, y) / SQRT(x)：求 x 的 y 次幂 / 求平方根。
• RAND()：返回一个 0 到 1 之间的随机浮点数。
• SIGN(x)：返回参数的符号（正数返回1，负数返回-1，0返回0）。

五、日期函数

日期函数是构建时效性业务（如会员有效期、活动倒计时、报表统计）的基石。

1. NOW() / CURDATE()：获取当前日期和时间 / 仅获取当前日期。

场景说明：用户下单时，系统需要记录精确的下单时间。

— 示例
SELECT NOW();
— 返回：2026-08-06 14:45:36

SELECT CURDATE();
— 返回：2026-08-06

— 插入当前时间戳
INSERT INTO orders (user_id, product_id, created_at) VALUES (1001, 2001, NOW());

2. DATEDIFF(date1, date2)：计算两个日期相差的天数。

场景说明：计算员工的工龄，或者计算订单距离超时还剩多少天。

— 示例
SELECT DATEDIFF('2026-08-06', '2026-08-01');
— 返回：5

— 计算会员剩余有效期（天）
SELECT user_id, DATEDIFF(expiry_date, CURDATE()) AS days_left FROM memberships WHERE days_left > 0;

3. DATE_ADD(date, INTERVAL num unit)：给日期增加时间间隔。

场景说明：用户购买了 30 天的 VIP 会员，需要计算会员的到期时间。

— 示例
SELECT DATE_ADD('2026-08-06', INTERVAL 30 DAY);
— 返回：2026-09-05

— 计算订单预计送达时间（3个工作日后）
SELECT order_id, DATE_ADD(order_date, INTERVAL 3 DAY) AS estimated_delivery FROM orders;

常用时间单位：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND。

4. YEAR(date) / MONTH(date) / DAY(date)：提取年/月/日。

场景说明：生成年度销售报表时，需要按年份对数据进行分组统计。

— 示例
SELECT YEAR('2026-08-06');
— 返回：2026

— 按年份统计销售额
SELECT YEAR(order_date) AS order_year, SUM(amount) AS total_sales FROM orders GROUP BY order_year;

其他常用日期函数速查：

• CURTIME()：返回当前时间。
• DATE_SUB(date, INTERVAL expr unit)：从日期减去指定的时间间隔。
• DAYNAME(date) / MONTHNAME(date)：返回日期的星期名 / 月份名。
• WEEK(date)：返回日期是一年中的第几周。

六、流程控制函数

这类函数赋予了 SQL 强大的分支逻辑表达能力，让你可以在查询时直接根据条件返回不同的结果，避免在应用层写大量的 if-else。

1. IF(condition, val1, val2)：简单的条件判断（类似三元运算符）。

场景说明：查询学生成绩时，大于等于 60 分显示“及格”，否则显示“不及格”。

— 示例
SELECT IF(85 >= 60, '及格', '不及格');
— 返回：及格

— 实际查询学生成绩表
SELECT student_name, score, IF(score >= 60, '及格', '不及格') AS result FROM exam_scores;

2. IFNULL(val1, val2)：空值处理。

场景说明：用户的“昵称”字段可能为空，展示时如果为空，就显示默认值“匿名用户”。

— 示例
SELECT IFNULL(NULL, '匿名用户');
— 返回：匿名用户

— 查询用户信息，处理空昵称
SELECT user_id, IFNULL(nickname, '匿名用户') AS display_name FROM users;

3. CASE WHEN … THEN … ELSE … END：多条件分支判断（类似 switch-case）。

场景说明：根据订单状态码（0, 1, 2）翻译成人类可读的文字（待支付, 已发货, 已完成）。

— 示例
SELECT
CASE
WHEN 1 = 1 THEN '待支付'
WHEN 1 = 2 THEN '已发货'
ELSE '未知状态'
END;
— 返回：待支付

— 实际订单状态翻译
SELECT order_id,
CASE status
WHEN 0 THEN '待支付'
WHEN 1 THEN '已发货'
WHEN 2 THEN '已完成'
WHEN 3 THEN '已取消'
ELSE '状态异常'
END AS status_text
FROM orders;

七、聚合函数

聚合函数通常与 GROUP BY 搭配使用，用来对多行数据进行统计。它是生成各种数据报表的核心。

1. COUNT(*) / COUNT(列名)：统计行数。

场景说明：统计网站今天的总访问量，或者统计某个部门有多少名员工。

— 示例：统计总用户数
SELECT COUNT(*) FROM users;
— 返回：表中的总行数

— 统计特定部门的员工数（忽略 NULL）
SELECT COUNT(employee_id) FROM employees WHERE department = '技术部';

— 统计今日独立访客
SELECT COUNT(DISTINCT user_id) FROM site_visits WHERE visit_date = CURDATE();

2. SUM(列名)：求和。

场景说明：计算某个用户所有订单的总消费金额。

— 示例：计算用户 1001 的总消费
SELECT SUM(price) FROM orders WHERE user_id = 1001;
— 返回：该用户的总花费

— 按月份统计销售总额
SELECT MONTH(order_date) AS month, SUM(amount) AS monthly_sales FROM orders GROUP BY month;

3. AVG(列名)：求平均值。

场景说明：计算全班同学的数学平均成绩。

— 示例
SELECT AVG(score) FROM scores WHERE subject = '数学';
— 返回：平均分

— 计算每个产品的平均评分
SELECT product_id, AVG(rating) AS avg_rating FROM product_reviews GROUP BY product_id;

4. MAX(列名) / MIN(列名)：求最大值 / 最小值。

场景说明：找出公司里薪资最高和最低的员工，或者找出商品库里的最高价和最低价。

— 示例：找出最高薪资
SELECT MAX(salary) FROM employees;
— 返回：最高薪资

— 找出每个品类的最低价商品
SELECT category, MIN(price) AS lowest_price FROM products GROUP BY category;

八、注意事项

在实际开发中，尽量避免在 WHERE 子句中对字段使用函数（例如 WHERE YEAR(create_time) = 2026），这会导致该字段的索引失效，引发全表扫描，严重影响查询性能。尽量改写为范围查询，如 create_time BETWEEN '2026-01-01' AND '2026-12-31'。

性能影响原理对比：

原因分析：
如果不用函数：你查 create_time = '2026-05-01'，MySQL 知道 create_time 字段是排好序的，它可以直接去索引树里精准定位。

如果用了函数：MySQL 的索引里存的只是 create_time 的原始值（比如 2026-05-01 14:30:00），并没有存 YEAR(create_time) 计算后的结果。MySQL 没办法直接去索引里找，只能被迫把表里的每一行数据都拿出来，挨个计算 YEAR(create_time)，看看是不是等于 2026。这个“挨个拿出来计算”的过程，就是全表扫描。当你的表里有 100 万条数据时，这个计算过程会极其缓慢，严重拖垮系统性能。

最佳实践建议：