企业官网建设流程全解析

但凡写过两年SQL的人，手里应该都攒过一本「MySQL函数笔记」：这个函数怎么拼、那个函数返回什么、为什么同样一段SQL换个环境就报错——这些零碎问题，最后几乎都能在MySQL内置函数这里碰头。MySQL内置函数是数据库提供的一组现成处理函数，覆盖字符串、数值、日期时间、条件判断、聚合统计等场景，用好了能让SQL变得又短又稳，不用一股脑把逻辑搬到程序里一遍遍重写。

这篇文章打算把内置函数按「分类体系→高频实操→性能雷区→踩坑排查」这条线完整梳理一遍。无论是刚装好MySQL 8.0、还在照着教程建表的初学者，还是已经负责业务库、天天写报表SQL的开发，或者是做数据同步和调优的运维，都能从这里找到可直接抄走的东西。函数本身不分项目大小，建订单表、做用户画像、统计活动转化、同步数据到ClickHouse，处处都用得上，值得认真过一遍。

1. 内置函数全景：先建体系再记细节，别一头扎进函数堆

MySQL官方文档里的函数加起来几百个，如果按照「看到一个记一个」的方式去学，结果大概率是边记边忘，真到写SQL时还要反复查。更合理的做法是先建一个分类框架，把函数按用途装进抽屉里，用到哪一类就翻哪个抽屉，这样记忆负担小很多，应用的时候也能更快定位。

我自己习惯把内置函数分成六大类：字符串处理、数值计算、日期时间、流程控制、聚合统计、JSON与系统信息。前四类属于「行级函数」，也就是对每一行数据单独做处理；聚合函数则是把多行数据汇总成一个结果；JSON和系统函数在5.7之后越来越重要，尤其是8.0把JSON能力大幅增强之后，几乎成了业务表设计的标配。

分类之外，还要搞清楚函数的出现位置。同一个函数放在SELECT里、WHERE里、GROUP BY里，作用和语义可能截然不同。比如SUBSTRING(phone, 1, 3)放在SELECT里是把手机号前三位取出来展示，放在WHERE里就是按前三位过滤，放在GROUP BY里就是按前三位分组统计。这一点很多人初始化学习时会忽略，实际写SQL时却最容易在这里犯迷糊。

内置函数还有一个需要留意的点是「内置」两个字。它指的是MySQL服务端自带的函数，不需要额外安装插件，也不需要你自己写逻辑。和它相对的是自定义函数（UDF），那是需要开发者自己创建、自己维护的函数体，不在本文讨论范围之内。在实际项目中，能用内置函数解决的，尽量不要去写自定义函数，原因后面性能部分会详细说。

版本差异同样不容忽视。MySQL 5.7和8.0虽然都以「MySQL」命名，但函数能力差距不小。JSON函数在5.7里已经能用基础语法，8.0进一步支持JSON_TABLE等高级特性；窗口函数是8.0才有的，5.7里只能用子查询加变量硬凑；REGEXP_REPLACE这类字符串正则替换函数也只在8.0里才完整可用。所以写函数之前，第一件事是确认线上版本，别拿8.0的语法去5.7上跑，那一定会报错。

2. 字符串与数值函数：日常SQL里最常用的两个大类

2.1 字符串函数：拼接、截取、替换、清洗一站搞定

字符串函数是平时写SQL用得最多的，尤其是做数据清洗和报表展示的时候。先看拼接：CONCAT(str1, str2, ...)可以把多个字段或常量拼成一个字符串，但有一个非常经典的坑——只要任何一个参数是NULL，整个结果就会变成NULL。比如CONCAT(first_name, last_name)，只要last_name为空，结果就整个是空的，这在用户名单拼接场景里很容易引发事故。

解决办法有两种，各看场景。第一种是CONCAT_WS(separator, str1, str2, ...)，它会在参数之间插入分隔符，并且会自动跳过NULL值，不会返回NULL。第二种是用IFNULL先把NULL转成默认值再拼接。我的习惯是：拼接地址、姓名这类「某个字段可能缺失但其他字段还要保留」的场景，优先用CONCAT_WS；需要严格控制结果格式的场景，用IFNULL手动指定空值替换。

