MySQL内置函数实战技巧与性能优化指南

1. MySQL内置函数全面解析作为一名长期与MySQL打交道的开发者我发现很多刚入门的同行对内置函数的理解仅限于表面用法。今天我将结合多年实战经验深度剖析MySQL最常用的四类内置函数带你掌握那些官方文档里没写的实用技巧。2. 日期函数实战指南2.1 基础日期时间操作最常用的日期函数莫过于获取当前时间的几个函数SELECT CURRENT_DATE(); -- 2023-08-15 SELECT CURRENT_TIME(); -- 14:30:22 SELECT NOW(); -- 2023-08-15 14:30:22.123456注意NOW()返回的是完整的datetime类型包含微秒精度而SYSDATE()会在每次调用时重新获取系统时间日期计算是业务系统的高频需求DATE_ADD和DATE_SUB的灵活用法值得掌握-- 三天后的这个时间 SELECT DATE_ADD(NOW(), INTERVAL 3 DAY); -- 两个月前的今天 SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH); -- 精确到分钟的时间计算 SELECT DATE_ADD(2023-08-15 14:30:00, INTERVAL 15 MINUTE);2.2 日期差值计算与类型转换DATEDIFF函数虽然简单但在实际业务中非常实用-- 计算两个日期相差天数 SELECT DATEDIFF(2023-12-31, 2023-01-01); -- 364 -- 结合DATE函数提取日期部分 SELECT DATEDIFF( DATE(2023-08-15 14:30:00), DATE(2023-08-10 09:15:00) ); -- 5日期类型转换的常见场景-- datetime转date SELECT DATE(NOW()); -- 2023-08-15 -- 字符串转datetime SELECT STR_TO_DATE(08/15/2023, %m/%d/%Y); -- 2023-08-15 00:00:002.3 日期函数性能优化建议避免在WHERE条件中对字段使用函数计算-- 反例无法使用索引 SELECT * FROM orders WHERE DATE_FORMAT(create_time,%Y-%m)2023-08; -- 正例可以利用索引 SELECT * FROM orders WHERE create_time BETWEEN 2023-08-01 00:00:00 AND 2023-08-31 23:59:59;处理时区问题-- 转换为UTC时间 SELECT CONVERT_TZ(NOW(), SYSTEM, UTC); -- 设置会话时区 SET time_zone 08:00;3. 字符串处理高级技巧3.1 字符串连接与截取CONCAT函数虽然基础但有几个实用变体-- 基本连接 SELECT CONCAT(Hello, , World); -- Hello World -- 处理NULL值 SELECT CONCAT(Hello, NULL, World); -- NULL SELECT CONCAT_WS(-, 2023, 08, 15); -- 2023-08-15 -- 安全截取字符串 SELECT SUBSTRING(MySQL, 2, 3); -- ySQ SELECT SUBSTRING(MySQL FROM 2 FOR 3); -- 同上经验SUBSTRING的position参数从1开始计数如果超过字符串长度返回空字符串3.2 字符串查找与替换INSTR和LOCATE的对比使用-- 查找子串位置 SELECT INSTR(foobarbar, bar); -- 4 SELECT LOCATE(bar, foobarbar, 5); -- 从第5位开始查找返回7 -- 安全替换 SELECT REPLACE(www.mysql.com, mysql, oracle); -- www.oracle.com SELECT REPLACE(Hello, NULL, Hi); -- 返回NULL3.3 字符串格式化与清理处理用户输入时的字符串清理-- 去除空格 SELECT TRIM( MySQL ); -- MySQL SELECT LTRIM( MySQL ); -- MySQL SELECT RTRIM( MySQL ); -- MySQL -- 格式化显示 SELECT LPAD(7, 3, 0); -- 007 SELECT RPAD(Hi, 5, !); -- Hi!!! SELECT FORMAT(1234567.89, 2); -- 1,234,567.894. 数值计算与随机数生成4.1 数学运算函数精确的数值计算在金融系统中尤为重要-- 基本运算 SELECT ABS(-3.14); -- 3.14 SELECT CEILING(3.14); -- 4 SELECT FLOOR(3.14); -- 3 SELECT ROUND(3.14159, 3); -- 3.142 -- 取模运算的特殊情况 SELECT MOD(10, 3); -- 1 SELECT 10 % 3; -- 同上 SELECT MOD(10, 0); -- NULL4.2 进制转换技巧处理不同进制数据的转换-- 常用进制转换 SELECT BIN(10); -- 1010 SELECT HEX(255); -- FF SELECT OCT(8); -- 10 -- 任意进制间转换 SELECT CONV(A, 16, 10); -- 10 SELECT CONV(1010, 2, 16); -- A4.3 随机数生成实战生成测试数据时随机数的妙用-- 基本随机数 SELECT RAND(); -- [0,1)之间的随机数 -- 生成1-100的随机整数 SELECT FLOOR(1 RAND() * 100); -- 可复现的随机序列相同种子产生相同序列 SELECT RAND(42); -- 每次返回相同的随机值提示在ORDER BY RAND()实现随机排序时大数据量表性能极差应考虑其他方案5. 系统函数与高级应用5.1 用户与数据库信息获取环境信息的常用函数-- 用户信息 SELECT USER(); -- rootlocalhost SELECT CURRENT_USER(); -- 同上 SELECT SYSTEM_USER(); -- 同上 -- 数据库信息 SELECT DATABASE(); -- 当前数据库名 SELECT VERSION(); -- 8.0.33 SELECT CONNECTION_ID(); -- 连接ID5.2 加密函数安全实践密码存储的正确姿势-- MD5加密已不推荐用于密码存储 SELECT MD5(password); -- 32位哈希值 -- MySQL特有的PASSWORD函数 SELECT PASSWORD(secret); -- *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 -- 更安全的做法使用SHA2 SELECT SHA2(password, 256); -- 64位哈希值重要生产环境密码存储应使用专门的密码哈希函数如bcrypt而非MD5等快速哈希5.3 条件逻辑函数处理NULL值的优雅方式-- 基本用法 SELECT IFNULL(NULL, default); -- default SELECT IFNULL(not null, default); -- not null -- 复杂条件判断 SELECT CASE WHEN score 90 THEN A WHEN score 80 THEN B ELSE C END AS grade FROM students; -- NULL安全比较 SELECT NULL NULL; -- 1 (TRUE) SELECT NULL NULL; -- NULL6. 函数性能优化与避坑指南6.1 索引使用禁忌避免在索引列上使用函数-- 反例索引失效 SELECT * FROM users WHERE YEAR(create_time) 2023; -- 正例可以使用索引 SELECT * FROM users WHERE create_time BETWEEN 2023-01-01 AND 2023-12-31;字符串函数与索引-- 反例左模糊查询无法使用索引 SELECT * FROM products WHERE name LIKE %phone%; -- 正例右模糊可以使用索引 SELECT * FROM products WHERE name LIKE iPhone%;6.2 隐式类型转换陷阱混合类型比较的常见问题-- 字符串与数字比较可能导致全表扫描 SELECT * FROM orders WHERE order_id 100; -- 比 order_id 100 性能更好如果order_id是整数类型 -- 日期比较的隐式转换 SELECT * FROM logs WHERE log_date BETWEEN 2023-08-01 AND 2023-08-31; -- 比 WHERE log_date BETWEEN 20230801 AND 20230831 更安全6.3 函数调用开销控制减少重复函数调用的技巧-- 反例NOW()每次调用都重新计算 SELECT id, NOW() AS current_time FROM large_table; -- 正例使用变量存储函数结果 SET current_time NOW(); SELECT id, current_time AS current_time FROM large_table;在实际项目中我经常看到开发者过度依赖数据库函数处理本该在应用层完成的逻辑。记住SQL函数虽强大但滥用会导致性能瓶颈。对于复杂的数据处理合理的做法是在数据库完成基础处理后将结果集交给应用层进一步处理。