1. 为什么说函数是零基础学MySQL最该优先掌握的内容刚开始学MySQL的时候很多人会有一种错觉把增删改查INSERT、SELECT、UPDATE、DELETE背熟了就算会数据库了。真到了实际项目里你会发现业务需求永远不可能只是“把表里所有数据查出来”这么简单。举几个最常见的场景用户表里存的手机号是182xxxx1234前端页面却要显示成182****1234订单表里记录了下单时间老板要的是“这个月每天成交了多少单”员工表里有一列是性别存的是0和1报表却要展示成“男”和“女”多张表关联统计时要按部门、按月份分组汇总。这些需求靠原生SQL语句很吃力但用函数处理几行就能搞定。我接触过的很多零基础学员学到函数这一章就开始划水理由是“感觉这东西就是一堆死记硬背的写法”。这个看法大错特错。函数不是让人背的它的核心价值在于“把数据库里已经存在的数据经过加工之后直接变成你想要的格式”。换句话说函数是数据和结果之间的一座桥。如果你不会用这座桥就只能把数据原样拿出来再写到程序里用Java、Python、PHP去处理。一次两次没问题一旦数据量大、业务复杂程序端处理的效率和可维护性都远不如直接在SQL里完成。所以这篇文章我打算用一套完整思路从“函数到底是什么”“为什么能提效”讲起然后按类别拆解MySQL里最常用的几组函数配合真实业务场景和SQL示例最后再把容易踩的坑整理一遍。内容不追求函数大全式的罗列只挑日常开发中真正高频、真正能解决问题的那些。2. 先搞清楚函数的本质和工作原理在MySQL里函数可以理解成一个“黑盒子加工厂”。你给它输入一个或几个值它按照预设的规则处理然后返回一个结果。这个结果可以是一个数值、一段字符串、一个日期也可以是一个判断后的布尔值甚至可以是一个完整的表格。从使用方式上分MySQL函数主要有两大类单行函数和聚合函数。这里的“行数”是理解函数的一个关键分水岭。单行函数说白了就是“进来一行出去一行”。它对每一行数据分别做处理每一行都会得到一个独立的输出结果。比如你有一张100行的用户表用字符串函数把每个用户的手机号打码那最终返回的还是100行只是每一行的手机号都变了。聚合函数正好相反它是“多行进来、一行出去”。比如用COUNT统计用户总数不管表里有100行还是10000行最终只返回一个统计值。理解这一点特别重要很多新手搞不明白“为什么我的SELECT里既写了普通字段又写了COUNT结果就报错”其实就是没搞清单行和聚合的处理逻辑差异。从数据类型的角度函数又可以分为字符串函数、数值函数、日期时间函数、流程控制函数、加密函数、类型转换函数等。不同类别的函数服务于不同类型的数据加工需求。就像家里厨房里的刀具切片刀切菜、斩骨刀剁骨头各有分工你不能指望一把刀解决所有问题但你也确实不需要一次买齐几十把刀常用的就那么几把。我个人的建议是零基础阶段不要抱着文档从头到尾把函数背一遍太枯燥而且背完就忘。正确路线是先搞清楚“有哪些类型的函数”“每个类型里哪几个最常用”“它们大概解决什么问题”然后在使用中逐渐熟悉参数细节。用到哪个查哪个比死记硬背高效得多。3. 零基础快速上手高频常用函数分类拆解这一节是全文的重头戏我按类别把日常开发中使用频率最高的函数挑出来每一个都讲清楚“是什么”“怎么用”“实际解决什么问题”。建议你跟着示例自己动手敲一遍光看一遍记不住手过一遍才是自己的。3.1 字符串函数处理文本数据的基本功字符串函数是使用频率最高的一类函数因为业务系统里几乎所有的描述性信息都是文本。姓名、地址、手机号、邮箱、商品名称、备注全都离不开字符串处理。CONCAT字符串拼接把多个字符串拼成一个字符串常用于把姓和名拼成全名、把省市区拼成完整地址。基本语法是CONCAT(str1, str2, ...)。注意一个容易踩的坑如果任何一个参数为NULLCONCAT整体返回NULL。比如CONCAT(张, NULL)的结果不是张而是NULL。真遇到这种需求建议用CONCAT_WS它的语法是CONCAT_WS(separator, str1, str2, ...)第一个参数是指定的分隔符并且它会自动忽略NULL值。比如CONCAT_WS(-, 2024, 01, 15)返回2024-01-15中间那个NULL会被直接跳过。SUBSTRING截取字符串SUBSTRING(str, pos)表示从第pos位开始截取到最后SUBSTRING(str, pos, len)表示从第pos位开始截取len个字符。实际项目中经常用它截取身份证号里的出生日期、截取订单号的某一段作为业务标识。举个例子SUBSTRING(MySQL运维实战, 6, 2)的结果是运维。REPLACE替换字符串把字符串中的指定子串替换成新内容。比如手机号打码、把文本里的敏感词替换掉。REPLACE(13812345678, 138, **)返回12345678。如果你想做更模糊的打码效果比如把中间四位隐藏可以这样写CONCAT(LEFT(phone, 3), , RIGHT(phone, 4))其中LEFT取左边3位RIGHT取右边4位。UPPER和LOWER大小写转换这两个函数很简单一个转大写一个转小写。在数据比对时特别有用比如用户输入的验证码不区分大小写可以用UPPER( code ) UPPER(aBcD)来统一转换成大写再比对。LENGTH和CHAR_LENGTH计算长度LENGTH返回字符串的字节长度CHAR_LENGTH返回字符串的字符长度。这两个函数的区别在中文场景下非常重要。在UTF-8编码下一个汉字占3个字节所以LENGTH(你好)返回6而CHAR_LENGTH(你好)返回2。如果后端程序里限定了用户输入的字符个数数据库这边最好用CHAR_LENGTH否则很容易出现“明明只写了两个汉字数据库却说长度是6”这种疑惑。TRIM去掉首尾空格用户注册时不小心在邮箱前后多打了空格导致登录匹配不上是特别常见的线上问题。TRIM函数就是干这个用的。TRIM( abc )返回abc。还有LTRIM和RTRIM分别只去掉左边和右边的空格。这里提醒一句如果数据里中间位置也有空格比如用户输入“张 三”TRIM是处理不了的这种情况得用REPLACE把空格替换掉。3.2 数值函数让计算在数据库里直接完成数值函数用于对数值类型的数据做数学计算、取整、取绝对值等操作。很多时候业务需要的数据本身就是数字计算的结果直接在SQL里算完比捞出来到程序里再算要快得多。ROUND四舍五入ROUND(x, d)表示对x四舍五入并保留d位小数d不写默认保留0位。比如ROUND(3.14159, 2)返回3.14。电商系统里计算商品折扣价、统计报表里的百分比指标都经常用到。CEIL和FLOOR向上取整和向下取整CEIL(4.2)返回5FLOOR(4.8)返回4。这两个函数在分页计算里很有用。比如每页显示10条数据总共95条总页数就是CEIL(95 / 10) 10。用程序算当然也能算但直接写在SQL里代码会精简不少。ABS绝对值ABS(-5)返回5。统计账户金额变动差异、计算距离差值时很常用。MOD取余数MOD(10, 3)返回1。取余运算可以用来对数据进行分组。比如想把用户按照ID拆分成10个分片可以用MOD(id, 10)。做那种按天轮询、按用户ID哈希分表的逻辑时这个函数很顺手。3.3 日期时间函数统计报表最离不开的一类日期时间函数在业务系统里几乎无处不在。只要涉及“时间范围”“时间差”“按月按天统计”就一定会用到它们。零基础阶段掌握下面这几个基本能覆盖八九成需求。NOW和SYSDATE获取当前时间NOW()返回当前日期和时间格式是YYYY-MM-DD HH:MM:SS。插入数据时经常用它填充创建时间字段。SYSDATE和NOW看起来差不多实际上有个细微区别NOW是语句开始执行时的时间SYSDATE是函数执行瞬间的时间。在一条复杂的SQL里如果执行时间很长这两个函数返回的时间可能会不一样。日常开发用NOW就足够了。CURDATE和CURTIME获取当前日期/当前时间CURDATE()返回YYYY-MM-DD格式的当前日期CURTIME()返回HH:MM:SS格式的当前时间。按天生成业务编号、查询当天新增数据时很常用。DATE_FORMAT格式化日期这是统计报表里出镜率最高的日期函数之一。DATE_FORMAT(date, format)可以把日期按照指定格式输出。比如DATE_FORMAT(2024-01-15 14:30:00, %Y-%m-%d)返回2024-01-15DATE_FORMAT(2024-01-15 14:30:00, %H:%i)返回14:30。常见格式化符号如下表符号含义示例%Y四位年份2024%y两位年份24%m月份01-1201%d日01-3115%H小时00-2314%i分钟00-5930%s秒00-5945%W星期名Monday%w星期几0周日1统计每天的订单量用GROUP BY DATE_FORMAT(order_time, %Y-%m-%d)就能轻松实现不用在程序里循环处理。DATEDIFF和TIMESTAMPDIFF计算时间差DATEDIFF(date1, date2)返回两个日期相差的天数date1减date2。比如DATEDIFF(2024-01-20, 2024-01-15)返回5。TIMESTAMPDIFF更灵活可以指定单位。TIMESTAMPDIFF(MINUTE, start_time, end_time)返回两个时间点相差的分钟数单位还可以是SECOND、HOUR、DAY、MONTH、YEAR。计算用户会员还剩多少天到期、计算任务执行耗时都用得上。DATE_ADD和DATE_SUB日期加减在某个日期基础上加上或减去一段时间。DATE_ADD(2024-01-15, INTERVAL 7 DAY)返回2024-01-22。INTERVAL后面的单位除了DAY还可以是HOUR、MINUTE、MONTH、YEAR等。比如查询最近7天的订单条件可以写成WHERE order_time DATE_SUB(NOW(), INTERVAL 7 DAY)。这种写法看着比直接写死日期更专业也更好维护。3.4 流程控制函数在SQL里写“如果……就……”刚开始学函数的人往往意识不到SQL里还可以做条件判断。其实MySQL提供了好几种类似编程语言中if-else的函数可以在查询结果里动态生成字段值。这是减少程序端代码量的利器。IF函数IF(expr, v1, v2)如果expr为真返回v1否则返回v2。最简单的例子SELECT IF(1 0, yes, no)返回yes。业务里很常见的用法是把字段值映射成中文。比如学生表里sex字段存的值是1和2查询时写成SELECT name, IF(sex 1, 男, 女) AS gender FROM student结果集里直接就出现“男”“女”不用再在Java代码里做二次判断。IFNULL函数IFNULL(expr1, expr2)如果expr1不为NULL返回expr1否则返回expr2。这个函数的应用场景非常广。比如用户表里nickname允许为空但前端页面不想显示空白可以写成SELECT IFNULL(nickname, 未设置昵称) FROM user。再比如统计商品实际销量时如果某些商品没有卖出过SUM(sales_count)的结果是NULL用IFNULL(SUM(sales_count), 0)就可以把NULL转成0。CASE WHEN表达式CASE WHEN是SQL里最灵活的条件表达式比IF更适合多分支判断。语法是CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END举一个实际例子。电商订单表里status字段存的是数字1代表待支付、2代表已支付、3代表已发货、4代表已完成、5代表已取消。查询时要直接显示状态名称SELECT order_no, CASE WHEN status 1 THEN 待支付 WHEN status 2 THEN 已支付 WHEN status 3 THEN 已发货 WHEN status 4 THEN 已完成 WHEN status 5 THEN 已取消 ELSE 未知状态 END AS status_name FROM orders;这种方式比在程序里维护一个状态字典再逐行翻译要直观得多而且数据库迁移、报表导出时导出的数据直接就是可读文本。3.5 聚合函数从“每一行”到“整体统计”聚合函数是SQL里极具特色的一类函数它们不逐行处理而是把多行数据压缩成一个结果。这种“压缩能力”正是做统计报表的核心。COUNT统计行数COUNT()统计表中所有行数COUNT(column)统计该列非NULL值的个数COUNT(DISTINCT column)统计该列去重后的非NULL值个数。这个区别必须牢记。很多人统计用户数时直接写COUNT(phone)如果phone列存在NULL值结果就会比实际行数少。更安全的是用COUNT()或者COUNT(主键)。SUM求和SUM(column)对某一列的数值求和。需要注意如果列里有NULL值SUM会自动忽略不会报错。但如果整列都是NULLSUM的结果是NULL不是0所以业务里常常写成IFNULL(SUM(amount), 0)。AVG求平均值AVG(column)返回某列的平均值。同样地SUM为NULL的极端情况对AVG也适用。统计订单平均金额时如果没有订单AVG返回NULL展示时就不好看用IFNULL包装一下更稳妥。MAX和MIN最大值和最小值MAX(column)和MIN(column)分别取某列的最大值和最小值。它们对文本和日期类型也能生效比如查询最早下单时间可以用MIN(order_time)查询销量最高的单品可以用MAX(sales_count)。聚合函数与GROUP BY的组合是SQL统计的核心。举个例子统计每个城市的用户数。SELECT city, COUNT(*) AS user_count FROM user GROUP BY city;如果还要过滤某些城市比如只保留用户数大于100的城市就得用HAVING而不是WHERE。这里有一个非常重要的执行顺序问题WHERE是在分组之前过滤原始行HAVING是在分组之后再过滤聚合结果。新手写SQL时最容易在这个地方翻车。4. 函数实战用一张订单表串联常用操作讲完单个函数的用法接下来我用一个完整的业务场景把这些函数串起来。这个实战案例非常贴近真实开发建议你照着在本地MySQL里演练一遍感受函数组合使用的威力。假设有一张在线教育平台的订单表order_info结构如下CREATE TABLE order_info ( id INT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(50), phone VARCHAR(20), course_name VARCHAR(100), order_amount DECIMAL(10, 2), order_status TINYINT COMMENT 1-待支付 2-已支付 3-已取消, pay_time DATETIME );插入几条测试数据INSERT INTO order_info (student_name, phone, course_name, order_amount, order_status, pay_time) VALUES (张三, 13812340001, MySQL零基础入门, 99.00, 2, 2024-01-05 10:23:00), (李四, 13912340002, Java全栈开发, 399.00, 1, NULL), (王五, 13712340003, MySQL进阶实战, 199.00, 3, NULL), (张三, 13812340001, Python数据分析, 299.00, 2, 2024-01-12 14:45:00), (赵六, 13612340004, MySQL零基础入门, 99.00, 2, 2024-01-15 09:10:00), (孙七, 13512340005, Spring Boot实战, 259.00, 2, 2024-01-18 20:30:00);现在业务方提了几个需求我们逐个分析。需求一导出订单列表手机号需要打码显示这正好用上CONCAT、LEFT、RIGHT三个函数。SELECT student_name, CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone, course_name, order_amount, order_status FROM order_info;执行结果中13812340001会被显示成138****0001。这个功能如果放在程序里做你得逐行读出来再逐行替换数据库直接处理省去了不必要的网络传输和内存占用。需求二统计已支付订单的总金额和平均金额这里需要先用WHERE过滤掉非已支付订单再做聚合。SELECT SUM(order_amount) AS total_amount, ROUND(AVG(order_amount), 2) AS avg_amount, COUNT(*) AS paid_order_count FROM order_info WHERE order_status 2;ROUND(AVG(order_amount), 2)保证平均值只保留两位小数。这里还能明显看出聚合函数的效果原始表里多行数据最终只返回一行统计结果。需求三按月统计已支付订单的金额分布用DATE_FORMAT把pay_time格式化到月份再分组聚合。SELECT DATE_FORMAT(pay_time, %Y-%m) AS pay_month, SUM(order_amount) AS month_amount, COUNT(*) AS order_count FROM order_info WHERE order_status 2 AND pay_time IS NOT NULL GROUP BY DATE_FORMAT(pay_time, %Y-%m) ORDER BY pay_month;结果大概是2024-01这一个月份的数据。这里有个细节GROUP BY后面的表达式和SELECT后面DATE_FORMAT的表达式必须保持一致很多数据库会直接要求你按这个规则写否则报错。这也是新手常见报错点。需求四统计不同课程名称的订单数量并只显示订单数超过1的课程这个要用GROUP BY结合COUNT再用HAVING过滤SELECT course_name, COUNT(*) AS order_count FROM order_info GROUP BY course_name HAVING order_count 1;结果里MySQL零基础入门出现两次符合条件其他课程只有1单被过滤掉。注意这里不能用WHERE order_count 1因为聚合函数的结果是在分组之后才产生的WHERE执行时COUNT还没算出来。需求五根据订单状态status显示中文状态名称这就是CASE WHEN的典型场景SELECT student_name, course_name, order_amount, CASE WHEN order_status 1 THEN 待支付 WHEN order_status 2 THEN 已支付 WHEN order_status 3 THEN 已取消 ELSE 未知 END AS status_text, IFNULL(DATE_FORMAT(pay_time, %Y-%m-%d %H:%i), 未支付) AS pay_time_text FROM order_info;这个SQL同时用了CASE WHEN和IFNULL。pay_time字段如果是NULL代表未支付就显示成“未支付”文本否则展示格式化后的时间。这种写法在列表页展示场景里非常实用。5. 用函数时最容易踩的坑如果说前面讲的是“怎么用”这一节就是“怎么不出错”。以下这些坑都是我在实际项目和带新人的过程中反复遇到过的有些甚至会造成线上事故级别的数据问题。5.1 在索引列上使用函数会导致索引失效这是性能优化里最经典的一个坑。假设user表的phone字段建了普通索引你按手机号查询用户时写SELECT * FROM user WHERE phone 13812340001;这样写能正常走索引。但如果改写成SELECT * FROM user WHERE UPPER(phone) 13812340001;MySQL就需要先把每一行phone都经过UPPER函数处理然后再比对。函数处理的结果和原始列值不同索引就没法用了只能全表扫描。在数据量大的表上这种SQL能把查询从毫秒级拖到秒级。正确的做法是尽量不让函数作用在索引列上。如果业务上就是要忽略大小写比较可以直接在查询条件里对右侧参数做处理WHERE phone UPPER(13812340001)。把函数放在等号右边索引列就能保持原样索引也就能用上了。还有一种办法是生成冗余列比如在表里专门存一列处理好的小写值并建索引程序写入时同步维护。这个方案多占存储空间但查询效率高。5.2 隐式类型转换引发的“怪问题”MySQL中如果字段类型和传入的参数类型不一致会发生隐式类型转换。这里有个经典的坑如果某个字符串类型的字段比如order_no里存的数据全是数字你用WHERE order_no 12345去查MySQL会把order_no列转成数字再比较导致order_no上的索引失效。更隐蔽的是隐式类型转换还可能导致查询结果不符合预期。比如用字符串比较和数值比较得到的结果不一样因为MySQL底层对数字和字符串的排序规则不同。我的建议很简单字段是什么类型传入参数就写成什么类型。该加引号就加引号不要偷懒。5.3 聚合函数与NULL值的纠缠SUM、AVG、COUNT这三个函数对NULL的处理机制不一样很多人会记混。COUNT(column)不统计NULLSUM(column)忽略NULLAVG(column)也忽略NULL。例如某一列的值是(10, 20, NULL)COUNT(col)的结果是2SUM(col)的结果是30AVG(col)的结果是15而不是10。在业务统计中这种差异会造成数据看起来“对不上”。比如统计员工平均工资时如果某些员工的绩效字段是NULLAVG就自动只算了有绩效的这些人最终平均值比预期高。遇到这种情况要么用IFNULL(column, 0)把NULL先转成0再参与计算要么在统计口径里明确说明NULL的处理方式。5.4 日期函数使用时区与格式不一致DATE_FORMAT的格式化符号大小写敏感的坑也很常见。%Y和%y不同前者是四位年份后者是两位年份%H和%h也不同前者是24小时制后者是12小时制。如果用户输入“2024-01-05 14:30:00”用%h显示出来就是02而不是14会导致时间显示完全不正确。另外SQL里日期的字符串写法要统一。MySQL默认日期格式是YYYY-MM-DD但有些业务方传给数据库的可能是YYYY/MM/DD这种格式在不同模式下可能被解析成不同含义甚至直接报错。建议团队内统一约定日期字符串的标准格式。5.5 函数嵌套不要过度可读性也是效率的一部分有些开发者写SQL特别“炫技”一个SELECT里嵌套四五个函数比如SUBSTRING(REPLACE(TRIM(column), -, ), 1, 6)。功能上没问题但后面的人维护起来非常痛苦。而且函数嵌套越多执行计划会越复杂难以利用索引。我的经验是如果一层函数能解决绝对不写两层如果确实需要多层处理可以考虑用子查询把每一步拆开或者把中间结果先放进临时表然后分步处理。代码可读性和性能一样都是生产力。6. 常用函数速查与选型思路最后整理一张速查表把本文讲过的核心函数浓缩到一起。你可以把这篇文章收藏起来写SQL的时候当作参考手册翻一翻。类别函数作用常用示例字符串CONCAT / CONCAT_WS拼接字符串CONCAT_WS(-, 2024, 01)字符串SUBSTRING截取子串SUBSTRING(abc123, 1, 3)字符串REPLACE替换子串REPLACE(a-b-c, -, )字符串LEFT / RIGHT从左侧/右侧截取LEFT(abc123, 3)字符串UPPER / LOWER大小写转换UPPER(abc)字符串LENGTH / CHAR_LENGTH字节数/字符数CHAR_LENGTH(你好)字符串TRIM去首尾空格TRIM( a )数值ROUND四舍五入ROUND(3.14159, 2)数值CEIL / FLOOR向上/向下取整CEIL(4.2)数值ABS绝对值ABS(-5)数值MOD取余MOD(10, 3)日期NOW当前日期时间NOW()日期CURDATE / CURTIME当前日期/时间CURDATE()日期DATE_FORMAT格式化日期DATE_FORMAT(NOW(), %Y-%m-%d)日期DATEDIFF日期差天数DATEDIFF(2024-01-20, 2024-01-15)日期TIMESTAMPDIFF指定单位的时间差TIMESTAMPDIFF(MINUTE, t1, t2)日期DATE_ADD / DATE_SUB日期加减DATE_SUB(NOW(), INTERVAL 7 DAY)流程IF条件判断IF(sex 1, 男, 女)流程IFNULL空值处理IFNULL(name, 暂无)流程CASE WHEN多分支判断CASE WHEN ... THEN ... END聚合COUNT统计行数COUNT(*)聚合SUM求和SUM(amount)聚合AVG平均值AVG(amount)聚合MAX / MIN最大/最小值MAX(order_time)关于选择思路我给你三条建议。第一能用原生函数处理的数据不要拿回程序里处理。数据库作为数据管理和计算的底座天然适合做这类批量加工而且减少程序端的代码量和网络传输开销。第二优先掌握流程控制和聚合函数。相比字符串处理和日期处理CASE WHEN、IFNULL、COUNT、SUM、GROUP BY这套组合才是做业务统计的核心武器。字符串和日期函数可以边用边查但这几个必须熟练到随手就能写。第三写完函数SQL后养成看执行计划的习惯。在查询语句前面加EXPLAIN关键字就能看到MySQL是怎么执行这条SQL的有没有走索引、扫描了多少行。尤其当你把函数用在WHERE条件里时看执行计划能帮你迅速发现索引失效的问题。我在实际使用中最深的体会是函数本身不难难的是知道“什么时候该用哪个”。当你真正理解了单行函数和聚合函数的区别理解了WHERE、GROUP BY、HAVING的执行顺序理解了函数对索引的影响你对MySQL的掌握程度就已经远超“会用增删改查”的阶段了。这篇文章里的示例建议你全都亲自动手跑一遍哪怕只是拿临时表做实验也比只看不练强得多。遇到报错不要慌把报错信息复制到搜索引擎里一定能有答案——每一个写SQL的人都是这么过来的。