拓冰网站定制 
1. 为什么写到第七篇还要继续讲内置函数先跟大家说个题外话。熟悉我这个系列的朋友应该知道前面六篇我已经把MySQL内置函数里那些最常用的部分挨个过了一遍字符串函数、数值函数、日期时间函数、流程控制函数、聚合函数、类型转换和格式化函数。按理说日常写SQL够用了为什么还要单独挤出第七篇因为实际工作中我慢慢发现真正把你和别人拉开差距的往往是那些不常用但一旦用上就特别省事的函数。比如老板让你出一张每个品类销量Top3的报表你第一反应是不是用子查询加变量慢慢绕其实MySQL 8.0的窗口函数一行OVER()就解决了。再比如业务表里塞了一个JSON字段存用户扩展属性你想在里面捞一个值还在用正则劈里啪啦截取JSON_EXTRACT一个函数搞定。这些函数不冷门但很多写了好几年SQL的同学是真的没用顺手。所以这第七篇我打算聚焦几个方向窗口函数、JSON函数、正则相关函数、加密散列类函数外加系统信息类函数。它们不属于每天必用的范畴但都属于关键时刻能救命的类型。我会把每个函数的语法、适用场景、以及我在实际项目中踩过的坑都写出来大家可以直接照着抄。提示本篇所有代码基于MySQL 8.0.27验证MySQL 5.7部分函数不适用我会在文中单独标注。2. 窗口函数解决TopN、同比环比、连续登录这类老大难窗口函数是MySQL 8.0相对5.7最大的语法升级之一。如果你还在用5.7这部分内容可以先收藏等将来迁移8.0再回来翻但我建议尽早升级因为窗口函数解决的都是真实业务里最挠头的问题。2.1 窗口函数到底是什么我尽量用大白话解释。普通聚合函数比如SUM()、MAX()一聚合就把多行压缩成一行你再也看不到明细。窗口函数则是在每一行数据上基于一个窗口范围做计算但结果仍然保留每一行。这个窗口就是OVER()括号里定义的范围。最简单的写法SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee;这句话的意思是把数据按dept_id分组PARTITION BY组内按照salary降序排然后给每一行编一个序号。拿到这个序号之后你再在外面套一层查询取rn 3就是每个部门工资前三名。部门A和部门B的员工互不干扰各自从1开始编号。这就是分组内排序编号。2.2 ROW_NUMBER、RANK、DENSE_RANK到底怎么选这三个函数长得像用途差别很大面试也爱考。我直接用一个例子说清楚。假设一个班有四个学生成绩90、90、85、80。ROW_NUMBER()不管分数一样不一样硬编序号结果是1、2、3、4没有并列。RANK()分数相同则并列但下一个名次会跳过。结果是1、1、3、4。DENSE_RANK()分数相同并列但下一个名次不跳过。结果是1、1、2、3。我用一个表格对比函数成绩90的学生排名成绩85的学生排名成绩80的学生排名适用场景ROW_NUMBER1、234必须给每行唯一序号比如分页RANK1、134体育比赛排名允许跳名次DENSE_RANK1、123榜单排名不希望名次断层实际项目里取TopN用ROW_NUMBER最稳因为它不会因为并列导致返回超过N条数据。比如每个部门取工资最高的3个人如果两个人工资并列第一用RANK会返回4条用ROW_NUMBER就稳定返回3条。2.3 LAG和LEAD算环比、同比的利器环比、同比这类需求以前要么用自连接要么用子查询写起来又臭又长。窗口函数里的LAG()和LEAD()直接解决。LAG(column, n)取当前行往前数第n行的值。LEAD(column, n)取当前行往后数第n行的值。举个例子按月统计销售额算每个月比上个月增长了多少SELECT month_id, sales_amount, LAG(sales_amount, 1) OVER (ORDER BY month_id) AS last_month_sales, sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month_id) AS mom_change FROM monthly_sales;这里有个细节要提醒LAG()取不到值时会返回NULL比如第一行前面没有数据所以mom_change也会是NULL。实际报表里通常要处理一下用IFNULL把它变成0或者留空不然前端展示会多一个NULL字样容易闹笑话。LEAD()用得相对少但有个场景很经典判断用户是否连续两天登录。你只需要取每行日期之后的一行日期如果二者刚好相差一天就说明这个用户有连续登录行为。2.4 滑动窗口SUM配合ROWS BETWEEN窗口函数的进阶玩法是移动计算。比如计算最近30天的累计销售额或者近7天平均销量。语法核心是ROWS BETWEENSELECT sale_date, daily_amount, SUM(daily_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS last_7_days_total FROM daily_sales;这里6 PRECEDING AND CURRENT ROW就是从当前行往前推6行一直到当前行形成一个7行的滑动窗口。窗口会随着每一行往下移动每一行计算的都是它自己往前7天的合计。这个写法在统计移动平均、移动总和时非常高效不需要你手动去JOIN日期表。我个人的经验是滑动窗口一旦数据量超过几十万行性能会开始明显下滑因为每一行都要扫描一个窗口范围。这时候先缩小数据范围比如只取最近三个月再上窗口函数执行效率会好很多。2.5 窗口函数的坑MySQL 5.7及以下版本完全不支持窗口函数语法直接报错。只能在应用层排序编号或者用变量模拟。PARTITION BY后面不能直接跟聚合函数必须先分组聚合完再用窗口。窗口函数不能出现在WHERE子句中只能出现在SELECT列表或ORDER BY中。想筛选rn 1的记录必须嵌套一层子查询。如果表非常大建议在PARTITION BY的列上建索引否则全表排序带来的临时文件会让磁盘IO飙升。3. JSON函数业务表里那个万能扩展字段终于有救了现在很多业务表都爱设计一个extra或attributes字段类型是JSON往里塞各种乱七八糟的扩展属性。好处是表结构不用频繁加列坏处是查询的时候不知道怎么写SQL。MySQL从5.7开始支持JSON类型8.0又强化了一大批JSON函数我把最常用的几个串一遍。3.1 取JSON里的值JSON_EXTRACT与-、-的区别假设有一张用户表有个字段user_info存的是JSON{name: 张三, age: 28, address: {city: 上海, street: 南京路}, tags: [vip, 老用户]}要取出name字段的值有三种写法SELECT JSON_EXTRACT(user_info, $.name) FROM user_table; SELECT user_info-$.name FROM user_table; SELECT user_info-$.name FROM user_table;JSON_EXTRACT返回的是JSON类型注意结果会带双引号输出是张三带着引号。-是JSON_EXTRACT的简写结果也一样带引号。-是带引号剥离的简写返回的是纯字符串输出是张三。实际开发里最常用的是-因为它可以直接和普通字符串比较。比如SELECT * FROM user_table WHERE user_info-$.name 张三;我见过不少新手用-取出来的值跟字符串比较结果怎么都匹配不上最后发现是引号问题。记住-是给机器看的-才是给人看的。3.2 判断JSON里有没有某个keyJSON_CONTAINS和JSON_OVERLAPSJSON_CONTAINS用于判断一个JSON文档是否包含另一个指定的JSON值。比如判断tags数组里有没有vipSELECT * FROM user_table WHERE JSON_CONTAINS(user_info-$.tags, vip);注意第二参数必须是合法的JSON字符串字符串也要写成vip带双引号否则报错。这个细节很坑人。JSON_OVERLAPS是MySQL 8.0.17才引入的函数用于判断两个JSON数组是否有交集。比如找出tags同时包含vip和老用户的用户SELECT * FROM user_table WHERE JSON_OVERLAPS(user_info-$.tags, [vip, 老用户]);这个函数在处理多个标签筛选时特别好用不需要OR条件绕来绕去。3.3 修改JSON内容JSON_SET、JSON_INSERT、JSON_REPLACE这三个都是往JSON里塞值区别很微妙JSON_SET如果key存在就更新值不存在就新增key。JSON_INSERTkey存在则忽略不存在才新增。JSON_REPLACEkey存在才更新不存在则忽略。实际操作中我改JSON字段用得最多的是JSON_SET因为它的行为最符合直觉有则更新无则新增。比如UPDATE user_table SET user_info JSON_SET(user_info, $.age, 29, $.level, gold) WHERE id 1;这个SQL会同时把age改成29如果原本没有level这个键就新增一个。一句SQL搞定多个键的更新不用先查出来再拼字符串很方便。3.4 JSON_ARRAYAGG和JSON_OBJECTAGG行转JSON5.7开始提供JSON_ARRAYAGG函数作用是把多行数据聚合成一个JSON数组。比如统计每个部门的员工姓名列表SELECT dept_id, JSON_ARRAYAGG(emp_name) FROM employee GROUP BY dept_id;结果类似1部门返回[张三, 李四, 王五]。JSON_OBJECTAGG则是把多行聚合成JSON对象比如统计每个部门的平均工资然后用部门ID作为key、平均值作为value输出。这两个函数在做报表接口时非常有用。以前你要么在Java里循环拼接要么在MySQL里用GROUP_CONCAT自己拼JSON字符串后者如果值里有特殊字符就很容易拼坏。JSON_ARRAYAGG和JSON_OBJECTAGG是真正意义上数据库原生生成JSON安全省心。3.5 深度查询JSON_TABLE把JSON展开成行JSON_TABLE这个函数稍微硬核但真的是神器。它能把一个JSON数组展开成一张虚拟表方便你JOIN查询。比如用户表里有一个orders数组存着用户的多个订单你想把每个订单都展开成一行和用户主表关联查询。SELECT u.id, o.order_id, o.amount FROM user_table u, JSON_TABLE( u.orders, $[*] COLUMNS ( order_id VARCHAR(50) PATH $.order_id, amount DECIMAL(10,2) PATH $.amount ) ) AS o;这个写法把同一个用户的所有订单都拉平了一行一个订单方便后续做聚合统计。我第一次用的时候感觉像打开了新世界的大门原来JSON数据也能像普通表一样JOIN和GROUP BY。3.6 JSON函数的坑JSON字段必须保证是合法JSON否则插入时直接报错。所以程序端往这个字段写值的时候一定要先JSON.stringify再入库。JSON列不支持直接建普通索引但MySQL支持为JSON列里某个key生成虚拟列然后在虚拟列上建索引。常用写法是ALTER TABLE user_table ADD COLUMN user_age INT GENERATED ALWAYS AS (user_info-$.age), ADD INDEX idx_user_age (user_age);这个技巧能大幅提升按JSON属性查询的速度强烈建议用起来。 3. 不要在WHERE条件里对一个很大的JSON字段做路径查询比如JSON_EXTRACT(extra, $.a.b.c.d) xx。这种查询无法走索引只能全表扫数据量一大就悲剧。4. 正则函数数据清洗时的瑞士军刀MySQL一直支持正则匹配但8.0之前只有REGEXP运算符功能弱且没有替换、提取的能力。8.0开始补齐了四个正则函数REGEXP_LIKE、REGEXP_INSTR、REGEXP_REPLACE、REGEXP_SUBSTR。做数据清洗的时候这几个函数比字符串函数灵活得多。4.1 REGEXP_LIKE判断是否匹配其实和REGEXP用法类似都是返回布尔值。比如找出所有手机号开头是138或139的用户SELECT * FROM user_table WHERE REGEXP_LIKE(phone, ^13[89][0-9]{8}$);注意手机号的^和$必须写清楚否则你会把138123456789这串多一位的数也匹配进来。正则这东西边界条件不写明白很容易出事。4.2 REGEXP_REPLACE替换匹配到的部分做数据脱敏时REPLACE是首选。比如把手机号中间四位打码SELECT REGEXP_REPLACE(phone, ^([0-9]{3})[0-9]{4}([0-9]{4})$, \\1****\\2) AS masked_phone FROM user_table;这里\1和\2是反向引用分别代表第一组括号和第二组括号匹配到的内容。SQL里写正则转义很绕因为MySQL本身把\当作转义字符所以写\1要写成\1。这个细节我调试了很久才发现。再比如清洗脏数据把字符串里所有非数字字符去掉SELECT REGEXP_REPLACE(abc123def456, [^0-9], ) AS clean_str;一行就搞定比LPAD、SUBSTRING这种硬抠字符串的方式优雅太多了。数据清洗脚本里我经常用这个函数。4.3 REGEXP_SUBSTR和REGEXP_INSTR提取子串和定位REGEXP_SUBSTR用于提取第一个匹配的子串。比如从一段文本里提取邮箱SELECT REGEXP_SUBSTR(content, [a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}) AS email FROM message_table;REGEXP_INSTR更像POSITION返回匹配位置的索引值。用得少但如果你需要判断一个字符串中某个模式出现的具体位置用它比LOCATE灵活。我个人的建议是如果只是简单的包含判断用LIKE就行别上正则。正则虽然强大但执行效率远不如普通的LIKE前缀匹配。当数据量在百万级别以上时滥用正则查询会让数据库很难受。正则适合在数据清洗阶段用不适合在高频查询链路里用。4.4 正则函数的坑正则函数无法使用索引不论怎么优化都是全表扫描。只适合小表或临时清洗。MySQL的正则默认是大小写不敏感的。如果需要区分大小写可以在模式前面加(?-i)或用BINARY强制二进制比较。正则引擎在MySQL中的实现和PCRE有一些差异写复杂模式之前建议先在本地用工具验证一遍避免线上跑一次发现和预期不符。5. 加密与散列函数别再把密码存成MD5了安全类函数平时用得不多但一旦涉及用户密码、敏感数据传输很多人容易用错。这里重点讲几个常见函数以及我踩过的坑。5.1 MD5、SHA1、SHA2的基本用法SELECT MD5(abc); -- 900150983cd24fb0d6963f7d28e17f72 SELECT SHA1(abc); -- a9993e364706816aba3e25717850c26c9cd0d89d SELECT SHA2(abc, 256); -- ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad这三个函数把字符串变成定长的十六进制散列值。MD5长度32SHA1长度40SHA2根据第二个参数支持224、256、384、512位。注意MD5和SHA1已经被证明存在碰撞风险做数据完整性校验都勉强更不能用来存密码。现在主流做法是SHA2系列推荐用SHA2(..., 256)及以上。5.2 存密码的正确姿势加盐用慢哈希这里我必须多说一句。很多教程会让你把用户密码直接MD5存储这是非常危险的做法。因为网上有现成的彩虹表MD5(admin123)是固定值一查就破。正确的思路是密码加一个随机盐salt再算散列。使用bcrypt、scrypt这类慢哈希算法让每次计算都耗时几十毫秒增大暴力破解成本。MySQL内置函数里没有直接的bcrypt实现通常用应用层代码完成。如果你确实非要在数据库层做可以用SHA2配合随机盐虽然不是最优但比裸MD5强不少。比如创建一个临时盐并拼接SET salt abcdef123456; SET password user_input_password; SELECT SHA2(CONCAT(salt, password), 256);散列值每次都不一样前提是每一个用户都配一个独立的盐而不是所有用户同一个盐。5.3 AES_ENCRYPT和AES_DECRYPT对称加密如果业务需要对某些字段做对称加密存储MySQL提供了AES_ENCRYPT和AES_DECRYPT函数。用法如下SET key my_secret_key_16b; INSERT INTO secure_table (raw_data, enc_data) VALUES (这是一个敏感信息, AES_ENCRYPT(这是一个敏感信息, key)); SELECT AES_DECRYPT(enc_data, key) FROM secure_table;注意默认的块加密模式是aes-128-ecb同样的明文对应的密文相同安全性一般。想用更安全的模式需要设置block_encryption_modeSET block_encryption_mode aes-256-cbc; SET key my_key_32_bytes_length...;但有个坑设置block_encryption_mode是会话级别的。也就是说如果应用A用cbc模式加密应用B用ecb模式解密结果完全乱码。生产环境必须保证所有节点、所有会话都统一设置。这个容易在连接池里踩坑因为连接池会复用会话不同会话的配置可能不一致。AES_DECRYPT解密失败时一般返回NULL而不是报错。所以实际项目里要结合业务判断一下解出来是不是NULL别把NULL当真值存回去。5.4 HEX和UNHEX二进制与十六进制互转这两个函数单独看很简单但配合上面说的加密函数特别好用。因为AES_ENCRYPT返回的是二进制数据如果你直接存进VARCHAR字段写到数据库、再读出来经常会看到一堆乱码。正确做法是INSERT INTO t (enc_data) VALUES (HEX(AES_ENCRYPT(原始数据, key))); SELECT AES_DECRYPT(UNHEX(enc_data), key) FROM t;先把密文转成十六进制字符串存储读取时先UNHEX还原成二进制再解密。这个操作虽然多一步但是避免了字符集转换导致的乱码问题。我当年第一次做字段加密就是没加HEX结果数据入库后怎么解都不对查了好久才发现二进制和字符集在搞鬼。5.5 RAND和UUID相关的坑顺带提一下生成随机数的函数RAND()生成0到1之间的随机数和ORDER BY RAND()配合可以随机排序但数据量大时非常慢因为它要给每一行算一次随机值再排序。想要随机取几条记录别用ORDER BY RAND() LIMIT n效率烂得离谱。可以先用COUNT(*)算总行数再用随机数取偏移量然后LIMIT 1。UUID()函数生成全局唯一标识但UUID()生成的字符串是无序的如果直接当主键会导致索引频繁分裂写入性能暴跌。MySQL 8.0的UUID_TO_BIN和BIN_TO_UUID可以把UUID转换为二进制有序存储同时保持可读性。这是8.0新增的一对好函数主键设计为UUID且对性能有要求的场景建议研究一下。6. 系统信息与控制类函数写通用脚本时的小帮手最后讲一类不太起眼但很实用的函数——系统信息函数和部分控制类函数。这类函数在写运维脚本、通用工具SQL时经常用到。6.1 VERSION、DATABASE、USER、CONNECTION_IDVERSION()返回当前MySQL版本号比如8.0.27。DATABASE()返回当前默认数据库名如果没有选择数据库则返回NULL。USER()和CURRENT_USER()返回当前用户名和主机。USER()返回你连接时用的账号CURRENT_USER()返回MySQL真正认证时的账号。两者在用了代理用户时会有差异。CONNECTION_ID()返回当前连接的ID。这个值在排查慢查询、定位堵塞源时很有用结合performance_schema可以精确定位到某一条出问题的连接。写通用脚本时DATABASE()非常实用。比如你想在程序里动态拼接表名就得靠它判断当前连的是哪个库避免跨库误操作。6.2 LAST_INSERT_ID插入之后马上拿自增主键很多人在ORM层面已经习惯了insert后自动返回主键ID但如果你直接写原生SQL或者在存储过程里操作LAST_INSERT_ID()就是你的救命函数。INSERT INTO user_table (name) VALUES (张三); SELECT LAST_INSERT_ID();这里有个非常容易踩的坑如果一次插入多条记录LAST_INSERT_ID()返回的是第一条生成的自增值不是最后一条。而且如果插入的表没有自增主键返回值是0。还有一点LAST_INSERT_ID()是基于连接的也就是说它只对当前会话有效不同的连接互不影响。绝对不能在一个连接里插入然后在另一个连接里查这个函数。6.3 FORMAT、LPAD、RPAD报表展示必备FORMAT(x, d)把数字格式化为带千分位的字符串比如FORMAT(1234567.891, 2)返回1,234,567.89。注意返回的是字符串不是数值不能再直接做加减运算必须先转回数字。LPAD(str, len, padstr)和RPAD(str, len, padstr)在字符串左边或右边填充指定字符到指定长度。比如把订单号补成固定宽度8位LPAD(order_no, 8, 0)。这些函数在报表导出场景中特别常见。比如导出的Excel要求金额带千分位、编号固定长度直接在SQL里格式化好后端就省不少事。但format完的数字和原字段排序会不一致因为它是字符串排序这个要注意。6.4 CAST和CONVERT类型转换的隐性坑CAST和CONVERT都是类型转换函数我前面系列讲过这里只强调两个特别容易翻车的点CAST(字符串 AS SIGNED)转数字时如果字符串以数字开头MySQL会截断转换比如CAST(123abc AS SIGNED)返回123不会报错。这在数据清洗时可能掩盖脏数据问题。你以为是123实际原值是123abc后面同步到报表时对不上账排查半天才发现是这里截断了。日期转换格式有严格限制。CAST(2023-02-30 AS DATE)不会报错MySQL会自动修正为2023-02-28。这种静默修正非常危险一旦业务数据里出现非法日期你需要及时发现而不是让它悄悄被修掉。6.5 流程控制函数补充IF、IFNULL、NULLIF这几类函数前面讲过但我在第七篇里想补充一个容易被忽视的NULLIFSELECT NULLIF(a, b);如果a等于b则返回NULL否则返回a。这在实际场景里很好用比如防止除数为零SELECT total_amount / NULLIF(order_count, 0) AS avg_order_amount FROM sales;当order_count是0时NULLIF把它转成NULL除法的结果就是NULL而不是报错ERROR 1365或返回无穷值。比用IF(condition, ...)简单清爽。7. 最后一篇我这几年用内置函数的几条实在体会系列写到第七篇很多函数都讲完了。最后聊几句我的真实感受。第一遇到问题先别急着在程序里做复杂循环。SQL层面多查一下函数文档八成有现成的解决方案。窗口函数、JSON函数这类特性早一天学会早一天从用拼接字符串模拟复杂逻辑的苦海里解放出来。我自己就是活生生的例子以前做报表能用子查询嵌套五层硬算后来换成窗口函数不仅代码短执行计划也干净得多线上慢查询直接少了一半。第二内置函数用起来有三个原则能用原生函数处理的不要丢到程序里处理能用索引解决的不要在函数上折腾需要用函数的优先考虑执行效率而不是一味追求写法的炫技。比如正则函数确实好用但你自己心里要有一根弦它不走索引量大的时候要控制使用范围。第三JSON字段和加密字段这类非传统数据一定要做好写入侧的数据校验程序别指望数据库帮你兜底。JSON非法、密文乱码这种问题一旦发生排查成本远高于在写入前做一次校验的成本。第七篇就写到这里。下一篇我打算整理一套MySQL常用函数速查表把前面七篇涉及的所有函数汇总成一份能直接打印的参考文档到时候大家放到手边查起来方便。如果你在项目里遇到什么冷门函数或者奇葩用法欢迎在评论区聊聊我看到了都会回复。 MySQL内置函数进阶指南：窗口函数、JSON处理与加密实战
1. 为什么写到第七篇还要继续讲内置函数先跟大家说个题外话。熟悉我这个系列的朋友应该知道前面六篇我已经把MySQL内置函数里那些最常用的部分挨个过了一遍字符串函数、数值函数、日期时间函数、流程控制函数、聚合函数、类型转换和格式化函数。按理说日常写SQL够用了为什么还要单独挤出第七篇因为实际工作中我慢慢发现真正把你和别人拉开差距的往往是那些不常用但一旦用上就特别省事的函数。比如老板让你出一张每个品类销量Top3的报表你第一反应是不是用子查询加变量慢慢绕其实MySQL 8.0的窗口函数一行OVER()就解决了。再比如业务表里塞了一个JSON字段存用户扩展属性你想在里面捞一个值还在用正则劈里啪啦截取JSON_EXTRACT一个函数搞定。这些函数不冷门但很多写了好几年SQL的同学是真的没用顺手。所以这第七篇我打算聚焦几个方向窗口函数、JSON函数、正则相关函数、加密散列类函数外加系统信息类函数。它们不属于每天必用的范畴但都属于关键时刻能救命的类型。我会把每个函数的语法、适用场景、以及我在实际项目中踩过的坑都写出来大家可以直接照着抄。提示本篇所有代码基于MySQL 8.0.27验证MySQL 5.7部分函数不适用我会在文中单独标注。2. 窗口函数解决TopN、同比环比、连续登录这类老大难窗口函数是MySQL 8.0相对5.7最大的语法升级之一。如果你还在用5.7这部分内容可以先收藏等将来迁移8.0再回来翻但我建议尽早升级因为窗口函数解决的都是真实业务里最挠头的问题。2.1 窗口函数到底是什么我尽量用大白话解释。普通聚合函数比如SUM()、MAX()一聚合就把多行压缩成一行你再也看不到明细。窗口函数则是在每一行数据上基于一个窗口范围做计算但结果仍然保留每一行。这个窗口就是OVER()括号里定义的范围。最简单的写法SELECT emp_name, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee;这句话的意思是把数据按dept_id分组PARTITION BY组内按照salary降序排然后给每一行编一个序号。拿到这个序号之后你再在外面套一层查询取rn 3就是每个部门工资前三名。部门A和部门B的员工互不干扰各自从1开始编号。这就是分组内排序编号。2.2 ROW_NUMBER、RANK、DENSE_RANK到底怎么选这三个函数长得像用途差别很大面试也爱考。我直接用一个例子说清楚。假设一个班有四个学生成绩90、90、85、80。ROW_NUMBER()不管分数一样不一样硬编序号结果是1、2、3、4没有并列。RANK()分数相同则并列但下一个名次会跳过。结果是1、1、3、4。DENSE_RANK()分数相同并列但下一个名次不跳过。结果是1、1、2、3。我用一个表格对比函数成绩90的学生排名成绩85的学生排名成绩80的学生排名适用场景ROW_NUMBER1、234必须给每行唯一序号比如分页RANK1、134体育比赛排名允许跳名次DENSE_RANK1、123榜单排名不希望名次断层实际项目里取TopN用ROW_NUMBER最稳因为它不会因为并列导致返回超过N条数据。比如每个部门取工资最高的3个人如果两个人工资并列第一用RANK会返回4条用ROW_NUMBER就稳定返回3条。2.3 LAG和LEAD算环比、同比的利器环比、同比这类需求以前要么用自连接要么用子查询写起来又臭又长。窗口函数里的LAG()和LEAD()直接解决。LAG(column, n)取当前行往前数第n行的值。LEAD(column, n)取当前行往后数第n行的值。举个例子按月统计销售额算每个月比上个月增长了多少SELECT month_id, sales_amount, LAG(sales_amount, 1) OVER (ORDER BY month_id) AS last_month_sales, sales_amount - LAG(sales_amount, 1) OVER (ORDER BY month_id) AS mom_change FROM monthly_sales;这里有个细节要提醒LAG()取不到值时会返回NULL比如第一行前面没有数据所以mom_change也会是NULL。实际报表里通常要处理一下用IFNULL把它变成0或者留空不然前端展示会多一个NULL字样容易闹笑话。LEAD()用得相对少但有个场景很经典判断用户是否连续两天登录。你只需要取每行日期之后的一行日期如果二者刚好相差一天就说明这个用户有连续登录行为。2.4 滑动窗口SUM配合ROWS BETWEEN窗口函数的进阶玩法是移动计算。比如计算最近30天的累计销售额或者近7天平均销量。语法核心是ROWS BETWEENSELECT sale_date, daily_amount, SUM(daily_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS last_7_days_total FROM daily_sales;这里6 PRECEDING AND CURRENT ROW就是从当前行往前推6行一直到当前行形成一个7行的滑动窗口。窗口会随着每一行往下移动每一行计算的都是它自己往前7天的合计。这个写法在统计移动平均、移动总和时非常高效不需要你手动去JOIN日期表。我个人的经验是滑动窗口一旦数据量超过几十万行性能会开始明显下滑因为每一行都要扫描一个窗口范围。这时候先缩小数据范围比如只取最近三个月再上窗口函数执行效率会好很多。2.5 窗口函数的坑MySQL 5.7及以下版本完全不支持窗口函数语法直接报错。只能在应用层排序编号或者用变量模拟。PARTITION BY后面不能直接跟聚合函数必须先分组聚合完再用窗口。窗口函数不能出现在WHERE子句中只能出现在SELECT列表或ORDER BY中。想筛选rn 1的记录必须嵌套一层子查询。如果表非常大建议在PARTITION BY的列上建索引否则全表排序带来的临时文件会让磁盘IO飙升。3. JSON函数业务表里那个万能扩展字段终于有救了现在很多业务表都爱设计一个extra或attributes字段类型是JSON往里塞各种乱七八糟的扩展属性。好处是表结构不用频繁加列坏处是查询的时候不知道怎么写SQL。MySQL从5.7开始支持JSON类型8.0又强化了一大批JSON函数我把最常用的几个串一遍。3.1 取JSON里的值JSON_EXTRACT与-、-的区别假设有一张用户表有个字段user_info存的是JSON{name: 张三, age: 28, address: {city: 上海, street: 南京路}, tags: [vip, 老用户]}要取出name字段的值有三种写法SELECT JSON_EXTRACT(user_info, $.name) FROM user_table; SELECT user_info-$.name FROM user_table; SELECT user_info-$.name FROM user_table;JSON_EXTRACT返回的是JSON类型注意结果会带双引号输出是张三带着引号。-是JSON_EXTRACT的简写结果也一样带引号。-是带引号剥离的简写返回的是纯字符串输出是张三。实际开发里最常用的是-因为它可以直接和普通字符串比较。比如SELECT * FROM user_table WHERE user_info-$.name 张三;我见过不少新手用-取出来的值跟字符串比较结果怎么都匹配不上最后发现是引号问题。记住-是给机器看的-才是给人看的。3.2 判断JSON里有没有某个keyJSON_CONTAINS和JSON_OVERLAPSJSON_CONTAINS用于判断一个JSON文档是否包含另一个指定的JSON值。比如判断tags数组里有没有vipSELECT * FROM user_table WHERE JSON_CONTAINS(user_info-$.tags, vip);注意第二参数必须是合法的JSON字符串字符串也要写成vip带双引号否则报错。这个细节很坑人。JSON_OVERLAPS是MySQL 8.0.17才引入的函数用于判断两个JSON数组是否有交集。比如找出tags同时包含vip和老用户的用户SELECT * FROM user_table WHERE JSON_OVERLAPS(user_info-$.tags, [vip, 老用户]);这个函数在处理多个标签筛选时特别好用不需要OR条件绕来绕去。3.3 修改JSON内容JSON_SET、JSON_INSERT、JSON_REPLACE这三个都是往JSON里塞值区别很微妙JSON_SET如果key存在就更新值不存在就新增key。JSON_INSERTkey存在则忽略不存在才新增。JSON_REPLACEkey存在才更新不存在则忽略。实际操作中我改JSON字段用得最多的是JSON_SET因为它的行为最符合直觉有则更新无则新增。比如UPDATE user_table SET user_info JSON_SET(user_info, $.age, 29, $.level, gold) WHERE id 1;这个SQL会同时把age改成29如果原本没有level这个键就新增一个。一句SQL搞定多个键的更新不用先查出来再拼字符串很方便。3.4 JSON_ARRAYAGG和JSON_OBJECTAGG行转JSON5.7开始提供JSON_ARRAYAGG函数作用是把多行数据聚合成一个JSON数组。比如统计每个部门的员工姓名列表SELECT dept_id, JSON_ARRAYAGG(emp_name) FROM employee GROUP BY dept_id;结果类似1部门返回[张三, 李四, 王五]。JSON_OBJECTAGG则是把多行聚合成JSON对象比如统计每个部门的平均工资然后用部门ID作为key、平均值作为value输出。这两个函数在做报表接口时非常有用。以前你要么在Java里循环拼接要么在MySQL里用GROUP_CONCAT自己拼JSON字符串后者如果值里有特殊字符就很容易拼坏。JSON_ARRAYAGG和JSON_OBJECTAGG是真正意义上数据库原生生成JSON安全省心。3.5 深度查询JSON_TABLE把JSON展开成行JSON_TABLE这个函数稍微硬核但真的是神器。它能把一个JSON数组展开成一张虚拟表方便你JOIN查询。比如用户表里有一个orders数组存着用户的多个订单你想把每个订单都展开成一行和用户主表关联查询。SELECT u.id, o.order_id, o.amount FROM user_table u, JSON_TABLE( u.orders, $[*] COLUMNS ( order_id VARCHAR(50) PATH $.order_id, amount DECIMAL(10,2) PATH $.amount ) ) AS o;这个写法把同一个用户的所有订单都拉平了一行一个订单方便后续做聚合统计。我第一次用的时候感觉像打开了新世界的大门原来JSON数据也能像普通表一样JOIN和GROUP BY。3.6 JSON函数的坑JSON字段必须保证是合法JSON否则插入时直接报错。所以程序端往这个字段写值的时候一定要先JSON.stringify再入库。JSON列不支持直接建普通索引但MySQL支持为JSON列里某个key生成虚拟列然后在虚拟列上建索引。常用写法是ALTER TABLE user_table ADD COLUMN user_age INT GENERATED ALWAYS AS (user_info-$.age), ADD INDEX idx_user_age (user_age);这个技巧能大幅提升按JSON属性查询的速度强烈建议用起来。 3. 不要在WHERE条件里对一个很大的JSON字段做路径查询比如JSON_EXTRACT(extra, $.a.b.c.d) xx。这种查询无法走索引只能全表扫数据量一大就悲剧。4. 正则函数数据清洗时的瑞士军刀MySQL一直支持正则匹配但8.0之前只有REGEXP运算符功能弱且没有替换、提取的能力。8.0开始补齐了四个正则函数REGEXP_LIKE、REGEXP_INSTR、REGEXP_REPLACE、REGEXP_SUBSTR。做数据清洗的时候这几个函数比字符串函数灵活得多。4.1 REGEXP_LIKE判断是否匹配其实和REGEXP用法类似都是返回布尔值。比如找出所有手机号开头是138或139的用户SELECT * FROM user_table WHERE REGEXP_LIKE(phone, ^13[89][0-9]{8}$);注意手机号的^和$必须写清楚否则你会把138123456789这串多一位的数也匹配进来。正则这东西边界条件不写明白很容易出事。4.2 REGEXP_REPLACE替换匹配到的部分做数据脱敏时REPLACE是首选。比如把手机号中间四位打码SELECT REGEXP_REPLACE(phone, ^([0-9]{3})[0-9]{4}([0-9]{4})$, \\1****\\2) AS masked_phone FROM user_table;这里\1和\2是反向引用分别代表第一组括号和第二组括号匹配到的内容。SQL里写正则转义很绕因为MySQL本身把\当作转义字符所以写\1要写成\1。这个细节我调试了很久才发现。再比如清洗脏数据把字符串里所有非数字字符去掉SELECT REGEXP_REPLACE(abc123def456, [^0-9], ) AS clean_str;一行就搞定比LPAD、SUBSTRING这种硬抠字符串的方式优雅太多了。数据清洗脚本里我经常用这个函数。4.3 REGEXP_SUBSTR和REGEXP_INSTR提取子串和定位REGEXP_SUBSTR用于提取第一个匹配的子串。比如从一段文本里提取邮箱SELECT REGEXP_SUBSTR(content, [a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}) AS email FROM message_table;REGEXP_INSTR更像POSITION返回匹配位置的索引值。用得少但如果你需要判断一个字符串中某个模式出现的具体位置用它比LOCATE灵活。我个人的建议是如果只是简单的包含判断用LIKE就行别上正则。正则虽然强大但执行效率远不如普通的LIKE前缀匹配。当数据量在百万级别以上时滥用正则查询会让数据库很难受。正则适合在数据清洗阶段用不适合在高频查询链路里用。4.4 正则函数的坑正则函数无法使用索引不论怎么优化都是全表扫描。只适合小表或临时清洗。MySQL的正则默认是大小写不敏感的。如果需要区分大小写可以在模式前面加(?-i)或用BINARY强制二进制比较。正则引擎在MySQL中的实现和PCRE有一些差异写复杂模式之前建议先在本地用工具验证一遍避免线上跑一次发现和预期不符。5. 加密与散列函数别再把密码存成MD5了安全类函数平时用得不多但一旦涉及用户密码、敏感数据传输很多人容易用错。这里重点讲几个常见函数以及我踩过的坑。5.1 MD5、SHA1、SHA2的基本用法SELECT MD5(abc); -- 900150983cd24fb0d6963f7d28e17f72 SELECT SHA1(abc); -- a9993e364706816aba3e25717850c26c9cd0d89d SELECT SHA2(abc, 256); -- ba7816bf8f01cfea414140de5dae2223b00361a396177a9cb410ff61f20015ad这三个函数把字符串变成定长的十六进制散列值。MD5长度32SHA1长度40SHA2根据第二个参数支持224、256、384、512位。注意MD5和SHA1已经被证明存在碰撞风险做数据完整性校验都勉强更不能用来存密码。现在主流做法是SHA2系列推荐用SHA2(..., 256)及以上。5.2 存密码的正确姿势加盐用慢哈希这里我必须多说一句。很多教程会让你把用户密码直接MD5存储这是非常危险的做法。因为网上有现成的彩虹表MD5(admin123)是固定值一查就破。正确的思路是密码加一个随机盐salt再算散列。使用bcrypt、scrypt这类慢哈希算法让每次计算都耗时几十毫秒增大暴力破解成本。MySQL内置函数里没有直接的bcrypt实现通常用应用层代码完成。如果你确实非要在数据库层做可以用SHA2配合随机盐虽然不是最优但比裸MD5强不少。比如创建一个临时盐并拼接SET salt abcdef123456; SET password user_input_password; SELECT SHA2(CONCAT(salt, password), 256);散列值每次都不一样前提是每一个用户都配一个独立的盐而不是所有用户同一个盐。5.3 AES_ENCRYPT和AES_DECRYPT对称加密如果业务需要对某些字段做对称加密存储MySQL提供了AES_ENCRYPT和AES_DECRYPT函数。用法如下SET key my_secret_key_16b; INSERT INTO secure_table (raw_data, enc_data) VALUES (这是一个敏感信息, AES_ENCRYPT(这是一个敏感信息, key)); SELECT AES_DECRYPT(enc_data, key) FROM secure_table;注意默认的块加密模式是aes-128-ecb同样的明文对应的密文相同安全性一般。想用更安全的模式需要设置block_encryption_modeSET block_encryption_mode aes-256-cbc; SET key my_key_32_bytes_length...;但有个坑设置block_encryption_mode是会话级别的。也就是说如果应用A用cbc模式加密应用B用ecb模式解密结果完全乱码。生产环境必须保证所有节点、所有会话都统一设置。这个容易在连接池里踩坑因为连接池会复用会话不同会话的配置可能不一致。AES_DECRYPT解密失败时一般返回NULL而不是报错。所以实际项目里要结合业务判断一下解出来是不是NULL别把NULL当真值存回去。5.4 HEX和UNHEX二进制与十六进制互转这两个函数单独看很简单但配合上面说的加密函数特别好用。因为AES_ENCRYPT返回的是二进制数据如果你直接存进VARCHAR字段写到数据库、再读出来经常会看到一堆乱码。正确做法是INSERT INTO t (enc_data) VALUES (HEX(AES_ENCRYPT(原始数据, key))); SELECT AES_DECRYPT(UNHEX(enc_data), key) FROM t;先把密文转成十六进制字符串存储读取时先UNHEX还原成二进制再解密。这个操作虽然多一步但是避免了字符集转换导致的乱码问题。我当年第一次做字段加密就是没加HEX结果数据入库后怎么解都不对查了好久才发现二进制和字符集在搞鬼。5.5 RAND和UUID相关的坑顺带提一下生成随机数的函数RAND()生成0到1之间的随机数和ORDER BY RAND()配合可以随机排序但数据量大时非常慢因为它要给每一行算一次随机值再排序。想要随机取几条记录别用ORDER BY RAND() LIMIT n效率烂得离谱。可以先用COUNT(*)算总行数再用随机数取偏移量然后LIMIT 1。UUID()函数生成全局唯一标识但UUID()生成的字符串是无序的如果直接当主键会导致索引频繁分裂写入性能暴跌。MySQL 8.0的UUID_TO_BIN和BIN_TO_UUID可以把UUID转换为二进制有序存储同时保持可读性。这是8.0新增的一对好函数主键设计为UUID且对性能有要求的场景建议研究一下。6. 系统信息与控制类函数写通用脚本时的小帮手最后讲一类不太起眼但很实用的函数——系统信息函数和部分控制类函数。这类函数在写运维脚本、通用工具SQL时经常用到。6.1 VERSION、DATABASE、USER、CONNECTION_IDVERSION()返回当前MySQL版本号比如8.0.27。DATABASE()返回当前默认数据库名如果没有选择数据库则返回NULL。USER()和CURRENT_USER()返回当前用户名和主机。USER()返回你连接时用的账号CURRENT_USER()返回MySQL真正认证时的账号。两者在用了代理用户时会有差异。CONNECTION_ID()返回当前连接的ID。这个值在排查慢查询、定位堵塞源时很有用结合performance_schema可以精确定位到某一条出问题的连接。写通用脚本时DATABASE()非常实用。比如你想在程序里动态拼接表名就得靠它判断当前连的是哪个库避免跨库误操作。6.2 LAST_INSERT_ID插入之后马上拿自增主键很多人在ORM层面已经习惯了insert后自动返回主键ID但如果你直接写原生SQL或者在存储过程里操作LAST_INSERT_ID()就是你的救命函数。INSERT INTO user_table (name) VALUES (张三); SELECT LAST_INSERT_ID();这里有个非常容易踩的坑如果一次插入多条记录LAST_INSERT_ID()返回的是第一条生成的自增值不是最后一条。而且如果插入的表没有自增主键返回值是0。还有一点LAST_INSERT_ID()是基于连接的也就是说它只对当前会话有效不同的连接互不影响。绝对不能在一个连接里插入然后在另一个连接里查这个函数。6.3 FORMAT、LPAD、RPAD报表展示必备FORMAT(x, d)把数字格式化为带千分位的字符串比如FORMAT(1234567.891, 2)返回1,234,567.89。注意返回的是字符串不是数值不能再直接做加减运算必须先转回数字。LPAD(str, len, padstr)和RPAD(str, len, padstr)在字符串左边或右边填充指定字符到指定长度。比如把订单号补成固定宽度8位LPAD(order_no, 8, 0)。这些函数在报表导出场景中特别常见。比如导出的Excel要求金额带千分位、编号固定长度直接在SQL里格式化好后端就省不少事。但format完的数字和原字段排序会不一致因为它是字符串排序这个要注意。6.4 CAST和CONVERT类型转换的隐性坑CAST和CONVERT都是类型转换函数我前面系列讲过这里只强调两个特别容易翻车的点CAST(字符串 AS SIGNED)转数字时如果字符串以数字开头MySQL会截断转换比如CAST(123abc AS SIGNED)返回123不会报错。这在数据清洗时可能掩盖脏数据问题。你以为是123实际原值是123abc后面同步到报表时对不上账排查半天才发现是这里截断了。日期转换格式有严格限制。CAST(2023-02-30 AS DATE)不会报错MySQL会自动修正为2023-02-28。这种静默修正非常危险一旦业务数据里出现非法日期你需要及时发现而不是让它悄悄被修掉。6.5 流程控制函数补充IF、IFNULL、NULLIF这几类函数前面讲过但我在第七篇里想补充一个容易被忽视的NULLIFSELECT NULLIF(a, b);如果a等于b则返回NULL否则返回a。这在实际场景里很好用比如防止除数为零SELECT total_amount / NULLIF(order_count, 0) AS avg_order_amount FROM sales;当order_count是0时NULLIF把它转成NULL除法的结果就是NULL而不是报错ERROR 1365或返回无穷值。比用IF(condition, ...)简单清爽。7. 最后一篇我这几年用内置函数的几条实在体会系列写到第七篇很多函数都讲完了。最后聊几句我的真实感受。第一遇到问题先别急着在程序里做复杂循环。SQL层面多查一下函数文档八成有现成的解决方案。窗口函数、JSON函数这类特性早一天学会早一天从用拼接字符串模拟复杂逻辑的苦海里解放出来。我自己就是活生生的例子以前做报表能用子查询嵌套五层硬算后来换成窗口函数不仅代码短执行计划也干净得多线上慢查询直接少了一半。第二内置函数用起来有三个原则能用原生函数处理的不要丢到程序里处理能用索引解决的不要在函数上折腾需要用函数的优先考虑执行效率而不是一味追求写法的炫技。比如正则函数确实好用但你自己心里要有一根弦它不走索引量大的时候要控制使用范围。第三JSON字段和加密字段这类非传统数据一定要做好写入侧的数据校验程序别指望数据库帮你兜底。JSON非法、密文乱码这种问题一旦发生排查成本远高于在写入前做一次校验的成本。第七篇就写到这里。下一篇我打算整理一套MySQL常用函数速查表把前面七篇涉及的所有函数汇总成一份能直接打印的参考文档到时候大家放到手边查起来方便。如果你在项目里遇到什么冷门函数或者奇葩用法欢迎在评论区聊聊我看到了都会回复。