MySQL常用函数全解析：从字符串到日期的零基础实战指南

1. 内容整体设计与思路拆解1.1 为什么零基础要先搞定函数很多人学MySQL时都有这种感觉SELECT、WHERE、JOIN这些基础语句都会写但一遇到真实的数据处理需求就卡壳。比如要把用户手机号中间四位打上星号比如要按月份统计订单金额比如要把成绩划分成优良中差明明数据库里有这些数据就是不知道SQL该怎么写。这背后缺的往往就是MySQL常用函数这一环。函数是MySQL内置的一批现成工具用来做字符串处理、数值计算、日期转换、逻辑判断和统计汇总。它们不改变数据本身而是在查询过程中把数据加工成你想要的样子。你可以把它理解成一组“半成品调料”数据库把最常用、最考基本功的操作封装好了你只需要知道每个函数的名字、参数和返回值就能组合出非常强大的查询逻辑。对于零基础的同学我的建议是不要试图一次性记住所有函数。先掌握高频的20个左右已经能覆盖日常80%的需求。函数不是SQL的“进阶内容”而是从入门到实战中间最关键的桥梁。Excel里点几下鼠标能完成的清洗、统计工作在SQL里就要靠函数来落地。掌握了函数你才真正拥有了用SQL处理问题的能力。1.2 MySQL函数怎么分类更快上手MySQL官网把函数分成了很多类字符串函数、数值函数、日期时间函数、流程控制函数、聚合函数、类型转换函数、加密函数、信息查询函数等。对零基础来说不用面面俱到抓住四大类加一个大类就足够起步函数类别解决什么问题典型场景字符串函数文字的拼接、截取、替换、去空格姓名脱敏、字段清洗、格式统一数值函数数字的取整、四舍五入、取余金额汇总、分页计算、奇偶判断日期时间函数日期的格式化、加减、差值计算日报周报月报、年龄计算、到期提醒流程控制函数SQL里做逻辑判断和分支成绩分等级、状态码转中文聚合函数多行数据汇总成一行统计值统计总数、平均值、分组汇总这五类函数和你的日常需求是一一对应的名字前后有空格、需要脱敏、要拼接去找字符串函数算金额小计、四舍五入、判断奇偶去找数值函数按天按月统计、算工龄、判断到期时间去找日期函数成绩分等级、状态字段转中文去找流程控制函数统计总数、平均值、按班级汇总去找聚合函数。后面所有示例都会基于同一张学生成绩表你在自己的MySQL里直接跑一遍建表语句就行。我特意在设计表结构时埋了一些“坑”比如名字里带空格、字段类型选择等方便演示函数怎么解决真实问题。CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), class VARCHAR(20), score DECIMAL(5,1), birth_date DATE, created_at DATETIME ); INSERT INTO student (name, class, score, birth_date, created_at) VALUES (王小明, 三班, 87.5, 2010-05-12, NOW()), (李芳, 二班, 92.0, 2011-01-23, NOW()), (张伟, 一班, 76.5, 2009-11-08, NOW()), (赵静, 三班, 88.0, 2010-07-30, NOW()), (刘洋, 二班, 65.5, 2012-03-15, NOW()), (陈晨, 一班, 91.5, 2010-12-02, NOW());注意看第一条数据王小明名字前后我故意加了空格后面讲TRIM函数时你就知道这个坑有多常见了。2. 核心细节解析与实操要点字符串与数值函数2.1 字符串函数数据清洗的基础工具字符串函数是日常使用频率最高的一类因为业务数据里最脏的往往就是文本字段。前后有空格、大小写不统一、格式不规整这些问题几乎天天都能遇到。CONCAT与CONCAT_WS拼接字段CONCAT负责把多个字符串拼成一个字符串。比如我们要输出“王小明同学考了87.5分”这种一句话描述SELECT CONCAT(name, 同学考了, score, 分) AS info FROM student;这里有个非常容易踩的坑CONCAT只要有一个参数是NULL整个拼接结果就是NULL。假设某个学生的score字段是NULL这条语句的结果不会是“某某同学考了NULL分”而是直接一整条都是NULL。甚至你写CONCAT(name, class)只要name和class里有一个为空结果就没了。这是新手最容易忽略的地方。解决这个问题有两种思路。第一种是先IFNULL处理字段这属于流程控制函数后面会讲。第二种是用CONCAT_WS它的第一个参数是分隔符并且会自动跳过NULL值不会把NULL拼进去也不会在跳过NULL时多出一个分隔符SELECT CONCAT_WS(-, name, class, score) FROM student;王小明-三班-87.5 李芳-二班-92.0 ...SUBSTRING与LEFT/RIGHT截取字符串SUBSTRING用于按位置截取一段字符第二个参数是起始位置从1开始计数SELECT SUBSTRING(mysql常用函数, 1, 5); -- 结果: mysql SELECT SUBSTRING(name, 2, 2) FROM student; -- 从第2个字符开始截2个当字段是中文时SUBSTRING按字符截取不会把一个汉字劈成两半UTF-8编码下这个行为很友好。如果想从右侧截取用RIGHT左侧截取用LEFT。手机号脱敏就是这三个函数的经典组合SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) FROM user;138****5678LENGTH与CHAR_LENGTH统计长度这是零基础特别容易翻车的一个点。LENGTH统计的是字节数CHAR_LENGTH统计的是字符数。在UTF-8编码下一个汉字占3个字节所以同样四个字两个函数的结果天差地别SELECT LENGTH(mysql), CHAR_LENGTH(mysql); -- 5 和 5 SELECT LENGTH(常用函数), CHAR_LENGTH(常用函数); -- 12 和 4我见过不少人用LENGTH限制用户昵称长度结果明明只让填16个字符用户一输中文就提示超长就是因为LENGTH按字节算、中文要乘3。校验用户输入字符数时除非你明确知道自己在按字节算否则一律用CHAR_LENGTH。另外一个妙用是计算一个字符串里有多少个中文字符把LENGTH和CHAR_LENGTH的差除以2就能估算出来在数据清洗时可以用来判断字段是中文还是英文。TRIM / LTRIM / RTRIM清理首尾空格TRIM去掉字符串两端的空格LTRIM只去左边RTRIM只去右边。回想示例表里王小明名字前后的空格直接GROUP BY会把“王小明”和“ 王小明 ”当两个不同的人SELECT TRIM(name) AS clean_name FROM student WHERE id 1;实际项目中从Excel导入的数据经常带着各种空格做分组统计之前先TRIM是非常好的习惯。TRIM还支持去掉指定字符比如TRIM(BOTH x FROM xxxmysqlxxx)这个语法面试偶尔会问日常用得少。REPLACE替换字符REPLACE把字符串里某个子串替换成新值语法是REPLACE(str, from_str, to_str)SELECT REPLACE(mysql常用函数, mysql, MySQL);MySQL常用函数真实业务里常用它清洗数据中不规范的符号比如把用户输入里的中文逗号统一替换成英文逗号或者把老数据里废除的部门名称替换成新名称。它和SUBSTRING不一样不需要知道位置只要子串存在就能替换。LPAD / RPAD补位LPAD和RPAD在字符串左侧或右侧填充字符让字符串达到指定长度。比如希望id在显示时统一补成5位SELECT LPAD(id, 5, 0) FROM student;00001 00002 ...做订单号、流水号展示时这个函数非常好用。需要注意补位只作用于显示层面不要把MySQL里存储的整型id直接补位更新回去保持数据型字段的纯粹类型对后续索引和运算都有好处。LOCATE查找子串位置LOCATE返回子串第一次出现的位置找不到返回0SELECT LOCATE(sql, mysql常用函数); -- 3注意m1y2s3所以结果是3。配合SUBSTRING可以实现“按分隔符截取”比如从“姓名:王小明”中提取冒号后面的内容先LOCATE冒号的位置再SUBSTRING往右取。这是纯SQL实现简单文本解析的常用套路。2.2 数值函数让数字变成你想要的样子数值函数的数量不多但几乎每个带数字的报表都要用到。ROUND四舍五入ROUND(x, d)把x四舍五入到d位小数SELECT score, ROUND(score), ROUND(score, 1) FROM student;不给小数位数时默认四舍五入到整数也就是ROUND(score, 0)的简写。容易忽略的是第二个参数可以是负数表示对小数点左侧取整SELECT ROUND(1234.567, -2); -- 结果是 12001200金额按百元、千元为单位统计时这个特性很实用。比如财务要看“以百元为单位”的订单金额ROUND(amount, -2)直接得到结果。CEIL与FLOOR上下取整CEIL向上取整FLOOR向下取整SELECT CEIL(3.2), FLOOR(3.2); -- 4 和 3 SELECT CEIL(-1.2), FLOOR(-1.2); -- -1 和 -2负数时方向容易搞反记住口诀CEIL往正无穷方向走FLOOR往负无穷方向走。写分页逻辑时经常用CEIL计算总页数23条数据每页10条就是CEIL(23/10)3页。MOD取余MOD(x, y)返回x除以y的余数也支持x % y的写法SELECT MOD(7, 2); -- 1说明7是奇数取余最常见的用途是奇偶判断和分桶。比如把用户按id奇偶分成两组做AB测试SELECT id, name, IF(MOD(id, 2) 0, A组, B组) AS bucket FROM student;数据库分表时的常用方案也是用id对分表数取余MOD的语义在算法链路里是一致的学一遍能打通SQL和分库分表的概念。FORMAT千分位格式化FORMAT(x, d)把数字转成带千分位分隔符的字符串保留d位小数SELECT FORMAT(1234567.891, 2); -- 1,234,567.89注意这个函数返回的是字符串类型不能再直接参与数值运算。需要继续计算时要先通过CAST把它转回数字这个坑我在做财务报表时踩过格式化后的金额直接求和结果全是0。RAND随机数RAND()返回0到1之间的随机数。最常见的场景是从表里随机抽取一条数据SELECT * FROM student ORDER BY RAND() LIMIT 1;不过要提醒一句ORDER BY RAND()在数据量大的时候性能很差因为它需要给每一行生成随机数再排序。几万条数据随便用几百万条就别用了数据量大的场景应该想办法生成随机id区间再取数。POWER与SQRT幂和平方根这两个函数在数据处理中偶尔用到。POWER(base, exp)求幂SQRT求平方根SELECT POWER(2, 10), SQRT(16); -- 1024 和 4数值函数整体坑比较少但有个容易被忽视的问题浮点数精度。FLOAT/DOUBLE在计算时本身有误差比如ROUND(2.675, 2)在某些版本下可能是2.67而不是2.68这是二进制浮点数表示不精确导致的。对金额字段表结构要尽量用DECIMAL而不是FLOAT/DOUBLE这点在涉及钱的系统里是硬性要求。3. 实操过程与核心环节实现日期、流程控制与聚合统计3.1 日期时间函数报表统计离不开的时间轴日期函数是报表和运营分析的核心几乎每个看板都要按天、按月汇总数据没有日期函数寸步难行。NOW与CURRENT_DATE取当前时间NOW()返回当前的日期时间CURDATE()返回当前日期CURTIME()返回当前时间SELECT NOW(), CURDATE(), CURTIME();有个细节值得说NOW()在一条SQL语句执行期间返回的是同一个时间点而SYSDATE()是执行到那一刻的实时时间。在长事务、大批量更新时两者可能出现结果不一致导致逻辑判断上的差异。日常开发用NOW()就够了。我们示例表里的created_at用的就是NOW()所以几条数据的创建时间相同。DATE_FORMAT日期格式化DATE_FORMAT是日期函数里最重要的一个它把日期时间转成你想要的格式。格式符很多核心记住这6个就够了格式符含义示例%Y四位年份2024%m两位月份03%d两位日期15%H24小时制小时14%i分钟05%s秒09SELECT DATE_FORMAT(created_at, %Y-%m-%d %H:%i:%s) AS formatted_time FROM student; SELECT DATE_FORMAT(birth_date, %Y/%m/%d) FROM student;真实业务里你想统计“每天新增了多少用户”就按天格式化后再分组SELECT DATE_FORMAT(created_at, %Y-%m-%d) AS day, COUNT(*) FROM user GROUP BY DATE_FORMAT(created_at, %Y-%m-%d);日活、日新增这类指标立刻就能查出来。唯一要留意的是WHERE条件里用DATE_FORMAT包裹字段会导致该字段上的索引失效。大数据量下应该改写成范围查询比如created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00效果一样索引却能用上。DATEDIFF与TIMESTAMPDIFF计算日期差DATEDIFF(date1, date2)返回date1比date2多多少天第一个参数是被减数SELECT name, DATEDIFF(CURDATE(), birth_date) / 365 AS rough_age FROM student;DATEDIFF只精确到天。想按月份、年份、小时计算差异要用TIMESTAMPDIFF。它的语法有点反直觉第一个参数是单位后面是两个日期时间取的是“后一个减前一个”SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM student; SELECT TIMESTAMPDIFF(MONTH, birth_date, CURDATE()) AS age_in_months FROM student;我第一次用的时候顺序老搞反后来记住一个口头禅“结束时间放后面开始时间放前面结果是后减前”。这样就不会再错。TIMESTAMPDIFF支持的单位很多YEAR、MONTH、DAY、HOUR、MINUTE、SECOND都能用。DATE_ADD与DATE_SUB日期加减DATE_ADD在日期上加一个时间间隔DATE_SUB则相反SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY); -- 一周后 SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH); -- 一个月前这个函数最经典的案例是查询最近7天的数据。关键技巧是把函数放在NOW()一侧而不是包住字段这样索引还是能正常使用SELECT * FROM student WHERE created_at DATE_SUB(NOW(), INTERVAL 7 DAY);INTERVAL后面的单位要用单引号括起来这也是新手常见报错点。支持的复合写法还有INTERVAL 1-1 YEAR_MONTH面试偶尔会考。YEAR / MONTH / DAY提取日期部分这三个函数分别用来提取年、月、日SELECT YEAR(birth_date), MONTH(birth_date), DAY(birth_date) FROM student;和GROUP BY联用就是典型的按月统计。注意GROUP BY里写了表达式SELECT里也要写同样的表达式不要试图用别名代替部分版本会直接报错。STR_TO_DATE字符串转日期STR_TO_DATE和DATE_FORMAT相反它把字符串解析成日期格式符用法一致SELECT STR_TO_DATE(2024-03-15 10:30:00, %Y-%m-%d %H:%i:%s);批量导入外部数据时日期经常是文本格式。用STR_TO_DATE转成日期类型后再入表能避免后续比较时的类型混乱。比如CSV里写的是“2024/03/15”直接存字符串和日期字段比较会触发隐式转换容易出问题。DAYOFWEEK与WEEKDAY星期几这两个函数都返回星期信息但基准不同DAYOFWEEK返回1星期天到7星期六WEEKDAY返回0星期一到6星期天。用哪个取决于你业务里把周一当一周的第一天还是周日当第一天。报表里的周统计经常用到别把两者记混。3.2 流程控制函数SQL里的逻辑分支流程控制函数让SQL不再是简单的数据搬运而是能做条件判断。这是零基础学会后“爽感”最强的一类函数因为很多复杂的业务规则都能用SQL直接表达。IF简单的二选一IF(expr, true_value, false_value)是最简单的条件判断SELECT name, score, IF(score 60, 及格, 不及格) AS pass_status FROM student;expr可以是任意能判断真假的表达式比如IF(score IS NULL, 缺考, 有成绩)。不过IF适合简单二选一分支一多就容易嵌套得难看这时应考虑CASE WHEN。IFNULL与COALESCE处理NULLIFNULL(expr1, expr2)在expr1为NULL时返回expr2否则返回expr1SELECT name, IFNULL(score, 0) AS safe_score FROM student;COALESCE更灵活可以给多个参数返回第一个非NULL值SELECT COALESCE(NULL, NULL, score, 0) FROM student;NULL在SQL里很麻烦任何数和NULL做四则运算结果都是NULL。比如score字段允许NULL那么score 10的结果很可能变成NULL而不是预期的“多10分”。算分、算金额、算累计前先IFNULL兜底是保底的好习惯。CASE WHEN多分支判断CASE WHEN是SQL里最强大的逻辑工具相当于其他语言里的if-else if-else强烈建议零基础重点掌握。给成绩划分等级的典型写法SELECT name, score, CASE WHEN score 90 THEN 优秀 WHEN score 80 THEN 良好 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM student;这里有个顺序陷阱CASE WHEN从上往下匹配遇到第一个满足条件的分支就返回不再往后判断。所以要把最严格的条件写在前面。一旦写反比如先写80再写9090分以上的会先命中“80”而变成“良好”结果就错了。CASE WHEN还支持两种写法。上面是搜索CASE还有一种是简单CASE直接比较一个字段的值SELECT id, CASE class WHEN 一班 THEN 一班同学 WHEN 二班 THEN 二班同学 ELSE 其他 END AS class_desc FROM student;CASE WHEN配合聚合函数做行列转换是报表里的高频操作。比如统计每个班各成绩段的人数一条SQL搞定SELECT class, SUM(CASE WHEN score 90 THEN 1 ELSE 0 END) AS excellent_cnt, SUM(CASE WHEN score 60 AND score 90 THEN 1 ELSE 0 END) AS pass_cnt, SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) AS fail_cnt FROM student GROUP BY class;这个技巧能把“长表”的明细数据压成“宽表”的统计结果面试基本必考有机会一定要亲手跑一遍。3.3 聚合函数从明细数据到统计结果聚合函数是SQL统计能力的根基它们把一个分组内的多行数据合并成一个值。前面讲的所有函数都是“一行进一行出”聚合函数则是“多行进一行出”。COUNT统计行数COUNT()、COUNT(1)、COUNT(字段)三者的差异是面试官最喜欢问的问题之一。简单来说COUNT()统计结果集的行数包含NULLCOUNT(1)效果等价于COUNT(*)两者在MySQL 8.0里性能没有明显差异COUNT(字段)只统计该字段不为NULL的行数。你可以在示例表里插入一条score为NULL的记录再对比SELECT COUNT(*), COUNT(score), COUNT(class) FROM student;假设总共有6条数据其中一条score为NULL那么COUNT(*)是6COUNT(score)可能只有5。如果你想统计“有多少条有效成绩”用COUNT(score)反而更准确。COUNT(DISTINCT 字段)可以去重统计比如统计学生来自几个班级SELECT COUNT(DISTINCT class) FROM student结果是3。SUM / AVG / MAX / MIN求和平均极值这四个函数语法一致SELECT COUNT(*) AS total, SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM student;需要注意SUM和AVG会自动忽略NULL值但“全组都是NULL”时AVG直接返回NULL所以再包一层IFNULL更稳妥。MAX和MIN不仅适用于数字对日期和字符串也有效比如查班级里出生日期的最大最小值、姓名字典序最靠前的记录。GROUP_CONCAT把多行拼到一行GROUP_CONCAT能把分组内的多个值拼成一个字符串。比如看每个班都有哪些学生SELECT class, GROUP_CONCAT(name) AS students FROM student GROUP BY class;它支持排序和自定义分隔符SELECT class, GROUP_CONCAT(name ORDER BY score DESC SEPARATOR 、) AS students FROM student GROUP BY class;这里有个容易踩的坑GROUP_CONCAT默认最大长度是1024字节超过部分会被静默截断结果看起来就像“少了几个名字”。出现这种情况可以临时调大SET SESSION group_concat_max_len 1048576;注意这是session级别重启后失效生产环境考虑写在配置文件里。GROUP BY与HAVING聚合函数几乎总是和GROUP BY一起出现用来按组汇总。前面班级平均分就是典型例子。这里要明确WHERE和HAVING的分工WHERE在分组前过滤原始行HAVING在分组后过滤聚合结果。比如“只统计平均分高于80的班级”SELECT class, AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score 80;SQL的逻辑执行顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。这就是为什么WHERE里不能用SELECT里定义的别名而HAVING可以用别名。很多新手在WHERE里写别名结果报错想明白这个顺序就通了。4. 常见问题与排查技巧实录4.1 新手最容易踩的六个坑我平时带新人时上面这些函数的坑反复出现。整理成一张速查表每个都对应一条具体经验典型表现根本原因正确做法中文长度总是“算错”LENGTH按字节UTF-8下一个汉字占3字节统计字符用CHAR_LENGTHWHERE里用SELECT别名报错SQL执行时WHERE早于SELECT生成别名用完整表达式或HAVINGGROUP_CONCAT结果少数据默认长度限制1024字节被截断调大group_concat_max_len计算字段有时结果变NULL字段本身为NULLNULL参与运算结果为NULL算前IFNULL(字段, 0)DATE_FORMAT包索引字段后查询明显变慢函数包裹字段导致索引失效改用范围条件查询日期字段和字符串比较结果诡异隐式类型转换或格式不一致统一用STR_TO_DATE转成日期这六个坑我基本都在真实项目里遇到过。尤其第一个和第三个几乎没人第一次就能想到是编码和长度限制的问题。查了半小时文档才发现是这种小细节很影响心情。4.2 函数使用中的性能与规范建议函数用得顺手以后很容易出现“什么都想用函数包一层”的习惯。这里必须泼一盆冷水函数不是不能用在WHERE条件里而是别用在索引字段上。最常见的反面例子是WHERE DATE(created_at) CURDATE()。它会让created_at上的索引失效触发全表扫描。数据量小无所谓数据量一旦过千万查询可能从毫秒级变成秒级甚至更慢。改成下面这种范围查询效果完全一样索引却能正常使用SELECT * FROM student WHERE created_at CURDATE() AND created_at DATE_ADD(CURDATE(), INTERVAL 1 DAY);另外ORDER BY RAND()在大表上会全表扫描加排序成本极高字符串函数嵌套太深也会让SQL可读性变差。建议养成一个习惯写完SQL后用EXPLAIN确认一下有没有用到索引。EXPLAIN结果不会骗人type列是ALL就代表全表扫描这种情况在核心查询里要尽量避免。还有一点关于版本差异。MySQL 8.0之后有些旧函数被废弃比如PASSWORD()有些函数的排序和字符集行为也变了。网上很多教程写的是5.7时代的结论如果你用的是8.0最好以官方文档为准。新项目我强烈建议直接上MySQL 8.0以上版本字符集统一utf8mb4函数行为也更规范还能用上窗口函数这些新特性。最后再分享一个我实际带人练函数的经验。学完这一篇别急着背语法直接拿手头的业务数据做三个练习把用户手机号中间四位打码按月份统计订单金额保留两位小数把订单金额按区间分成“低中高”三档并统计每档订单数。这三个练习分别对应字符串、日期、数值、流程控制、聚合五大类函数能独立写出来常用函数这一关就算真正过了。我自己带过的同事里很少有人能一遍全对但凡是亲手跑完这三道题的后面写报表SQL基本都不再需要人带。