1. 为什么你需要这份MySQL函数手册干了这么多年后端开发我越来越觉得数据库操作就像炒菜SQL是锅铲而函数就是那些决定菜最终风味的调料。你光会往锅里扔食材数据不行还得知道什么时候放盐字符串处理、什么时候加糖数值计算、什么时候来点料酒去腥日期转换。很多新手朋友一上来就死记硬背复杂的联表查询和索引优化却忽略了最基础也最常用的函数结果写出来的SQL又长又臭性能还差。这份汇总不是官方文档的简单罗列而是我这些年踩过坑、优化过无数慢查询后沉淀下来的“实战函数手册”。我会重点讲那些在业务开发中出场率超过80%的函数并且会告诉你在什么场景下该用哪个用的时候有什么坑要避开。比如你知道CONCAT和CONCAT_WS在处理可能为NULL的字段时结果天差地别吗又比如DATE_FORMAT和STR_TO_DATE这一对好兄弟用错了格式字符串能让你排查一整天。无论你是正在写增删改查的应届生还是需要优化报表SQL的资深工程师这份手册都能让你像老师傅一样信手拈来写出既高效又优雅的数据库语句。我们不止讲“怎么用”更会深挖“为什么这么用”以及“怎么用更好”。2. 字符串处理函数从拼接截取到模糊匹配字符串操作是SQL里最频繁的需求之一无论是用户名的展示、地址的拼接还是关键词的搜索都离不开它们。这块函数用好了能极大减少应用层的代码量。2.1 连接与填充让数据展示更规整CONCAT(str1, str2, ...)是最基础的字符串连接函数但它有个著名的“特性”任何参数为NULL则整个结果返回NULL。这个特性在早期版本坑了无数人。-- 假设last_name或first_name可能为NULL SELECT CONCAT(last_name, , first_name) AS full_name FROM users; -- 如果last_name为NULL那么full_name就是NULL而不是‘ 张三’所以在不确定字段是否为空时更安全的做法是使用CONCAT_WS(separator, str1, str2, ...)。WS是“With Separator”的缩写它用指定的分隔符连接字符串并且会忽略参数中的NULL值只连接非NULL的部分。-- 安全连接即使部分为NULL也能得到有意义的结果 SELECT CONCAT_WS( , last_name, first_name) AS full_name FROM users; -- 结果可能是‘张三’如果last_name为NULL而不是NULL另一个常用函数是LPAD(str, len, padstr)和RPAD(str, len, padstr)用于在字符串左侧或右侧填充指定字符直到达到指定长度。这在生成固定位数的订单号、工号时特别有用。-- 生成8位用户ID不足8位左侧补0 SELECT LPAD(id, 8, 0) AS user_sn FROM users; -- 例如 id123 - ‘00000123’实操心得在生成流水号时不要依赖应用层来格式化直接在SQL查询时用LPAD处理好。这样即使数据被导出到Excel等工具格式也是规整的。但要注意len参数应小于或等于数据库字段定义的长度否则会被截断。2.2 截取与替换精准操控文本内容SUBSTRING(str, pos, len)或SUBSTR(str, pos, len)用于从指定位置开始截取子串。这里有个易错点MySQL中字符串的起始位置是1不是0。如果你从0开始MySQL会理解为从字符串开头开始。-- 截取手机号后四位 SELECT SUBSTRING(phone_number, -4) AS tail_number FROM users; -- 正确负数表示从末尾倒数 SELECT SUBSTRING(phone_number, LENGTH(phone_number)-3) AS tail_number FROM users; -- 另一种写法REPLACE(str, from_str, to_str)会将字符串中所有匹配的子串替换掉。它是对整个字段进行全局替换区分大小写。-- 将标题中的旧产品名统一替换为新产品名 UPDATE articles SET title REPLACE(title, ‘OldProduct’, ‘NewProduct’);这里有个高级技巧REPLACE可以巧妙地用来统计某个子串出现的次数。因为替换前后字符串的长度差除以子串长度就是出现的次数。SELECT (LENGTH(content) - LENGTH(REPLACE(content, ‘BUG’, ‘’))) / LENGTH(‘BUG’) AS bug_count FROM feedback;TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)用于去除首尾空格或指定字符。默认是BOTH两端和空格。在用户输入清洗时非常关键。-- 清理用户输入的用户名两端的空格 UPDATE users SET username TRIM(username); -- 去除字符串两端特定的字符例如括号 SELECT TRIM(BOTH ‘()’ FROM ‘(Hello World)’) AS result; -- 结果Hello World2.3 大小写与模糊匹配搜索与比较的核心UPPER(str)和LOWER(str)用于转换大小写。在进行不区分大小写的搜索或比较时通常的做法是将双方都转为大写或小写。-- 不区分大小写的用户名查找假设collation是区分大小写的 SELECT * FROM users WHERE UPPER(username) UPPER(‘JohnDoe’);但请注意频繁对字段使用函数会使索引失效。如果username字段有索引UPPER(username)会让查询无法使用这个索引。更好的做法是在存储时就将数据统一为一种格式或者使用不区分大小写的校对规则collation。对于模糊匹配LIKE操作符是主力但它本身不是函数。与之相关的函数是LOCATE(substr, str)、POSITION(substr IN str)和INSTR(str, substr)它们返回子串第一次出现的位置找不到则返回0。这可以用来实现更复杂的模糊逻辑。-- 查找包含‘技术支持’且‘技术支持’出现在前100个字符内的工单 SELECT * FROM tickets WHERE LOCATE(‘技术支持’, content) BETWEEN 1 AND 100;3. 数值计算函数不只是加减乘除数值处理看似简单但在聚合、统计、财务计算中精度和函数选择直接关系到结果的正确性。3.1 四舍五入与取整精度控制的艺术ROUND(X, D)是最常用的四舍五入函数。D表示小数点后保留的位数可以为负数表示对整数部分进行四舍五入。SELECT ROUND(123.4567, 2); -- 123.46 SELECT ROUND(123.4567, -1); -- 120 (对个位四舍五入到十位)这里有个关键细节MySQL的ROUND函数采用“四舍六入五成双”的银行家舍入法吗答案是对于.5的情况它总是“向上舍入”即五入。例如ROUND(2.5)结果是3ROUND(-2.5)结果是-3。这与一些编程语言如Python的round的银行家舍入法不同需要特别注意。CEILING(X)或CEIL(X)返回不小于X的最小整数向上取整。FLOOR(X)返回不大于X的最大整数向下取整。它们在分页计算、按区间分组时非常有用。-- 计算总页数总记录数 / 每页大小向上取整 SELECT CEILING(COUNT(*) / 20.0) AS total_pages FROM products; -- 将金额按100元向下取整到最近的整百数用于分组统计 SELECT FLOOR(amount / 100) * 100 AS amount_range, COUNT(*) FROM orders GROUP BY amount_range;3.2 绝对值、取余与符号判断ABS(X)返回绝对值在计算差值或距离时常用。MOD(N, M)或%操作符用于取余数常用于循环、分组、判断奇偶。-- 将用户按ID奇偶分为两组用于AB测试等 SELECT *, CASE WHEN MOD(id, 2) 0 THEN ‘group_a’ ELSE ‘group_b’ END AS test_group FROM users;SIGN(X)返回参数的符号正数返回1负数返回-10返回0。这在数据分析和趋势判断中能简化逻辑。-- 判断本月与上月销售额的变化趋势 SELECT month, SIGN(sales - LAG(sales) OVER (ORDER BY month)) AS trend FROM monthly_report;3.3 数学运算与随机数POW(X, Y)或POWER(X, Y)计算X的Y次方。SQRT(X)计算平方根。这些函数在计算几何距离、增长率等场景下会用到。RAND()生成一个0到1.0之间的随机浮点数。它最常见的用途是随机排序或抽样。-- 随机抽取10条记录注意大数据集下性能很差 SELECT * FROM articles ORDER BY RAND() LIMIT 10;注意事项ORDER BY RAND()在大数据表上是性能杀手因为它需要为每一行生成一个随机值然后排序。对于百万级以上的表应避免使用。替代方案是如果ID连续无空洞可以用WHERE id (SELECT FLOOR(MAX(id) * RAND()) FROM table)来近似随机选取或者使用应用层预先计算好随机ID列表。4. 日期与时间函数处理一切时间难题日期和时间是业务逻辑中最容易出错的领域之一时区、格式、计算一个都不能马虎。4.1 获取当前时间与时间戳NOW()返回当前日期和时间‘YYYY-MM-DD HH:MM:SS’。CURDATE()只返回当前日期。CURTIME()只返回当前时间。这里要分清NOW()和SYSDATE()的区别NOW()返回语句开始执行的时间在整个SQL语句中值是固定的而SYSDATE()返回函数执行时的实时时间。在复制或基于时间点的恢复中这个差异可能导致数据不一致通常建议使用NOW()。UNIX_TIMESTAMP([date])将日期转换为Unix时间戳秒数。FROM_UNIXTIME(unix_timestamp, [format])将时间戳转换回日期格式。这是连接MySQL时间与程序语言时间如Java的DatePython的datetime的桥梁。-- 查询最近一小时内创建的订单 SELECT * FROM orders WHERE create_time FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) - 3600);4.2 日期抽取与格式化DATE_FORMAT(date, format)是日期格式化的瑞士军刀。format字符串非常灵活常用的有%Y四位年份%m两位月份01-12%d两位日期01-31%H24小时制小时00-23%i分钟00-59%s秒00-59%W星期名Sunday…Saturday-- 将日期格式化为‘2023年10月27日 星期五’的形式 SELECT DATE_FORMAT(NOW(), ‘%Y年%m月%d日 %W’) AS formatted_date;与之相反的是STR_TO_DATE(str, format)它将格式化字符串转换为日期类型。这两个函数的format必须严格匹配否则会返回NULL或错误结果。-- 将字符串转换为日期 SELECT STR_TO_DATE(‘27,10,2023’, ‘%d,%m,%Y’); -- 结果2023-10-27YEAR(date),MONTH(date),DAY(date),HOUR(time),MINUTE(time),SECOND(time)等函数用于快速抽取日期时间的特定部分在按年、月、日分组统计时必不可少。-- 统计每年的订单总量 SELECT YEAR(create_time) AS order_year, COUNT(*) AS total_orders FROM orders GROUP BY order_year;4.3 日期计算与差值DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)用于日期的加减。unit可以是DAY,MONTH,YEAR,HOUR,MINUTE,SECOND等。-- 计算7天后的日期 SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week; -- 计算3个月前的第一天 SELECT DATE_SUB(CURDATE(), INTERVAL 3 MONTH);DATEDIFF(date1, date2)返回两个日期相差的天数date1 - date2。TIMESTAMPDIFF(unit, datetime1, datetime2)更强大可以返回相差的秒、分、时、天、周、月、年等。-- 计算用户年龄精确到年 SELECT name, TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM users; -- 计算工单处理时长小时 SELECT ticket_id, TIMESTAMPDIFF(HOUR, create_time, resolve_time) AS process_hours FROM tickets;常见问题计算月份差时TIMESTAMPDIFF(MONTH, ‘2023-01-31’, ‘2023-02-01’)返回的是0因为它只计算了完整的月份间隔。如果需要更精确的月份逻辑如财务月可能需要更复杂的计算。5. 条件判断与流程控制函数这类函数让SQL具备了简单的逻辑处理能力能够根据数据的不同状态返回不同的值是实现复杂业务逻辑查询的关键。5.1 CASE WHENSQL中的IF-ELSECASE表达式有两种形式简单CASE和搜索CASE。简单CASE用于等值比较搜索CASE可以实现更复杂的条件判断。-- 搜索CASE根据分数划分等级 SELECT student_name, score, CASE WHEN score 90 THEN ‘A’ WHEN score 80 THEN ‘B’ WHEN score 70 THEN ‘C’ WHEN score 60 THEN ‘D’ ELSE ‘F’ END AS grade FROM exam_results; -- 简单CASE根据枚举值返回描述 SELECT status, CASE status WHEN 0 THEN ‘待支付’ WHEN 1 THEN ‘已支付’ WHEN 2 THEN ‘已发货’ WHEN 3 THEN ‘已完成’ ELSE ‘未知状态’ END AS status_desc FROM orders;CASE表达式可以用在SELECT、WHERE、ORDER BY、GROUP BY等几乎所有子句中非常灵活。在ORDER BY中实现自定义排序规则尤其好用。-- 按照自定义优先级排序紧急高中低 SELECT * FROM tasks ORDER BY CASE priority WHEN ‘紧急’ THEN 1 WHEN ‘高’ THEN 2 WHEN ‘中’ THEN 3 WHEN ‘低’ THEN 4 ELSE 5 END;5.2 IF与IFNULL处理空值与简单逻辑IF(expr1, expr2, expr3)是三目运算符如果expr1为真非0且非NULL返回expr2否则返回expr3。它适合简单的二选一逻辑。-- 判断库存状态 SELECT product_name, stock, IF(stock 0, ‘有货’, ‘缺货’) AS stock_status FROM products;IFNULL(expr1, expr2)专门用于处理NULL值。如果expr1不为NULL返回expr1否则返回expr2。这是防止NULL值破坏计算或拼接的常用工具。-- 计算订单总金额避免NULL加数值结果为NULL SELECT order_id, IFNULL(amount, 0) IFNULL(freight, 0) AS total_amount FROM orders;这里需要区分IFNULL、COALESCE和NULLIFCOALESCE(value1, value2, ...)返回参数列表中第一个非NULL的值。可以接收多个参数是IFNULL的增强版。NULLIF(expr1, expr2)如果expr1等于expr2则返回NULL否则返回expr1。常用于避免除零错误或标准化数据。-- 使用COALESCE设置多个备选显示值 SELECT username, COALESCE(nickname, real_name, ‘匿名用户’) AS display_name FROM users; -- 使用NULLIF避免除零错误 SELECT amount, quantity, amount / NULLIF(quantity, 0) AS avg_price FROM sales; -- 当quantity为0时avg_price为NULL而非报错6. 聚合函数与分组进阶聚合函数通常与GROUP BY子句一起使用但理解窗口函数后你可以进行更复杂的分组计算而不必真正折叠数据行。6.1 基础聚合COUNT, SUM, AVG, MAX, MIN这些是基石但有些细节值得深究COUNT(expr)统计非NULL值的行数。COUNT(*)统计所有行数包括NULL。COUNT(1)与COUNT(*)性能在MySQL中通常无差别。SUM(expr)求和忽略NULL。如果所有值都是NULL返回NULL。AVG(expr)求平均值忽略NULL。这意味着平均值是基于非NULL值计算的。MAX(expr)/MIN(expr)求最大/最小值忽略NULL。-- 统计有效评分非NULL的平均分 SELECT product_id, AVG(rating) AS avg_rating FROM reviews WHERE rating IS NOT NULL GROUP BY product_id; -- 与直接AVG的区别在于WHERE子句先过滤了NULL结果一样但逻辑更清晰。6.2 GROUP_CONCAT将分组值连接成字符串这是一个非常实用但常被忽略的函数。它可以将同一个分组内的多个字符串值用指定的分隔符连接成一个字符串。-- 查询每个标签下的所有文章ID SELECT tag_id, GROUP_CONCAT(article_id ORDER BY article_id SEPARATOR ‘, ‘) AS article_list FROM article_tags GROUP BY tag_id;关键参数DISTINCT在连接前对值去重。ORDER BY指定组内值的排序方式。SEPARATOR指定分隔符默认为逗号‘,’。注意事项GROUP_CONCAT的结果长度受系统变量group_concat_max_len限制默认是1024字节。如果结果可能很长需要先执行SET SESSION group_concat_max_len 1000000;来临时增大限制。6.3 窗口函数聚合的降维打击MySQL 8.0引入了窗口函数这彻底改变了复杂分析查询的写法。它允许你对一个结果集的“窗口”一组行进行计算而不需要将这些行合并为单一行。ROW_NUMBER()、RANK()、DENSE_RANK()是常用的排序窗口函数。ROW_NUMBER()为每一行分配一个唯一的连续序号1,2,3…即使值相同。RANK()排名相同值有相同排名但会跳过后续序号1,2,2,4…。DENSE_RANK()密集排名相同值有相同排名且不跳过序号1,2,2,3…。-- 对每个部门的员工按薪水排名 SELECT department_id, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank FROM employees;SUM/AVG/COUNT OVER用于计算累计、移动平均等。-- 计算每个员工的累计薪水部门内 SELECT employee_id, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary FROM employees;LAG(expr, offset)和LEAD(expr, offset)可以访问当前行之前或之后偏移量的行的数据非常适合计算环比、同比增长。-- 计算每月销售额的环比增长 SELECT month, sales, LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales, (sales - LAG(sales, 1) OVER (ORDER BY month)) / LAG(sales, 1) OVER (ORDER BY month) * 100 AS growth_rate FROM monthly_sales;7. 类型转换与信息函数这些函数帮助你处理数据类型转换和获取数据库、数据的元信息。7.1 类型转换CAST与CONVERTCAST(expr AS type)和CONVERT(expr, type)用于进行显式的类型转换。type可以是CHAR字符串、SIGNED有符号整数、UNSIGNED无符号整数、DECIMAL、DATE、DATETIME等。-- 将字符串转换为日期进行比较确保索引可能有效如果存储的是日期类型应避免此操作 SELECT * FROM logs WHERE CAST(log_date AS DATE) ‘2023-10-27’; -- 将浮点数转换为DECIMAL以进行精确计算 SELECT CAST(price AS DECIMAL(10,2)) AS fixed_price FROM products;性能警告在WHERE条件或JOIN的列上使用CAST或任何函数通常会导致该列上的索引失效引发全表扫描。设计表时应尽量让列以正确的类型存储。7.2 信息函数了解数据与连接LAST_INSERT_ID()返回最后一个AUTO_INCREMENT列自动生成的值。在插入数据后需要立即获取该ID时非常有用且它是基于当前连接的多用户环境下是安全的。VERSION()返回MySQL服务器版本字符串。DATABASE()返回当前数据库名。USER()返回当前MySQL用户名和主机名。-- 插入数据后获取自增ID INSERT INTO users (username) VALUES (‘new_user’); SELECT LAST_INSERT_ID(); -- 返回刚插入用户的idFOUND_ROWS()配合SQL_CALC_FOUND_ROWS使用可以在使用LIMIT时获取不带LIMIT条件时的总行数。但在MySQL 8.0.17以后官方建议使用COUNT(*) OVER()窗口函数作为替代因为前者可能带来性能开销。-- 传统方式可能被废弃 SELECT SQL_CALC_FOUND_ROWS * FROM products WHERE category‘electronics’ LIMIT 10; SELECT FOUND_ROWS() AS total_count; -- 推荐方式MySQL 8.0 SELECT *, COUNT(*) OVER() AS total_count FROM products WHERE category‘electronics’ LIMIT 10;8. 实战避坑与性能优化指南知道函数怎么用只是第一步在真实的生产环境中用对、用好才是关键。这里分享几个我踩过的坑和总结的经验。8.1 索引失效的常见陷阱这是最影响查询性能的问题之一。在索引列上使用函数几乎总会导致索引失效。-- 假设create_time字段有索引 -- 错误索引失效 SELECT * FROM orders WHERE DATE_FORMAT(create_time, ‘%Y-%m’) ‘2023-10’; -- 正确使用范围查询索引可能有效 SELECT * FROM orders WHERE create_time ‘2023-10-01’ AND create_time ‘2023-11-01’;同样对索引列进行运算或使用LIKE以通配符开头也会失效。-- 错误索引失效 SELECT * FROM products WHERE YEAR(create_date) 2023; SELECT * FROM users WHERE UPPER(username) ‘ADMIN’; SELECT * FROM articles WHERE title LIKE ‘%MySQL%’; -- 前导通配符导致索引失效解决方案设计优化存储衍生列。例如如果经常需要按YEAR(create_date)查询可以新增一个year列并在插入/更新时通过触发器或程序填充它并为此列建立索引。查询重写将函数作用从列转移到常量值上。如上面的日期范围查询例子。使用生成列MySQL 5.7/MariaDB 10.2创建虚拟列或存储列在其上建立索引。8.2 NULL值处理的统一原则NULL在SQL中代表“未知”它与任何值包括它自己的比较结果都是NULL而不是TRUE或FALSE。这会导致许多意想不到的结果。-- 查询年龄不是30岁的用户这会漏掉age为NULL的用户 SELECT * FROM users WHERE age ! 30; -- 正确的写法应该是 SELECT * FROM users WHERE age ! 30 OR age IS NULL; -- 或者使用NULL-safe比较运算符 SELECT * FROM users WHERE NOT age 30;在聚合函数中COUNT(column)会忽略NULL但COUNT(*)不会。在WHERE、HAVING、JOIN条件中要时刻考虑NULL的情况。我的建议是在业务逻辑允许的情况下尽量用默认值如0空字符串代替NULL可以简化很多查询逻辑。8.3 函数嵌套与可读性的平衡MySQL函数可以嵌套使用但过度嵌套会严重降低SQL的可读性和可维护性。-- 难以理解的嵌套 SELECT UPPER(LEFT(REPLACE(phone_number, ‘-’, ‘’), 3)) AS area_code FROM customers; -- 稍好一些使用CTECommon Table Expressions公用表表达式或子查询分步计算 WITH cleaned_phone AS ( SELECT id, REPLACE(phone_number, ‘-’, ‘’) AS clean_phone FROM customers ) SELECT id, UPPER(LEFT(clean_phone, 3)) AS area_code FROM cleaned_phone;对于特别复杂的格式化或计算逻辑考虑将其移到应用层代码中处理。SQL更擅长集合操作而编程语言更擅长复杂的流程控制和字符串处理。8.4 隐式类型转换的坑MySQL在执行比较时如果两边数据类型不一致会尝试进行隐式类型转换。这可能导致性能下降或结果错误。-- 假设user_id是字符串类型VARCHAR但存储的是数字 SELECT * FROM users WHERE user_id 123; -- MySQL会将所有user_id转换为数字再比较导致索引失效且可能出错‘123abc’会被转为123 -- 正确保持类型一致 SELECT * FROM users WHERE user_id ‘123’;最危险的是隐式转换可能导致意料之外的结果比如‘abc’ 0在MySQL中结果会是TRUE因为字符串’abc’在转换为数字时变成了0。养成在比较时保持类型一致的习惯能避免很多诡异的bug。说到底函数是工具工具的价值在于解决问题。不要为了用函数而用函数清晰的逻辑和可维护的代码永远比炫技的一行SQL更重要。当你面对一个复杂的数据处理需求时先拆解步骤想想用哪个函数最直接、对性能影响最小然后再下笔。多在测试环境跑跑看看执行计划你会对这些函数有更深的理解。