1. MySQL函数：数据库操作的瑞士军刀
作为数据库开发中最常用的工具之一，MySQL函数就像一把瑞士军刀，能帮我们高效处理各种数据操作。记得我刚入行时，每次写SQL都要翻文档查函数用法，直到有次在线上环境因为DATE_FORMAT格式写错导致报表全部出错，才真正意识到系统掌握这些函数的重要性。
MySQL函数主要分为三大类：字符串函数、数值函数和日期时间函数，每类都有几十个具体函数。实际工作中，80%的日常需求其实只需要掌握其中20%的核心函数就能应对。下面我就结合真实项目案例，带你系统梳理这些必会函数的使用技巧。
提示：所有示例基于MySQL 8.0版本，部分函数在低版本可能不支持
2. 字符串处理：从基础到高级实战
2.1 基础字符串操作三剑客
CONCAT、SUBSTRING和TRIM这三个函数构成了字符串处理的基础框架。上周我还用它们解决了用户地址字段的格式化问题：
-- 合并省市区字段并去除空格 UPDATE user_address SET full_address = CONCAT( TRIM(province), TRIM(city), TRIM(district), TRIM(detail) );这里有个坑要注意：CONCAT_WS才是处理带分隔符合并的更优选择，它自动跳过NULL值：
-- 更安全的合并方式（使用逗号分隔） SELECT CONCAT_WS(',', col1, col2, col3) FROM table;2.2 正则表达式的高级玩法
当基础函数不够用时，REGEXP系列函数就是大杀器。去年我们有个需求要校验产品编码格式，用正则轻松搞定：
-- 验证产品编码格式：AA-1234-BB SELECT product_code FROM products WHERE product_code REGEXP '^[A-Z]{2}-[0-9]{4}-[A-Z]{2}$';更强大的是REGEXP_REPLACE，我们曾用它批量清理脏数据：
-- 移除文本中的手机号码 UPDATE comments SET content = REGEXP_REPLACE(content, '1[3-9][0-9]{9}', '***') WHERE content REGEXP '1[3-9][0-9]{9}';2.3 字符集转换的坑
处理多语言数据时，CONVERT和CAST函数必不可少。但要注意字符集兼容性问题：
-- 将latin1编码转为utf8mb4 SELECT CONVERT(column_name USING utf8mb4) FROM table; -- 处理表情符号存储 UPDATE messages SET content = CAST(content AS CHAR CHARACTER SET utf8mb4) WHERE content LIKE '%😊%';注意：MySQL 8.0默认已是utf8mb4，但旧版需要显式指定才能支持emoji
3. 数值计算：精度与性能的平衡
3.1 四舍五入的学问
ROUND函数看似简单，但金融场景下一个小数点差异可能造成重大损失：
-- 金融计算使用4位小数精度 SELECT ROUND(amount, 4) FROM transactions; -- 银行家舍入法（五舍六入） SELECT ROUND(2.5), ROUND(3.5); -- 结果都是4如果确实需要五舍五入，可以用这个技巧：
SELECT FLOOR(number + 0.5); -- 传统五舍五入3.2 随机数生成实战
开发抽奖功能时，RAND函数配合ORDER BY的用法很实用：
-- 随机选取10个幸运用户 SELECT user_id FROM users ORDER BY RAND() LIMIT 10;但大数据量表要小心性能问题，更好的做法是：
-- 高效随机抽样（假设user_id是连续整数） SELECT user_id FROM users WHERE user_id >= ( SELECT FLOOR(RAND() * (MAX(user_id) - MIN(user_id) + 1)) + MIN(user_id) FROM users ) LIMIT 10;3.3 聚合函数的进阶技巧
除了常见的SUM/AVG，还有一些高阶用法值得掌握：
-- 计算移动平均值（最近3个月） SELECT month, amount, AVG(amount) OVER (ORDER BY month ROWS 2 PRECEDING) AS moving_avg FROM sales; -- 百分比计算 SELECT category, COUNT(*) AS count, COUNT(*) / SUM(COUNT(*)) OVER () * 100 AS percentage FROM products GROUP BY category;4. 日期时间处理：避开时区陷阱
4.1 日期格式化大全
DATE_FORMAT函数有30多种格式符，这几个最常用：
-- 常见日期格式转换 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS date1, DATE_FORMAT(NOW(), '%H:%i:%s') AS time1, DATE_FORMAT(NOW(), '%W, %M %e %Y') AS date2;实际项目中，我整理了一份格式符速查表贴在工位上：
| 格式符 | 说明 | 示例 |
|---|---|---|
| %Y | 四位年份 | 2023 |
| %y | 两位年份 | 23 |
| %m | 月份(01-12) | 07 |
| %c | 月份(1-12) | 7 |
| %d | 日(01-31) | 05 |
| %H | 小时(00-23) | 14 |
| %i | 分钟(00-59) | 30 |
4.2 日期计算的坑
计算两个日期差值时，DATEDIFF和TIMESTAMPDIFF的区别很重要：
-- 计算年龄（精确到年） SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users; -- 计算工作日差异（需要自定义函数） DELIMITER // CREATE FUNCTION WORKDAY_DIFF(start_date DATE, end_date DATE) RETURNS INT BEGIN -- 实现逻辑... END // DELIMITER ;4.3 时区转换方案
跨国项目必须处理的时区问题：
-- 将UTC时间转为本地时间 SELECT CONVERT_TZ(created_at, '+00:00', @@session.time_zone) AS local_time FROM orders;更安全的做法是应用层处理时区，但有时不得不在SQL中处理：
-- 按北京时间统计每日订单 SELECT DATE(CONVERT_TZ(created_at, '+00:00', '+08:00')) AS bj_date, COUNT(*) FROM orders GROUP BY bj_date;5. 高级函数组合应用
5.1 条件逻辑函数
CASE WHEN是SQL中的if-else，配合函数使用更强大：
-- 用户分级计算 SELECT user_id, CASE WHEN TIMESTAMPDIFF(MONTH, register_date, NOW()) > 12 THEN '老用户' WHEN purchase_count > 5 THEN '活跃用户' ELSE '新用户' END AS user_level FROM users;5.2 窗口函数实战
MySQL 8.0引入的窗口函数彻底改变了复杂查询的写法：
-- 计算每个部门的薪资排名 SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees; -- 同比环比分析 SELECT month, revenue, LAG(revenue, 12) OVER (ORDER BY month) AS last_year, revenue / LAG(revenue, 12) OVER (ORDER BY month) AS yoy FROM monthly_sales;5.3 JSON处理函数
现代MySQL对JSON的支持非常完善：
-- 提取JSON字段 SELECT JSON_EXTRACT(profile, '$.address.city') AS city, JSON_CONTAINS(privileges, '"vip"') AS is_vip FROM users; -- 动态更新JSON UPDATE products SET specs = JSON_SET(specs, '$.weight', 10.5) WHERE product_id = 1001;6. 性能优化与避坑指南
6.1 函数索引的正确用法
在列上使用函数会导致索引失效：
-- 错误写法（索引失效） SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-07'; -- 正确写法（使用范围查询） SELECT * FROM orders WHERE create_time >= '2023-07-01' AND create_time < '2023-08-01';但MySQL 8.0支持函数索引：
-- 创建函数索引 ALTER TABLE products ADD INDEX idx_name_upper ((UPPER(product_name))); -- 使用函数索引查询 SELECT * FROM products WHERE UPPER(product_name) = 'LAPTOP';6.2 存储过程中的函数优化
在存储过程中过度使用函数会导致性能问题：
DELIMITER // CREATE PROCEDURE calculate_stats() BEGIN -- 低效做法：在循环内调用函数 DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT id FROM large_table; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO v_id; IF done THEN LEAVE read_loop; END IF; -- 每次循环都调用函数（低效） SET @result = SOME_FUNCTION(v_id); END LOOP; CLOSE cur; -- 更优做法：批量处理数据 INSERT INTO results SELECT id, SOME_FUNCTION(id) FROM large_table; END // DELIMITER ;6.3 自定义函数开发规范
创建自定义函数时要遵循这些最佳实践：
DELIMITER // CREATE FUNCTION SAFE_DIVIDE( numerator DECIMAL(20,6), denominator DECIMAL(20,6) ) RETURNS DECIMAL(20,6) DETERMINISTIC BEGIN DECLARE result DECIMAL(20,6); IF denominator = 0 THEN SET result = NULL; ELSE SET result = numerator / denominator; END IF; RETURN result; END // DELIMITER ;关键要点：
- 使用DETERMINISTIC声明确定性函数
- 包含完善的参数校验
- 处理所有边界情况
- 为函数添加详细注释
7. 真实业务场景综合案例
7.1 电商促销活动分析
分析双十一活动数据时，我用到了这些函数组合：
SELECT user_id, COUNT(DISTINCT order_id) AS order_count, SUM(amount) AS total_spend, ROUND(SUM(amount) / COUNT(DISTINCT order_id), 2) AS avg_order_value, TIMESTAMPDIFF(HOUR, MIN(create_time), MAX(create_time)) AS shopping_hours, GROUP_CONCAT(DISTINCT product_category) AS categories FROM orders WHERE create_time BETWEEN '2023-11-11 00:00:00' AND '2023-11-11 23:59:59' AND status = 'completed' GROUP BY user_id HAVING total_spend > 1000 ORDER BY total_spend DESC LIMIT 100;7.2 用户行为路径分析
使用窗口函数分析用户行为序列：
WITH user_events AS ( SELECT user_id, event_time, event_name, LAG(event_name, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event, LEAD(event_name, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event FROM user_activity WHERE event_date = CURDATE() ) SELECT user_id, event_time, CONCAT(prev_event, ' -> ', event_name, ' -> ', next_event) AS event_flow FROM user_events WHERE event_name = 'checkout';7.3 数据清洗自动化
定期运行的脏数据清洗脚本：
-- 清理无效电话号码 UPDATE customers SET phone = NULL WHERE phone REGEXP '^[0-9]{1,7}$'; -- 过短的号码 -- 标准化日期格式 UPDATE documents SET publish_date = CASE WHEN publish_date LIKE '__/__/____' THEN STR_TO_DATE(publish_date, '%d/%m/%Y') WHEN publish_date LIKE '____-__-__' THEN STR_TO_DATE(publish_date, '%Y-%m-%d') ELSE NULL END WHERE publish_date IS NOT NULL; -- 修复乱码文本 UPDATE product_reviews SET content = REPLACE(content, 'é', 'é') WHERE content LIKE '%é%';8. 函数调试与错误排查
8.1 常见错误代码解析
这些错误我踩过不止一次：
-- 错误1：参数类型不匹配 SELECT DATE_FORMAT('2023-13-01', '%Y-%m-%d'); -- 返回NULL并警告："Incorrect datetime value" -- 错误2：除零错误 SELECT LOG(0); -- 返回NULL并警告："Invalid argument for logarithm" -- 错误3：字符集问题 SELECT CONCAT('中文', _latin1'test'); -- 可能产生乱码8.2 函数调试技巧
调试复杂函数表达式的方法：
-- 方法1：分步验证 SET @temp1 = SUBSTRING_INDEX(email, '@', 1); SET @temp2 = SUBSTRING_INDEX(email, '@', -1); SELECT @temp1, @temp2; -- 方法2：使用SELECT调试 SELECT original_value, FUNCTION1(original_value) AS step1, FUNCTION2(FUNCTION1(original_value)) AS step2 FROM table LIMIT 5; -- 方法3：查看函数依赖 SELECT * FROM information_schema.routines WHERE ROUTINE_DEFINITION LIKE '%FUNCTION_NAME%';8.3 性能诊断工具
分析函数执行效率：
-- 查看函数执行计划 EXPLAIN SELECT FUNCTION_NAME(column) FROM table; -- 性能分析（MySQL 8.0+） SET profiling = 1; SELECT FUNCTION_NAME(column) FROM table; SHOW PROFILE; -- 查询函数执行统计 SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE '%FUNCTION_NAME%';9. 版本兼容性指南
9.1 MySQL 5.7 vs 8.0函数差异
升级时特别注意这些变化：
| 函数/特性 | MySQL 5.7支持情况 | MySQL 8.0改进 |
|---|---|---|
| 窗口函数 | 不支持 | 完全支持 |
| JSON函数 | 基础支持 | 新增JSON_TABLE等20+函数 |
| 公用表表达式(CTE) | 不支持 | 支持递归CTE |
| 默认字符集 | latin1 | utf8mb4 |
| GROUP BY处理 | 非标准行为 | 符合SQL标准 |
9.2 替代方案编写技巧
保持跨版本兼容的写法：
-- JSON处理兼容写法 SELECT /*!80000 JSON_EXTRACT(metadata, '$.price') */ /*!50700 metadata->'$.price' */ AS price FROM products; -- 日期计算兼容方案 SELECT IF(@@version LIKE '5.7%', DATE_ADD(NOW(), INTERVAL 1 MONTH), NOW() + INTERVAL 1 MONTH ) AS next_month;9.3 废弃函数迁移路径
这些函数已经或即将被废弃：
-- 旧版密码函数（改用SHA2） SET PASSWORD = PASSWORD('123456'); -- 5.7已废弃 CREATE USER 'test' IDENTIFIED WITH mysql_native_password BY 'password'; -- 8.0建议caching_sha2_password -- GROUP BY的隐式排序（8.0不再保证） SELECT * FROM table GROUP BY column; -- 5.7会按column排序，8.0不会10. 最佳实践总结
经过多年实战，我总结了这些MySQL函数使用原则：
- 简单优于复杂：能用基础函数组合实现就不用复杂函数
-- 不推荐 SELECT AES_DECRYPT(data, key) FROM secure_data; -- 推荐（如非必要） SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;- 显式优于隐式：明确指定格式和类型
-- 不推荐 SELECT STR_TO_DATE(date_str) FROM table; -- 推荐 SELECT STR_TO_DATE(date_str, '%Y-%m-%d %H:%i:%s') FROM table;- 可读性优先：复杂表达式适当换行和注释
SELECT -- 计算用户活跃度得分 LOG(COUNT(DISTINCT DATE(access_time))) * 10 AS activity_score, -- 计算最近购买间隔 TIMESTAMPDIFF(DAY, MAX(purchase_date), CURDATE()) AS days_since_last_purchase FROM user_behavior WHERE user_id = 123;- 安全第一：处理用户输入时要过滤
-- 不安全 SET @sql = CONCAT('SELECT * FROM ', @table_name); PREPARE stmt FROM @sql; -- 安全做法 SET @table_name = REPLACE(@table_name, '`', '``'); SET @sql = CONCAT('SELECT * FROM `', @table_name, '`'); PREPARE stmt FROM @sql;最后分享一个实用技巧：在MySQL客户端中，可以用\P命令设置分页显示，配合\G垂直显示结果，特别适合调试复杂函数：
-- 在mysql命令行中执行 \P less -S SELECT LONG_FUNCTION_CALL() AS result\G
