有不少开发者对 MySQL 的印象还停留在“增删改查”上遇到复杂点的数据处理需求第一反应就是拉回应用层用代码搞定。实际上MySQL 自带的函数库比很多人想象的要强大得多——处理字符串、算日期、搞逻辑判断、做聚合统计大部分活儿在 SQL 里几行就能干完非要在 Java 或 Python 里绕一大圈性能损耗大代码也臃肿。这篇东西不打算做成文档翻译那玩意官方手册里都有。我从实际开发里经常用到的场景出发把 MySQL 常用函数的用法、坑点、性能注意项一次说清楚。不管你是刚入门的新手还是写了好几年 SQL 的老手都能在里头翻到点有价值的东西。1. 字符串处理数据清洗的常规武器字符串函数是日常打交道最多的一类。别以为就是CONCAT拼个串、SUBSTRING截个字符真到用的时候细节坑多得很。1.1 CONCAT 与 CONCAT_WS拼接也有讲究先看CONCAT。它接受两个或多个参数把它们连成一个字符串。绝大多数人第一次写 SQL 拼接字段用的都是它。SELECT CONCAT(Hello, , World); -- 输出: Hello World这里有个容易忽略的点CONCAT只要遇到任何一个参数为NULL整个结果就会变成NULL。这在拼接用户地址、姓名等可能为空的字段时结果会直接“消失”而不是显示出能用的部分。SELECT CONCAT(用户:, NULL, 来自); -- 输出: NULL我实际开发中就踩过这个坑。一次做报表要把省市区三个字段拼成完整地址某个客户的区字段是空的结果整条数据在列表里直接空白。排查半天才发现是NULL传导导致整串失效。后来统一改成CONCAT_WS问题迎刃而解。CONCAT_WS是带分隔符的拼接函数第一个参数指定分隔符后面的参数是要拼接的内容。它的好处有两个一是分分隔符只需写一次不需要每个字段后面跟一个二是它会自动跳过NULL值不会整串归零。SELECT CONCAT_WS(-, 2024, 05, 20); -- 输出: 2024-05-20 SELECT CONCAT_WS( , 用户, NULL, 已登录); -- 输出: 用户 已登录提示拼接多个字段形成唯一标识时优先用CONCAT_WS别用CONCAT。空值跳过这个特性能省掉一大串IFNULL判空逻辑。1.2 SUBSTRING 与字符串截取的边界问题SUBSTRING函数用来截取字符串的一部分常用写法是SUBSTRING(str, pos, len)从pos位置开始截取长度为len的字符。注意这里的“位置”是从 1 开始计数的不是从 0。SELECT SUBSTRING(abcdefg, 2, 3); -- 输出: bcd新手最容易搞混的是pos为 0 或者负数的情况。pos为 0 时返回空字符串pos为负数时从字符串尾部倒着数位置但截取方向仍然是正向的。SELECT SUBSTRING(abcdefg, -3, 2), SUBSTRING(abcdefg, 0, 3); -- 输出: ef 和 空串还有一个细节len如果省略则一直截到字符串末尾。SELECT SUBSTRING(abcdefg, 3); -- 输出: cdefg实际业务里SUBSTRING常用于清洗固定格式的编码、截取订单号前缀、处理日志里的时间戳等场景。需要配合LOCATE或INSTR找到某个子串的位置再动态截取这样就不怕字段长度有变化了。-- 假设字段值是 订单号:SO20240520001要截取冒号后面的内容 SELECT SUBSTRING(order_no, LOCATE(:, order_no) 1) FROM orders; -- 输出: SO202405200011.3 REPLACE 与字符替换的隐藏细节REPLACE(str, from_str, to_str)的作用是把字符串里所有匹配from_str的子串替换为to_str。它替换的是全部匹配项不是只换第一个。SELECT REPLACE(MySQL MySQL MySQL, MySQL, MariaDB); -- 输出: MariaDB MariaDB MariaDB这里有个容易忽略的点REPLACE区分大小写。在默认的排序规则下REPLACE(abc, A, X)不会生效因为A和字符串里的a不相等。需要大小写不敏感的替换时得配合LOWER或UPPER先统一大小写。另一个实际中很常见的用途是清洗文本里的换行符。从文件导入的数据经常带着\r\n这类字符在页面上显示时会有多余的空行。用REPLACE可以把它们统一干掉UPDATE article_content SET content REPLACE(REPLACE(content, \r\n, ), \n, );清洗完如果需要建全文索引处理好换行符能显著提升分词匹配的质量。1.4 LEFT、RIGHT 与 LPAD、RPAD 的配合使用LEFT(str, len)从左侧截取指定长度RIGHT(str, len)从右侧截取。这两个函数逻辑简单但在分组统计场景里非常实用。比如想按照日期字符串的“年-月”分组用LEFT(date_str, 7)就能直接取到月份前缀。SELECT LEFT(create_time, 7) AS month, COUNT(*) FROM orders GROUP BY LEFT(create_time, 7);LPAD和RPAD则是填充函数在左侧或右侧用指定字符填充到指定长度。典型的应用场景是流水号补零订单号要求固定 8 位从 1 开始递增可以写成LPAD(id, 8, 0)。SELECT LPAD(123, 8, 0); -- 输出: 00000123需要注意如果原字符串本身长度超过目标长度填充函数不会截断而是直接返回原字符串。这点和LEFT、RIGHT的行为不同容易让人误判。实操心得做导出文件或打印面单时编码统一用LPAD补齐位数能避免 Excel 打开长号码时自动变成科学计数法同时也让排序更规整。1.5 TRIM、LTRIM、RTRIM空格清理三兄弟TRIM函数能去除字符串两端的空格LTRIM只去掉左侧RTRIM只去掉右侧。它们处理的对象默认是空格也可以显式指定要去除的字符。SELECT TRIM( MySQL ), LTRIM( MySQL ), RTRIM( MySQL ); -- 输出: MySQL, MySQL , MySQL指定字符的做法有点冷门但实用SELECT TRIM(BOTH x FROM xxxMySQLxxx); -- 输出: MySQL这里支持的关键字是BOTH、LEADING、TRAILING分别对应两端、开头、结尾。处理带特定前缀后缀的字符串时很方便。实际业务中用户输入的手机号、邮箱地址经常带着意外的空格导致 WHERE 条件匹配失败。常规做法是写入前就做TRIM如果存量数据不干净可以跑一条 UPDATE 统一清洗UPDATE users SET phone TRIM(phone) WHERE phone ! TRIM(phone);2. 数值计算聚合之外的小能手SUM、AVG、COUNT这些聚合函数大家都会用这里不多说。我想重点聊聊的是那些在单行计算里好用到飞起但知道的人相对少的数值函数。2.1 ROUND、TRUNCATE 与 FLOOR、CEIL取整函数的细微差别ROUND(x, d)做四舍五入保留 d 位小数TRUNCATE(x, d)直接截断不四舍五入FLOOR(x)向下取整CEIL(x)向上取整。这四个放一起对比能看出 MySQL 在处理小数上的精细程度。SELECT ROUND(3.14159, 2), TRUNCATE(3.14159, 2), FLOOR(3.14159), CEIL(3.14159); -- 输出: 3.14, 3.14, 3, 4容易踩坑的是ROUND对负数的处理。ROUND(2.5)在 MySQL 里结果是 2因为 MySQL 的四舍五入采用的是“四舍六入五成双”的银行家舍入法。也就是说5 不一定进位得看前一位的奇偶性。这一点和多数编程语言里的Math.round行为不同写代码时容易默默中招。SELECT ROUND(2.5), ROUND(3.5); -- 输出: 2, 4TRUNCATE的 d 参数还可以是负数表示在小数点左侧进行截断SELECT TRUNCATE(12345.678, -2); -- 输出: 12300这在统计金额量级、抹零处理时很好用。算订单金额的“万”级别汇总时可以直接TRUNCATE(amount, -4)。2.2 MOD 取模与业务分表MOD(x, y)返回 x 除以 y 的余数。这东西在分库分表、按比例抽样、奇偶判断上都有用武之地。最简单的用法是判断奇偶MOD(id, 2) 1表示奇数MOD(id, 2) 0表示偶数。在做 A/B 测试分组、双色球类逻辑时很常用。分表场景下比如订单表按用户 ID 分成 16 张表取模就是路由的依据。虽然现在分库分表中间件很普及但偶尔手写分表逻辑时还是能用到SELECT CONCAT(orders_, MOD(user_id, 16)) FROM orders LIMIT 1;2.3 ABS、GREATEST、LEAST极值比较的快捷方式ABS(x)返回绝对值处理差值时常用。比如计算两个坐标的距离差或者比较两个时间戳的间隔不想关心谁大谁小套个ABS就能得到正数结果。GREATEST和LEAST则返回一组值里的最大值和最小值。这不是聚合函数是行级别的比较函数。SELECT GREATEST(10, 20, 15), LEAST(10, 20, 15); -- 输出: 20, 10GREATEST在实际业务里有个巧用限制涨幅下限。比如计算价格时要求最低不能低于原价的 8 折可以用GREATEST(price * 0.8, 100)——如果算出来的折后价低于 100就取 100。同理LEAST可以限制上限。还有个小众用途GREATEST和LEAST对日期类型也有效。比较两个日期谁更晚直接GREATEST(date1, date2)就行不需要转成时间戳再比。2.4 数值函数与字段类型的互相影响用数值函数的时候得留意字段本身的类型。FLOAT、DOUBLE是近似值存储DECIMAL是精确值存储。做金额计算时强烈建议字段用DECIMAL而不是FLOAT否则ROUND的结果可能会让你怀疑人生。举例说明。一张表里金额字段是FLOAT(10, 2)值 9999999.99。你执行SELECT ROUND(amount, 2)得到的可能不是自己想要的精确值。原因在于FLOAT存储时就已经有精度损失后续操作都是基于有损值进行的ROUND救不回来。注意涉及金钱、汇率、库存这类需要精确计算的场景一律使用DECIMAL类型。FLOAT和DOUBLE只适合科学计算这类允许误差的场景。3. 日期时间业务分析的时间引擎日期函数是用得最多也最容易写错的一类。常常有人在 WHERE 条件里对日期字段套函数结果导致索引失效拖慢整个查询。这一块值得好好理一理。3.1 NOW、CURDATE、CURTIME 的选择NOW()返回当前的日期和时间CURDATE()只返回日期CURTIME()只返回时间。这三兄弟的选择取决于你的业务需要。需要明确的是NOW()和SYSDATE()的区别。NOW()在语句开始执行时获取时间是一个固定的时间点SYSDATE()则是在函数执行时才获取当前时间。在一条复杂的 SQL 里多次调用NOW()结果一致而SYSDATE()在这条 SQL 执行的不同阶段返回值可能不同。这就导致SYSDATE()在长事务里可能产生不可预期的结果而且它还会干扰基于NOW()的查询优化。默认情况下能不用就不用统一用NOW()。SELECT NOW(), SYSDATE(), CURDATE(), CURTIME(); -- 输出: 2025-01-15 14:23:45, 2025-01-15 14:23:45, 2025-01-15, 14:23:453.2 日期格式化 DATE_FORMAT 与 STR_TO_DATEDATE_FORMAT(date, format)把日期按指定格式转成字符串。STR_TO_DATE(str, format)是它的逆操作把字符串按格式解析成日期。最常用的几个格式化占位符占位符含义示例%Y四位年份2025%y两位年份25%m两位月份01%d两位日期15%H24小时制小时14%i分钟23%s秒45%W星期名Wednesday%w星期数字(0-6)3DATE_FORMAT(now(), %Y-%m-%d %H:%i:%s)是最常见的输出格式。做月度报表分组时DATE_FORMAT(create_time, %Y-%m)能直接生成月份分组键。STR_TO_DATE在数据导入时特别有用。CSV 里的日期格式五花八门有的带斜杠有的带中文直接CAST转换很容易报错。用STR_TO_DATE显式指定格式能把各种非标准字符串统一转成标准日期类型。SELECT STR_TO_DATE(2025/01/15, %Y/%m/%d); -- 输出: 2025-01-15 SELECT STR_TO_DATE(2025年1月15日, %Y年%m月%d日); -- 输出: 2025-01-15注意STR_TO_DATE的格式必须和字符串实际结构一致否则返回NULL。导入数据前先抽样检测一遍别让脏数据静默通过。3.3 DATEDIFF 与 TIMESTAMPDIFF日期差值的两种思路DATEDIFF(date1, date2)返回 date1 和 date2 相差的天数是 date1 减 date2。这个函数只关注日期部分时间部分会被忽略。TIMESTAMPDIFF(unit, date1, date2)更灵活可以指定返回的单位包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR等。-- 用户注册多少天了 SELECT DATEDIFF(NOW(), register_time) FROM users; -- 两个时间点相差多少分钟 SELECT TIMESTAMPDIFF(MINUTE, start_time, end_time) FROM tasks;TIMESTAMPDIFF的注意点是参数顺序它是后面的时间减前面的时间。TIMESTAMPDIFF(MONTH, 2025-01-01, 2025-03-15)结果是 2而把顺序反过来结果是 -2。用DATEDIFF算月数不大合适因为一年按月算不是固定天数。要算月差、年差请用TIMESTAMPDIFF。3.4 DATE_ADD、DATE_SUB 与时间偏移DATE_ADD(date, INTERVAL expr unit)在日期上增加一段时间DATE_SUB(date, INTERVAL expr unit)则减少一段时间。这里的unit可以是DAY、MONTH、YEAR、HOUR、MINUTE、SECOND等。SELECT DATE_ADD(2025-01-15, INTERVAL 1 MONTH); -- 输出: 2025-02-15 SELECT DATE_SUB(2025-01-15, INTERVAL 7 DAY); -- 输出: 2025-01-08支持的关键字还包括INTERVAL 1 DAY、INTERVAL 2 HOUR这种复合表达式。甚至可以用负数来反向操作DATE_ADD(date, INTERVAL -1 DAY)等价于DATE_SUB(date, INTERVAL 1 DAY)。实际业务里查“近 30 天订单”最推荐的方式是SELECT * FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 30 DAY);这里有个非常关键的性能要点不要在日期字段上套函数比如WHERE DATE(create_time) CURDATE() - INTERVAL 30 DAY。一旦对字段应用函数字段上的索引就废了会变成全表扫描。直接让字段和表达式比较索引才能生效。3.5 UNIX_TIMESTAMP 与 FROM_UNIXTIME时间戳互转UNIX_TIMESTAMP(date)把日期时间转成 Unix 时间戳从 1970-01-01 00:00:00 UTC 到现在的秒数。FROM_UNIXTIME(timestamp)则做反向转换。SELECT UNIX_TIMESTAMP(2025-01-15 14:23:45); -- 输出: 1759471425 (示例值) SELECT FROM_UNIXTIME(1759471425); -- 输出: 2025-01-15 14:23:45FROM_UNIXTIME还支持第二个格式参数直接返回格式化字符串SELECT FROM_UNIXTIME(1759471425, %Y-%m-%d); -- 输出: 2025-01-15前端传时间戳到后端后端要按天统计活跃用户直接在 SQL 里FROM_UNIXTIME(login_ts, %Y-%m-%d)就能分组不需要在应用层循环处理。需要留意时区问题。UNIX_TIMESTAMP和FROM_UNIXTIME的转换基于 MySQL 会话的时区设置。如果数据库时区是 UTC而应用期望的是北京时间转换结果就会差 8 小时。处理这类问题时先确认SELECT global.time_zone, session.time_zone的配置。4. 条件逻辑让数据按规则分流条件判断函数是 SQL 里实现“if-else”逻辑的主要手段。合理使用能减少应用层代码让数据在数据库端就完成分流和清洗。4.1 IF 与 IFNULL最基础的条件分支IF(expr, true_value, false_value)是最简单的条件判断。如果表达式为真返回第二个参数否则返回第三个参数。SELECT IF(1 0, yes, no); -- 输出: yesIFNULL(expr1, expr2)是IF的一个特化包装当第一个参数为NULL时返回第二个参数否则返回第一个参数。SELECT IFNULL(NULL, 默认值); -- 输出: 默认值IFNULL在处理可空字段的默认值上非常顺手。比如用户表中nickname允许为空展示时需要显示为“匿名用户”直接IFNULL(nickname, 匿名用户)一行搞定。IF嵌套也能实现多分支但三层以上可读性就崩了。遇到这种情况改用CASE WHEN语法会更清晰。4.2 CASE WHEN多分支判断的正确姿势CASE WHEN有两种写法。简单写法是CASE expr WHEN value THEN result ... END搜刮写法是CASE WHEN condition THEN result ... END。后者更灵活支持任意表达式。SELECT order_id, CASE WHEN amount 10000 THEN 大额订单 WHEN amount 1000 THEN 普通订单 ELSE 小额订单 END AS order_level FROM orders;CASE WHEN在报表统计里的用法很惊艳。配合聚合函数可以直接在一行里算出多个条件的计数SELECT COUNT(CASE WHEN status paid THEN 1 END) AS paid_count, COUNT(CASE WHEN status refunded THEN 1 END) AS refunded_count FROM orders;这里用到了COUNT只统计非NULL值的特性不满足条件时返回NULL不计入总数。另一种常见用法是行转列。比如每个用户有多个标签想按标签把用户摊平成多列SELECT user_id, MAX(CASE WHEN tag vip THEN 1 ELSE 0 END) AS is_vip, MAX(CASE WHEN tag new_user THEN 1 ELSE 0 END) AS is_new FROM user_tags GROUP BY user_id;MAX在这里巧妙地充当了“只要有任一满足条件即为 1”的聚合逻辑是 SQL 面试题里高频出现的考点。4.3 NULLIF被低估的防呆函数NULLIF(expr1, expr2)在 expr1 等于 expr2 时返回NULL否则返回 expr1。这个函数的价值在于制造“有意义的空值”。最典型的场景是除零保护。计算两个数的比值分母可能为 0直接除会报错。用NULLIF把分母的 0 转成NULL除法结果就自动变为NULL不会抛异常。SELECT numerator / NULLIF(denominator, 0) FROM calculation_table;另一个场景是数据对比。当两个字段值相同时我们可能不关心这条记录想让它在聚合结果里消失。用NULLIF配合COUNT可以做到。-- 统计有实际变更的记录数 SELECT COUNT(NULLIF(new_value, old_value)) FROM change_log;4.4 COALESCE多值兜底的最优解COALESCE(val1, val2, ..., valN)返回参数列表中第一个非NULL的值。如果所有参数都是NULL结果才是NULL。这个函数在处理多个可能为空的字段时比IFNULL优雅得多。比如用户有三个联系电话要取第一个能用的SELECT COALESCE(phone_primary, phone_secondary, phone_emergency) AS valid_phone FROM contacts;IFNULL只能处理两个参数COALESCE可以接任意多个是它的超集。从代码规范的角度多个字段兜底时一律用COALESCE。5. 分组聚合与窗口函数分析场景的高级玩法聚合函数人人会用但真到了复杂分析场景光靠GROUP BY加简单聚合是不够的。窗口函数能提供更多维度的分析能力而且写法上更清晰。5.1 聚合函数的隐藏特性COUNT 与 COUNT DISTINCTCOUNT(*)统计行数COUNT(column)统计该列非NULL的行数。COUNT(DISTINCT column)统计去重后的非NULL行数。这三个看起来差不多实际结果可能差很远。一个容易翻车的点COUNT(DISTINCT)处理多列时是统计“组合去重”后的数量不是分别去重再相加SELECT COUNT(DISTINCT user_id, status) FROM orders; -- 统计的是 (user_id, status) 的组合数量开发中想算“活跃用户数”但订单表里同一用户可能有多个状态不同的订单。如果直接COUNT(DISTINCT user_id, status)会把同一用户的不同状态当成多行导致人数虚高。要按用户去重只需要COUNT(DISTINCT user_id)。SUM和AVG会自动忽略NULL值。这通常是我们想要的行为但有时候NULL应该按 0 参与计算——得先用IFNULL兜底或者用COALESCE转换后再聚合。5.2 GROUP_CONCAT把多行压成一行GROUP_CONCAT(column)能把同一分组内的多个值拼接成一个字符串。这在生成逗号分隔的 ID 列表、逗号分隔的商品名称时非常实用。SELECT user_id, GROUP_CONCAT(product_name ORDER BY purchase_time SEPARATOR | ) FROM orders GROUP BY user_id;有三个参数可以控制行为ORDER BY指定拼接顺序SEPARATOR指定分隔符默认是逗号DISTINCT可以去重需要注意GROUP_CONCAT默认最大长度是 1024 字节。超过这个长度会被静默截断结果不完整。需要更长的拼接结果时可以调整会话变量SET SESSION group_concat_max_len 102400;但要注意这只是当前会话生效。放到生产环境里要么在连接初始化时设置要么谨慎使用这个函数别因为拼接整张表导致内存压力过大。5.3 窗口函数ROW_NUMBER、RANK 与 DENSE_RANKMySQL 8.0 开始支持窗口函数这是分析场景里非常强大的能力。ROW_NUMBER()为每一行生成一个唯一的序号RANK()和DENSE_RANK()用于排名区别在于并列时是否跳过序号。SELECT user_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num, RANK() OVER (ORDER BY amount DESC) AS rank_no, DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_no FROM orders;ROW_NUMBER最经典的用法是分组去重每组内保留一条。比如取每个用户最近的一笔订单SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 1;PARTITION BY相当于在窗口函数内部做分组ORDER BY决定组内排序。这个写法替代了老版本里“自连接 聚合”的复杂方案逻辑清楚也高效。LAG和LEAD可以访问同组内前一行或后一行的值做环比、同比分析非常顺手。比如计算每个用户连续两次订单的时间间隔SELECT order_id, user_id, create_time, LAG(create_time) OVER (PARTITION BY user_id ORDER BY create_time) AS prev_order_time FROM orders;拿到上一单时间后外层查询一减就是下单间隔。这在分析复购行为、用户活跃度时是高频操作。5.4 注意事项窗口函数与性能窗口函数效率并不低但在大数据集上仍然需要足够的排序和内存资源。使用时尽量让PARTITION BY的字段有索引同时避免在窗口函数里做计算量过大的排序表达式。另外窗口函数是在WHERE、GROUP BY、HAVING之后执行的。如果你先分组再算窗口顺序要搞清楚不然算出来的数值和自己想的不一样。6. 加密与哈希安全场景的常用防御用户密码、Token、签名校验……这些场景离不开加密和哈希函数。MySQL 内置的加密函数足以应付大部分常见需求。6.1 MD5 与 SHA 系列不可逆哈希MD5(str)返回 32 位十六进制字符串SHA1(str)返回 40 位SHA2(str, hash_length)可以指定SHA-224、SHA-256、SHA-384、SHA-512。SELECT MD5(password123); -- 输出: 482c811da5d5b4bc6d497ffc984cf6ad SELECT SHA2(password123, 256); -- 输出: 64 位十六进制字符串MD5 和 SHA1 目前已经被认为不安全不建议用于密码存储。新系统请直接使用SHA2配合加盐。密码存储有个重要细节数据库层面哈希函数只是工具真正决定安全性的还有加盐策略。把盐直接拼在密码后面再哈希能有效对抗彩虹表攻击。但不建议用固定的盐每行用户独立的盐更安全这通常需要在应用层实现。6.2 AES_ENCRYPT 与 AES_DECRYPT可逆加密AES_ENCRYPT(str, key)用 AES 算法加密字符串AES_DECRYPT(str, key)解密。适用于手机号、身份证号、银行卡号这类需要还原的敏感字段。-- 加密写入 INSERT INTO users (phone_encrypted) VALUES (AES_ENCRYPT(13800138000, my_secret_key)); -- 查询解密 SELECT AES_DECRYPT(phone_encrypted, my_secret_key) FROM users;用AES_ENCRYPT加密后的内容是二进制的不能直接存进VARCHAR字段容易产生乱码或被隐式转换截断。正确做法是存进VARBINARY或BLOB类型或者用HEX()转成十六进制字符串存储查询时再用UNHEX()还原。INSERT INTO users (phone_encrypted) VALUES (HEX(AES_ENCRYPT(13800138000, my_secret_key))); SELECT AES_DECRYPT(UNHEX(phone_encrypted), my_secret_key) FROM users;加密密钥不要硬编码在 SQL 里。理论上密钥管理和 SQL 执行应该分离但是实际小团队项目里容易随手把密钥写死在代码里然后一部署就是好几年。推一次密钥轮换时迁移成本令人头大。至少做到把密钥放到环境变量或配置中心别出现在代码仓库里。6.3 加密函数与性能开销加密解密是 CPU 密集型操作。如果整个表的敏感字段都加密查出来再逐行解密性能会明显下降。常规做法是只加密真正必须加密的字段或者加一层应用缓存避免每次查询都跑解密的逻辑。批量解密一次 10 万行身份证号耗时可能从几十毫秒膨胀到几秒。在设计查询链路时就得想清楚别把解密放在高频路径里。7. JSON 函数半结构化数据的处理利器MySQL 5.7 引入了 JSON 类型8.0 进一步增强了 JSON 函数的表达能力。很多业务场景里扩展字段直接用 JSON 存储查询时需要从 JSON 里提取字段、做过滤、聚合这些都可以靠 JSON 函数在 SQL 层完成。7.1 JSON_EXTRACT 与 - 运算符JSON_EXTRACT(json_doc, path)从 JSON 文档中提取指定路径的值。路径写法是$.key、$.array[0]这种形式。SELECT JSON_EXTRACT({name: 张三, age: 30}, $.name); -- 输出: 张三 (注意带双引号)-运算符是JSON_EXTRACT的简写-运算符合并了JSON_UNQUOTE直接返回去掉引号的文本值SELECT {name: 张三, age: 30}-$.name; -- 输出: 张三实际开发里订单表常有extra_info字段存 JSON比如用户备注、渠道来源、设备信息等。要统计不同渠道的订单量SELECT extra_info-$.channel AS channel, COUNT(*) FROM orders WHERE extra_info IS NOT NULL GROUP BY extra_info-$.channel;注意提取出来的 JSON 值类型。用-提取数字时返回的还是字符串需要比较数值可以加 0 做个隐式转换或者直接用CAST显式转换。7.2 JSON_CONTAINS 与 JSON 过滤查询JSON_CONTAINS(target, candidate)判断 target 是否包含 candidate。常用于数组类型 JSON 的包含关系判断。SELECT * FROM articles WHERE JSON_CONTAINS(tags_list, MySQL);这里的 JSON 数组字段存在tags_list中需要判断是否包含标签 MySQL。注意candidate必须是合法的 JSON 片段字符串得自己带引号容易漏写。更泛化的做法是直接取成员做判断SELECT * FROM articles WHERE MySQL MEMBER OF (tags_list);MEMBER OF运算符是 MySQL 8.0 引入的专门用来判断某个值是否存在于 JSON 数组中语义上更直观。7.3 JSON_TABLE把 JSON 拉平成表JSON_TABLE是 8.0 的大杀器可以把 JSON 数组展开成关系表的行。这意味着存储在 JSON 里的明细数据可以直接参与 SQL 的 JOIN 和聚合。SELECT * FROM orders, JSON_TABLE(extra_info, $[*] COLUMNS ( product_name VARCHAR(100) PATH $.name, quantity INT PATH $.qty )) AS items;这一下就把订单里嵌套的多个商品摊平了。之后可以对quantity做聚合、对product_name做过滤完全像操作普通表字段一样。JSON_TABLE的写法略微复杂但实际处理动态配置、复杂事件日志时能省掉大量应用层解析工作。我第一次在报表里把它跑通的时候确实觉得打开了新世界。7.4 JSON 函数使用注意事项JSON 字段虽然灵活但也不是没有代价。索引方面可以在生成的列上建索引或者用多值索引但无法像普通字段那样直接建常规索引。查询性能会受影响高频过滤的字段应该抽成独立列而不是全都塞进 JSON。写入时JSON_SET、JSON_INSERT、JSON_REPLACE能局部修改 JSON 字段但改动会触发行更新成本。频繁更新的场景JSON 不是最优解。8. 实战总结常见坑点与性能速查分享几个我在实际项目中踩过坑、也帮身边同事排查过的典型问题。8.1 隐式类型转换索引失效的头号元凶当字符串字段和数值比较时或者数值字段和字符串比较时MySQL 会自动转换类型。WHERE phone 13800138000这种写法如果phone是VARCHAR类型查询优化器可能会把字段隐式转成数值再比较导致字段上的索引失效。更典型的是日期函数搭配字符串比较。WHERE create_time 2025-01-01这种没问题因为字符串可以隐式转日期。但WHERE DATE(create_time) 2025-01-01这种左侧字段套了函数索引直接失效。正确写法是范围查询WHERE create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:008.2 字符集与排序规则一致性函数返回值、字符串比较、排序结果都和字符集、排序规则有关。两个不同字符集的字段做CONCAT或比较时可能报错或产生意外结果。建库建表时统一用utf8mb4能规避掉绝大多数多语言和兼容性问题。LENGTH()返回字节数CHAR_LENGTH()返回字符数。utf8mb4 下一个中文占 3 字节如果直接拿LENGTH判断长度结果会偏差。判断字符串实际字符数量始终用CHAR_LENGTH。8.3 几类函数的性能对比场景推荐方案不推荐方案日期范围查询字段和表达式直接比较字段套 DATE() 函数多字段拼接CONCAT_WS多个 CONCAT 嵌套多个空值兜底COALESCE多个 IFNULL 嵌套分组排名窗口函数 (8.0)自连接 子查询逗号分隔列表GROUP_CONCAT应用层循环拼接判断包含关系JSON_CONTAINS / MEMBER OFLIKE %xx%8.4 推荐的学习路径MySQL 函数本身难度不大重点在于用过之后理解边界。我建议的顺序是先把字符串、数值、日期三类函数过一遍写几个真实场景的 SQL 练习再掌握 CASE WHEN 和聚合函数的组合用法这个在报表场景里出现频率最高最后接触窗口函数能解决大部分分析需求。遇到不确定的语法细节比如某个函数的边界行为直接查官方文档对应版本的手册别靠猜。版本差异客观存在MySQL 5.7 和 8.0 在函数行为上有明显不同JSON 函数和窗口函数这两个大件就是典型例子。最后再分享一个小技巧写复杂 SQL 前先用SELECT单独验证函数返回值特别是日期解析、JSON 提取这类容易出错的地方。等确认函数行为正确了再推进到完整查询里。这一步看着麻烦实际上能省掉不少排查时间。