MySQL聚合、日期、字符串等函数深度剖析
在日常的数据库开发中，我们不仅要进行基础的增删改查，更需要对数据进行深度的加工与计算。MySQL提供了极其丰富的内置函数，涵盖了聚合、日期、字符串、控制流等多个维度。熟练掌握这些函数，不仅能大幅度减少后端代码的处理逻辑，还能充分利用数据库引擎的优化能力，提升系统整体性能。本文将对MySQL中最核心、最常用的函数进行深度剖析。
一、 聚合函数：数据统计的利器
聚合函数通常与 GROUP BY 子句一起使用，用于对一组值进行计算，并返回单个值。它们是报表统计和数据分析的基础。
1. 基础聚合：COUNT, SUM, AVG, MAX, MIN
这五大聚合函数最为常见。需要注意的是 COUNT(*) 与 COUNT(字段) 的区别：COUNT(*) 会统计包含 NULL 值的所有行，而 COUNT(字段) 会忽略该字段为 NULL 的行。
-- 统计各部门的员工数量、平均薪资、最高与最低薪资及总薪资 SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary, MAX(salary) AS max_salary, MIN(salary) AS min_salary, SUM(salary) AS total_salary FROM employees GROUP BY department_id;
2. 进阶聚合：GROUP_CONCAT
当我们不仅需要分组统计，还需要把分组内的某个字段的值拼接在一起时，GROUP_CONCAT 是绝佳的选择。
-- 查询每个部门的所有员工姓名，用逗号分隔 SELECT department_id, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employee_names FROM employees GROUP BY department_id;
二、 日期与时间函数：时间序列的掌控者
业务系统中充斥着时间维度的查询与计算，如统计近一个月的活跃用户、计算用户年龄等。
1. 获取当前时间
NOW() 获取当前日期和时间，CURDATE() 获取当前日期，CURTIME() 获取当前时间。
-- 插入一条带有当前时间的记录
INSERT INTO system_logs (log_content, created_at)
VALUES ('用户登录成功', NOW());2. 日期格式化与解析
DATE_FORMAT(date, format) 将日期转为字符串，STR_TO_DATE(str, format) 将字符串转为日期。
-- 将当前时间格式化为年-月-日 时:分:秒
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_time;
-- 将字符串解析为日期进行查询
SELECT * FROM orders
WHERE order_date = STR_TO_DATE('2023-10-01', '%Y-%m-%d');3. 日期计算与差值
DATE_ADD() 和 DATE_SUB() 用于日期的加减，DATEDIFF() 用于计算两个日期的差值。
-- 查询注册时间超过30天的用户 SELECT user_id, username FROM users WHERE register_date < DATE_SUB(NOW(), INTERVAL 30 DAY); -- 计算订单创建时间与支付时间的间隔天数 SELECT order_id, DATEDIFF(pay_time, create_time) AS pay_duration_days FROM orders WHERE pay_status = 1;
三、 字符串函数：文本处理的魔法师
字符串函数在数据清洗、格式化输出和模糊匹配中扮演着重要角色。
1. 拼接与截取
CONCAT() 用于拼接字符串，SUBSTRING() 用于截取子串。
-- 拼接用户的完整称呼 SELECT CONCAT(last_name, ' ', first_name, ' - ', job_title) AS employee_info FROM employees; -- 截取邮箱的域名部分 (假设邮箱为 user@www.ipipp.com) SELECT email, SUBSTRING(email, INSTR(email, '@') + 1) AS email_domain FROM users;
2. 替换与清洗
REPLACE() 用于字符串替换，TRIM() 用于去除首尾空格。
-- 将文章内容中的旧域名替换为新域名 www.ipipp.com UPDATE articles SET content = REPLACE(content, 'www.old-domain.com', 'www.ipipp.com') WHERE content LIKE '%www.old-domain.com%'; -- 清洗用户输入的多余空格 SELECT TRIM(username) AS clean_username FROM users;
四、 控制流函数：逻辑判断的枢纽
在SQL层面进行逻辑判断，可以避免将大量数据拉取到应用层再进行循环处理。
1. IFNULL 与 IF
IFNULL(expr1, expr2) 如果第一个参数为NULL则返回第二个参数；IF(expr1, expr2, expr3) 如果条件成立返回expr2，否则返回expr3。
-- 处理空值：如果没有折扣，则显示0 SELECT product_name, IFNULL(discount_rate, 0) AS discount FROM products; -- 简单逻辑判断 SELECT name, IF(score >= 60, '及格', '不及格') AS exam_result FROM students;
2. CASE WHEN
这是SQL中最强大的条件表达式，类似于编程语言中的 switch-case 或 if-else if-else。
-- 将数字等级映射为文字描述 SELECT customer_name, credit_score, CASE WHEN credit_score >= 800 THEN '极好' WHEN credit_score >= 700 THEN '优秀' WHEN credit_score >= 600 THEN '良好' ELSE '一般' END AS credit_level FROM customers;
五、 性能避坑指南与最佳实践
虽然函数非常强大，但不恰当的使用往往会成为性能杀手。以下是几个核心注意点：
1. 警惕索引失效
在 WHERE 条件中对字段使用函数，会导致MySQL无法使用该字段的索引，从而退化为全表扫描。例如 WHERE YEAR(create_time) = 2023 会导致索引失效，应改写为范围查询。
-- 错误写法：索引失效 SELECT * FROM orders WHERE YEAR(create_time) = 2023; -- 正确写法：走索引 SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
2. 避免在 SELECT 中过度使用函数
虽然SELECT中使用函数不会影响索引，但在处理百万级数据时，复杂的字符串操作或计算会消耗大量的CPU资源，导致查询极慢。建议在非实时要求的场景下，将计算逻辑下放到数仓或通过定时任务预处理。
3. 合理利用生成列（Generated Columns）
如果某个函数计算的结果需要频繁查询，可以考虑使用MySQL 5.7+ 引入的生成列，将该函数结果持久化或虚拟化存储，甚至可以在生成列上建立索引。
-- 创建一个虚拟生成列，并基于JSON字段提取数据 ALTER TABLE system_config ADD COLUMN site_url VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(config_data, '$.site_url'))) VIRTUAL; -- 在生成列上建立索引 CREATE INDEX idx_site_url ON system_config(site_url);
结语
MySQL内置函数是SQL语言的灵魂所在，精妙地组合使用它们，可以让原本需要在应用层完成的复杂逻辑在数据库层高效完成。然而，便捷的背后也隐藏着性能陷阱。只有深入理解每个函数的特性及其对查询优化器的影响，才能在开发中写出既优雅又高效的SQL语句。