截取函数里，SUBSTRING(str, pos, len)是最基础的一个。注意MySQL的字符位置是从1开始数的，不是从0开始，这点和Java、JavaScript的字符串截取习惯完全不同，刚切换过来的人很容易写串。SUBSTRING('2024-06-15', 1, 4)返回的是"2024"，很多人第一次写成SUBSTRING('2024-06-15', 0, 4)，结果会平白多出一个空字符或错位。

从身份证取生日是一个很典型的综合练习：CONCAT(SUBSTRING(id_card, 7, 4), '-', SUBSTRING(id_card, 11, 2), '-', SUBSTRING(id_card, 13, 2))，一次把截取和拼接都用上了。如果只想取左侧或右侧固定长度，还有LEFT(str, len)和RIGHT(str, len)两个便捷函数，比如RIGHT(phone, 4)取手机号后四位做脱敏展示。

查找和替换也是高频操作。LOCATE(substr, str)返回子串第一次出现的位置，找不到返回0，常用来做条件判断，比如找出所有邮箱是QQ邮箱的用户：WHERE LOCATE('@qq.com', email) > 0。REPLACE(str, from_str, to_str)做全量替换，注意它是替换所有匹配项，不是只替换第一个。清洗用户输入数据时我会连续嵌套好几个REPLACE，把回车、换行、多个空格都清理掉。

有一个点必须单独拎出来讲：LENGTH(str)和CHAR_LENGTH(str)的区别。LENGTH返回的是字节数，CHAR_LENGTH返回的是字符数。在中文字符集UTF-8下，一个汉字占3个字节，LENGTH('张三')的结果是6，CHAR_LENGTH('张三')的结果是2。很多人在做长度校验时用错了函数，结果明明限定了用户名最多10个字符，中文用户却只能存3个，这就是典型的函数误用。

2.2 数值函数：取整、取余、随机数的那些坑

数值函数在报表计算中无处不在，但坑也多。最典型的是取整函数的选择。MySQL里有ROUND()、FLOOR()、CEILING()、TRUNCATE()四个看起来差不多的函数，实际行为完全不同。

ROUND(3.14159, 2)是四舍五入到指定小数位，返回3.14；FLOOR(3.99)向下取整返回3，CEILING(3.01)向上取整返回4；TRUNCATE(3.99, 1)是直接截断，不管后面是几，返回3.9。它们的区别可以用一句话总结：FLOOR和CEILING只处理整数方向，TRUNCATE只看小数位不管舍入，ROUND才会真正考虑进位。计算分页偏移量、库存分配这类场景，选错取整函数会导致结果差一。

MOD(a, b)取余数，等价于a % b。有一个容易被忽略的行为：当b为负数时，MySQL中MOD的结果可能和编程语言里不一样，所以习惯上用正数做模运算更安全。POWER(a, b)和SQRT()用于幂运算和平方根，ABS()取绝对值，这些就没太多幺蛾子，直接用。

随机数函数RAND()值得单独说一说。RAND()每次执行都会返回一个0到1之间的随机小数，注意是「每次执行」都会变。如果在一个查询里多次调用RAND()，每一行拿到的随机值都不同。想生成指定范围内的随机整数，标准写法是FLOOR(RAND() * (max - min + 1)) + min。比如要生成1到100之间的随机整数：FLOOR(RAND() * 100) + 1。这个公式理解起来也不难：RAND()最大接近1，乘以区间长度100得到接近100的数，FLOOR后得到0到99，再加1就是1到100。

我实际做过的一个需求是运营后台的随机抽奖，要从商品表里随机抽10个商品。当时写的SQL是ORDER BY RAND() LIMIT 10，功能是实现了，但商品量大之后明显变慢。原因是ORDER BY RAND()需要为每一行生成随机数再排序，全表扫描加文件排序，数据量上了百万就会拖垮库。后来改成先SELECT id FROM table WHERE ... LIMIT 1000取出候选集，在程序里随机挑10个，再用主键回表，速度快了一个数量级。这也是一个典型教训：函数好用，但不能无脑套在热点查询里。

3. 日期时间函数：订单统计和报表的地基工程

3.1 拿到当前时间：NOW、CURDATE、SYSDATE三兄弟别混用

业务表里几乎都有create_time字段，所以获取当前时间的函数是入门第一课。NOW()返回当前完整的日期和时间，格式是"YYYY-MM-DD HH:MM:SS"；CURDATE()只返回日期部分；CURTIME()只返回时间部分。这三兄弟很简单，但有个隐蔽的坑：NOW()和SYSDATE()在官方文档里都表示当前时间，执行结果看起来一样，实际机制不同。NOW()是语句开始执行的时间，一条SQL里不管调用多少次NOW()，拿到的都是同一个值；而SYSDATE()是函数被真正执行那一刻的时间，如果一条SQL执行耗时比较长，不同位置的SYSDATE()返回值可能不一样。

这个差异在复制架构里尤其危险。主库执行一条用了SYSDATE()的长SQL，备库在回放这条SQL时调用SYSDATE()拿到的是备库当前时间，两边数据就可能不一致。生产环境处理流水、订单这类对时间一致性敏感的表，我会统一用NOW()或直接让字段走DEFAULT CURRENT_TIMESTAMP，不碰SYSDATE()。

3.2 格式化与解析：DATE_FORMAT和STR_TO_DATE是一对镜像

日期格式化的核心函数是DATE_FORMAT(date, format)。format参数用一堆百分号占位符表示输出格式，最常用的几个是：%Y四位数年份、%m两位数月份、%d两位数日期、%H24小时制小时、%i分钟、%s秒。典型写法：DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s')，输出"2024-06-15 14:30:00"。

把字符串解析成日期的函数是STR_TO_DATE(str, format)，和DATE_FORMAT的格式占位符完全一致，相当于镜像操作。比如前端传过来一个"2024/06/15"这种格式，直接存进DATE字段之前要先解析：STR_TO_DATE('2024/06/15', '%Y/%m/%d')。如果不做解析，让MySQL隐式转换，很容易出现格式不识别导致报错或存储异常的情况。

有一个高频报表需求：按天、按月、按年分组统计。按月分组的经典写法是DATE_FORMAT(create_time, '%Y-%m')作为分组键，然后COUNT或SUM。但这里有一个必须提前知道的性能代价：对create_time字段套DATE_FORMAT函数后，这个字段上的索引就失效了，大量数据时查询会慢。具体解法后面第6节专门讲，这里先记住结论。

3.3 时间差与日期运算：DATEDIFF和TIMESTAMPDIFF的细节差异

计算两个日期之间差多少天，用DATEDIFF(expr1, expr2)，结果是expr1减expr2的天数，只看日期部分，不看时间。比如DATEDIFF('2024-06-15', '2024-06-01')返回14，用来算用户注册天数、优惠券剩余有效期都很顺手。

TIMESTAMPDIFF(unit, start, end)则更通用，unit可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、YEAR等，比如计算两个时间之间差多少分钟：TIMESTAMPDIFF(MINUTE, start_time, end_time)。注意参数顺序是「结束时间在前，开始时间在后」，很多人第一次用总写反，导致结果出现负数。

日期加减运算用DATE_ADD(date, INTERVAL expr unit)和DATE_SUB(date, INTERVAL expr unit)，也可以用等价的ADDDATE和SUBDATE。我的习惯是统一用DATE_ADD/DATE_SUB，因为INTERVAL语法更清晰。比如统计最近7天订单：WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)。注意这里用CURDATE()而不是NOW()，因为CURDATE()返回日期，和日期字段比较时不会把当天零点之前的数据漏掉。

如果你在做每月1号自动结算、每周一自动汇总这类周期性任务，遵循一个原则：能用日期函数直接在SQL里算出来的，就不要在程序里先算好再传参。这样逻辑收敛在数据库层，排查问题时只需要看SQL就能理解全部时间口径。

4. 流程控制与聚合函数：让SQL具备业务判断能力

4.1 IF、IFNULL、NULLIF、CASE WHEN的使用边界

流程控制函数让SQL不只是「查数据」，还能在查询过程中做判断。最基础的是IF(expr, true_value, false_value)，三目运算符的SQL版。比如把订单金额大于100的标记为「大单」：IF(amount > 100, '大单', '普通单')。

IFNULL(expr1, expr2)专门处理NULL，expr1为NULL时返回expr2。更灵活的是COALESCE(expr1, expr2, ..., exprN)，它能依次检查多个参数，返回第一个非NULL值。COALESCE在多个可能为空的字段里取「第一个有效值」这个场景非常好用，比如COALESCE(nickname, real_name, '匿名用户')，用户的昵称没填就取真名，真名也没有就显示"匿名用户"。

NULLIF(expr1, expr2)的逻辑是：当expr1等于expr2时返回NULL，否则返回expr1。这个函数最常见的用法是做「除零保护」。比如统计客单价：SUM(amount) / NULLIF(COUNT(*), 0)，当COUNT(*)=0时NULLIF返回NULL，除法结果就是NULL而不是报错。这一类「不要让SQL直接除零」的细节，就是日常开发里最容易体现功力的地方。

CASE WHEN是流程控制里的重头戏，也是我最推荐的条件判断写法。它的可读性比嵌套IF强太多，特别是多个分支时，IF嵌套写三层以上基本没法维护，CASE WHEN一层一层列出来，谁看了都明白。订单状态转中文是个经典例子：

SELECT order_no, CASE status WHEN 1 THEN '待付款' WHEN 2 THEN '已付款' WHEN 3 THEN '已发货' ELSE '未知状态' END AS status_text FROM orders;

这里有一个新手常踩的坑：CASE WHEN如果没有匹配到任何分支，又没有写ELSE，结果会返回NULL。状态转换场景里，这会让展示层直接出现空值。我现在的规矩是：所有CASE WHEN一律写ELSE兜底，哪怕兜底值就是'未知'，也要把NULL可能性堵死。

4.2 聚合函数：COUNT、SUM、AVG、MAX、MIN的隐藏陷阱

聚合函数是把多行数据汇总成一行结果的函数，通常和GROUP BY搭配使用。COUNT(*)统计行数、COUNT(字段)统计该字段非NULL的行数，这两者的差异是最常见的坑。COUNT()不管字段值是不是NULL都会计数，而COUNT(字段)会跳过NULL。如果写成COUNT(remark)想统计有备注的订单数，而某些订单的remark是NULL，结果会比COUNT()少，这是完全正常的，但很多人会当成Bug来排查。

SUM和AVG都有一个特性：计算时忽略NULL值。也就是说某一行字段是NULL，不会参与SUM的累加，也不会被算进AVG的分母。但如果整组数据都是NULL，SUM返回NULL而不是0，AVG也返回NULL。报表里把这些值直接展示出来，前端可能显示成空白甚至报错。稳妥做法是外层套IFNULL：IFNULL(SUM(amount), 0)，让结果为0而不是NULL。

MAX和MIN相对简单，取一组数据的最大最小值。注意它们同样忽略NULL，所以不会出现「最小值是NULL」的情况。

GROUP_CONCAT是把一组数据拼成字符串的函数，报表场景里很实用。比如查一个订单下的所有商品名：GROUP_CONCAT(product_name SEPARATOR '、')。它有两个注意事项：一是默认长度限制是1024字节，超过会被静默截断，需要先SET SESSION group_concat_max_len = 102400调大；二是排序稳定性问题，如果想让拼接结果按时间顺序排列，要写成GROUP_CONCAT(product_name ORDER BY create_time SEPARATOR '、')。

聚合函数配合CASE WHEN可以做行转列。一个经典的例子：统计各月份订单里不同支付方式的数量占比。

SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*) AS total_orders, SUM(CASE WHEN pay_type = 'wechat' THEN 1 ELSE 0 END) AS wechat_orders, SUM(CASE WHEN pay_type = 'alipay' THEN 1 ELSE 0 END) AS alipay_orders FROM orders GROUP BY DATE_FORMAT(create_time, '%Y-%m');

CASE WHEN在里面充当了过滤器的角色：满足条件返回1，不满足返回0，SUM之后就是计数。这个模式非常常用，建议直接背下来。

5. JSON函数与系统函数：5.7/8.0带来的新玩法

5.1 JSON字段的提取、修改与聚合

从MySQL 5.7开始支持JSON类型，8.0继续增强，JSON函数现在已经是业务开发绕不开的一块。业务表里存JSON的场景太多了：活动配置、用户扩展信息、埋点参数、第三方回调原始数据等等。如果还在用VARCHAR存JSON再靠程序解析，不仅查询麻烦，还没法用数据库侧的表达式索引。

提取JSON字段里的值，最基础的是JSON_EXTRACT(json_doc, path)，第二参数是路径表达式，比如$.name表示根节点下的name属性。它的简写形式是->运算符：data -> '$.name'。要注意的是，JSON_EXTRACT和->返回的仍然是JSON类型，如果原值是字符串"张三"，返回结果是带引号的"张三"。想直接拿到纯字符串，要用->>运算符：data ->> '$.name'。一个简单记忆方法：多一个>符号，就多剥一层引号。

SELECT user_id, profile ->> '$.name' AS user_name, profile ->> '$.age' AS user_age FROM user_profile WHERE profile ->> '$.city' = '杭州';

修改JSON用JSON_SET(json_doc, path, value)，它会更新已有键或新增不存在的键。JSON_INSERT只新增不更新，JSON_REPLACE只更新不新增，三者语义不同，选错会覆盖数据。实际生产里我几乎只用JSON_SET，因为它的行为最符合直觉：路径存在就改，不存在就加。

聚合生成JSON的函数也很有用。JSON_ARRAYAGG(expr)把一组值聚合成JSON数组，JSON_OBJECTAGG(key, value)把一组键值对聚合成JSON对象。比如查一个商品的所有标签：

SELECT product_id, JSON_ARRAYAGG(tag_name) FROM product_tags GROUP BY product_id;

如果你想在5.7上把多行数据拼成JSON数组，基本只能靠JSON_ARRAYAGG；等升到8.0，配合JSON_TABLE还能把JSON拆回关系表，两个方向都能走通。

5.2 系统信息函数：运维和开发的日常工具

系统信息函数在运维脚本和后台管理页面里出场频率很高。VERSION()返回当前MySQL版本号，排查环境差异时SELECT VERSION();一句就能定位。DATABASE()返回当前默认库名，多库共用连接池时很有用。USER()返回当前连接的用户和主机信息，CURRENT_USER()返回当前账号实际匹配的认证用户。调试权限问题时，这两个函数能帮你快速确认「我到底是谁」。

CONNECTION_ID()返回当前连接的线程ID，KILL一个卡住的连接时，先用它查到ID，再配合KILL命令处理，比满屏找更高效。LAST_INSERT_ID()是另一个高频函数，返回最近一次INSERT操作中自增主键的值。注意它只对本会话生效，不会被其他连接干扰，所以可以在程序里安全地获取刚插入记录的主键，避免再查一次表。

UUID()用来生成全局唯一字符串。它基于时间和MAC地址生成，算是一把不会重复的「随机钥匙」。如果不想让主键暴露业务量大小，可以用UUID()或者它的变体UUID_SHORT()。区别在于UUID_SHORT()返回一个64位整数，比UUID字符串省空间，但在分布式环境下依然有可能碰撞，单库场景下用问题不大。

MD5()和SHA1()是信息摘要函数，本质上不是加密，而是生成固定长度的指纹。常见用途：对手机号、身份证做脱敏前的哈希索引，或者对文件内容做完整性校验。但记住它们不能用于密码存储场景，密码哈希应该用专门的加密算法在应用层完成，数据库函数只负责业务数据的处理，不做安全边界。

6. 函数的性能代价：索引失效与隐式转换必须背下来

6.1 WHERE列上套函数，再好的索引也白搭

使用内置函数最大的性能隐患，就是把函数套在索引列上参与条件筛选。经典反面教材：

SELECT * FROM orders WHERE DATE(create_time) = '2024-06-15';

这条SQL的逻辑没毛病，但它对create_time调用了DATE()函数。MySQL在大多数情况下无法对函数作用后的结果使用B树索引，索引有序性被破坏了，优化器只能放弃索引，走全表扫描。数据量小的时候感觉不出来，等表里几百万行，一次全表扫描就能把接口拖到超时。

正确做法是把函数从列上抹掉，改成范围比较：

SELECT * FROM orders WHERE create_time >= '2024-06-15 00:00:00' AND create_time < '2024-06-16 00:00:00';

这样create_time字段保持原样，可以直接走索引，范围扫描比全表扫描快几个量级。这个改写思路可以推广到所有日期函数：DATE()、YEAR()、MONTH()、DATE_FORMAT()等凡是套在列上的,都想办法改写成对常量做函数、对列做范围比较。

如果因为业务需要必须按自然月分组统计，而原列上的索引又很重要，8.0以后可以用生成列加索引来兼顾。先定义month_col TINYINT GENERATED ALWAYS AS (MONTH(create_time)) STORED，然后对这个生成列建索引，查询时直接WHERE month_col = 6。这样外表看起来还是函数逻辑，实际已经转化成了普通列匹配，索引也不浪费。

6.2 隐式类型转换：数字当字符串用，字符串当数字用

另一种容易让索引失效的情况是隐式类型转换。典型场景：字段phone_num是VARCHAR类型，但SQL里写的是数字常量。

SELECT * FROM users WHERE phone_num = 13800138000;

MySQL会自动把字段值转换成数字再比较，而一旦对列做了类型转换，列上的索引就用不上了。结果就是全表扫描，一查一个准。解决办法是写SQL时保持类型一致，把数字常量写成字符串：

SELECT * FROM users WHERE phone_num = '13800138000';

反过来也一样，如果字段是INTEGER类型，条件里写字符串比较安全吗？WHERE id = '100'这种，MySQL还是会做类型转换，但因为是对常量转而不是对列转，索引不受影响。真正要避免的是「对列做隐式函数处理」的情况。所以核心原则是：比较时等号两边类型一致，或者让转换发生在常量那一侧。

6.3 分组排序中的函数索引问题

GROUP BY和ORDER BY里使用函数同样会导致无法高效排序或分组。ORDER BY DATE(create_time)这笔排序等于让MySQL把每行都计算一遍DATE()，然后再对计算结果排序，索引的有序性完全无效。GROUP BY DATE_FORMAT(create_time, '%Y-%m')同样如此，分组逻辑无法走索引，会在临时表里完成分组。

如果你的查询模式固定是「按月份分组」，我更推荐的方案是业务表直接冗余一个month字段，写入时由程序或默认值计算好，查询时直接GROUP BY month，索引照常生效。这确实有一点冗余，但换来的是查询性能的确定性。用空间换时间，在报表系统里是常态。

7. 高频踩坑清单与排查思路：遇到函数问题照着这个表走

把多年来被问到最多的函数相关问题整理成一张速查表，遇到异常先对照一遍，比翻文档快很多。

排查函数相关问题，我的固定套路是三步走。第一步，把SQL拆开，一段一段注释掉，定位是哪个函数导致的异常；第二步，单独SELECT这个函数作用于测试数据上的结果，确认函数本身的输出是否符合预期；第三步，用EXPLAIN看执行计划，确认是否因为函数导致索引失效或产生了额外的文件排序、临时表。大多数函数问题走完这三步都能定位到根因，剩下的基本就是业务口径没对齐，不是函数的问题。

提示：函数本身只是工具，跑得慢、报错、结果不对，多半是使用姿势或者周边环境的问题。排查时先确认数据、再确认函数行为、最后确认执行计划，这个顺序不要颠倒，否则容易在错误的方向上反复折腾。

8. 最后再分享一个实践技巧

和我一样经常被「又要函数可读、又要查询够快」夹在中间的人，可以试试MySQL 8.0的生成列方案。我已经在好几个项目里落地了这个思路：表里原本需要一个YEAR(create_time)作为分组维度，我不再让SQL每次现算，而是建一个STORED生成列，加索引，查询直接走列。表面上多占了一点存储，但换来了SQL更简洁、索引更稳、报表查询稳定可控。

另外，花半小时建一份自己的「函数速查表」绝对值得。我自己的表格分四列：函数名、语法、行为说明、生产环境里用过的真实场景。每踩一次坑就往里补一行，时间长了就是一份比官方文档更适合自己的参考手册。遇到拿不准的函数，先查自己的表，再查官方文档确认版本差异，基本不会再被困在同一个小坑里。