MySQL 8.0+ 新增函数

------------------------------------------------------------------

MySQL 8.0 引入了许多强大的新函数和特性，极大增强了数据库的功能性、安全性和性能。以下是一些常用且重要的新函数分类及示例：

一、JSON 增强函数

MySQL 8.0 对 JSON 支持进行了大幅扩展，新增了更多操作 JSON 数据的函数：

 

1. JSON_TABLE
   将 JSON 数据（数组或对象）转换为关系型表结构，是处理 JSON 最核心的函数之一。
   示例：解析 JSON 数组为表
   sql

    

    

   SELECT * FROM JSON_TABLE(
     '[{"id":1,"name":"苹果"},{"id":2,"name":"香蕉"}]',
     '$[*]' COLUMNS (
       id INT PATH '$.id',
       name VARCHAR(50) PATH '$.name'
     )
   ) AS jt;
   

    
2. JSON_VALUE
   从 JSON 中提取单个标量值（字符串、数字等），并可指定默认值。
   示例：提取 JSON 中的名称
   sql

    

    

   SELECT JSON_VALUE('{"id":1,"name":"苹果"}', '$.name') AS fruit_name;
   -- 结果：'苹果'
   

    
3. JSON_QUERY
   从 JSON 中提取非标量值（对象或数组），与 JSON_VALUE 互补。
   示例：提取 JSON 数组
   sql

    

    

   SELECT JSON_QUERY('[1,2,3,4]', '$[1:3]') AS sub_array;
   -- 结果：[2,3]
   

    
4. JSON_MERGE_PATCH / JSON_MERGE_PRESERVE
   合并多个 JSON 文档：
   • JSON_MERGE_PATCH：冲突时后者覆盖前者（符合 RFC 7396 标准）
   • JSON_MERGE_PRESERVE：保留所有数据（旧版 JSON_MERGE 的行为）
     示例：
   sql

    

    

   SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2, "a":3}') AS merged;
   -- 结果：{"a":3, "b":2}
   

    

二、正则表达式增强函数

MySQL 8.0 引入了更强大的正则函数，替代了旧版的 REGEXP 运算符：

 

1. REGEXP_LIKE
   检查字符串是否匹配正则模式，支持更多参数（如区分大小写）。
   示例：匹配邮箱格式
   sql

    

    

   SELECT REGEXP_LIKE('test@example.com', '^[a-z]+@[a-z]+\\.[a-z]{2,}$');
   -- 结果：1（匹配）
   

    
2. REGEXP_INSTR
   返回匹配的子串在字符串中的起始位置（无匹配返回 0）。
   示例：查找数字位置
   sql

    

    

   SELECT REGEXP_INSTR('abc123def', '[0-9]+') AS num_position;
   -- 结果：4（数字从第4位开始）
   

    
3. REGEXP_REPLACE
   替换匹配正则模式的子串。
   示例：隐藏手机号中间 4 位
   sql

    

    

   SELECT REGEXP_REPLACE('13812345678', '^(1[3-9])([0-9]{4})([0-9]{4})$', '\\1****\\3');
   -- 结果：138****5678
   

    
4. REGEXP_SUBSTR
   提取第一个匹配正则模式的子串。
   示例：提取 URL 中的域名
   sql

    

    

   SELECT REGEXP_SUBSTR('https://www.example.com/path', 'www\\.[a-z]+\\.com') AS domain;
   -- 结果：www.example.com
   

    

三、窗口函数（Window Functions）

MySQL 8.0 正式支持窗口函数，用于在一组表行（窗口）上执行计算，不影响整体聚合，适合排名、累计计算等场景：

 

1. ROW_NUMBER()：为每行分配唯一序号（同组内不重复）。
2. RANK()：排名（有并列时跳过后续名次，如 1,2,2,4...）。
3. DENSE_RANK()：密集排名（有并列时不跳过后续名次，如 1,2,2,3...）。
4. SUM() OVER()：累计求和。

 

示例：学生成绩排名

 

sql

 

 

SELECT 
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK() OVER (ORDER BY score DESC) AS rk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM students;

 

四、日期与时间函数

1. DATE_ADD / DATE_SUB 增强
   支持更灵活的时间单位（如 MICROSECOND、WEEK）。
2. EXTRACT
   提取日期时间的指定部分（如年、季度、分钟）。
   示例：
   sql

    

    

   SELECT EXTRACT(YEAR FROM '2023-05-15') AS year; -- 2023
   SELECT EXTRACT(QUARTER FROM '2023-05-15') AS quarter; -- 2（第二季度）
   

    
3. DATE_FORMAT 扩展
   支持更多格式符（如 %f 表示微秒，%v 表示周数）。

五、聚合函数增强

1. JSON_ARRAYAGG
   将分组内的多行数据聚合为 JSON 数组（区别于 GROUP_CONCAT 的字符串聚合）。
   示例：
   sql

    

    

   SELECT 
     user_id,
     JSON_ARRAYAGG(product) AS bought_products
   FROM orders
   GROUP BY user_id;
   -- 结果：user_id=1 → ["手机", "电脑"]
   

    
2. JSON_OBJECTAGG
   将分组内的键值对聚合为 JSON 对象。
   示例：
   sql

    

    

   SELECT 
     user_id,
     JSON_OBJECTAGG(product, price) AS product_prices
   FROM orders
   GROUP BY user_id;
   -- 结果：user_id=1 → {"手机":3000, "电脑":5000}
   

    

六、其他实用函数

1. STRCMP
   比较两个字符串的大小（区分大小写），返回 -1（前者小）、0（相等）、1（前者大）。
   sql

    

    

   SELECT STRCMP('apple', 'banana'); -- -1
   

    
2. CHARACTER_LENGTH
   返回字符串的字符数（区别于 LENGTH 返回字节数，适合多字节字符如中文）。
   sql

    

    

   SELECT CHARACTER_LENGTH('你好'); -- 2（2个字符）
   SELECT LENGTH('你好'); -- 6（UTF-8下每个中文3字节）
   

    
3. SHA2
   提供更安全的哈希算法（如 SHA-256、SHA-512），替代旧版 SHA1。
   sql

    

    

   SELECT SHA2('password', 256); -- 256位哈希值 

总结

MySQL 8.0 的新函数显著增强了以下能力：

 

• JSON 处理：JSON_TABLE、JSON_VALUE 等让 JSON 与关系数据互转更便捷；
• 正则表达式：功能更完善的 REGEXP_* 系列函数；
• 数据分析：窗口函数支持复杂的行级计算；
• 聚合灵活性：JSON_ARRAYAGG 等函数让聚合结果更易扩展。

 

这些函数使 MySQL 在处理复杂业务场景（如报表生成、JSON 数据存储、文本分析）时更高效、更灵活。

------------------------------------------------------------------

MySQL 8.0+ 新增函数

MySQL 8.0 及以上版本新增了许多实用函数，涵盖 JSON 处理、正则表达式、窗口函数、聚合函数等多个领域，极大提升了数据处理能力。以下是一些关键新增函数的详细介绍：

一、JSON 高级处理函数

MySQL 8.0 大幅增强了 JSON 支持，新增了多个高效处理 JSON 数据的函数：

1. JSON_TABLE
   将 JSON 数组或对象转换为关系型表结构，是 JSON 数据 “扁平化” 的核心工具。
   示例：解析 JSON 数组为表
   sql

    

    

   SELECT * FROM JSON_TABLE(
     '[{"id":1,"name":"张三"},{"id":2,"name":"李四"}]',
     '$[*]' COLUMNS (
       id INT PATH '$.id',
       name VARCHAR(50) PATH '$.name'
     )
   ) AS jt;
   

    
   
   
   结果会生成包含 id 和 name 列的表，每行对应 JSON 数组中的一个对象。
2. JSON_VALUE 与 JSON_QUERY
   • JSON_VALUE：提取 JSON 中的标量值（字符串、数字等），支持默认值。
     sql

      

      

     SELECT JSON_VALUE('{"price":99.9}', '$.price' DEFAULT 0 ON EMPTY) AS price;
     

      
   • JSON_QUERY：提取 JSON 中的非标量值（对象或数组），避免返回标量值。
     sql

      

      

     SELECT JSON_QUERY('{"items":[1,2,3]}', '$.items') AS items; -- 返回 [1,2,3]
     

      
3. JSON_MERGE_PATCH
   合并 JSON 文档，冲突时后者覆盖前者（符合 RFC 7396 标准），替代旧版 JSON_MERGE。
   sql

    

    

   SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2, "a":3}'); -- 结果：{"a":3, "b":2}
   

    
4. JSON_KEYS 与 JSON_CONTAINS_PATH
   • JSON_KEYS：返回 JSON 对象的所有键名组成的数组。
   • JSON_CONTAINS_PATH：检查 JSON 中是否存在指定路径。
     sql

      

      

     SELECT JSON_CONTAINS_PATH('{"name":"苹果", "price":5}', 'one', '$.name'); -- 1（存在）
     

      

二、正则表达式增强函数

替代了旧版 REGEXP 运算符，提供更丰富的正则处理能力：

1. REGEXP_LIKE
   检查字符串是否匹配正则模式，支持 CASE_INSENSITIVE 等参数。
   sql

    

    

   SELECT REGEXP_LIKE('Test123', '^[a-z0-9]+$', 'i'); -- 1（不区分大小写匹配）
   

    
2. REGEXP_INSTR
   返回匹配子串的起始位置（无匹配返回 0）。
   sql

    

    

   SELECT REGEXP_INSTR('abc123def', '\\d+'); -- 4（数字从第4位开始）
   

    
3. REGEXP_REPLACE
   替换匹配正则模式的子串，支持分组引用。
   sql

    

    

   SELECT REGEXP_REPLACE('2023-10-05', '^(\\d{4})-(\\d{2})-(\\d{2})$', '\\2/\\3/\\1');
   -- 结果：10/05/2023
   

    
4. REGEXP_SUBSTR
   提取第一个匹配的子串。
   sql

    

    

   SELECT REGEXP_SUBSTR('Email: test@example.com', '[a-z]+@[a-z]+\\.[a-z]+');
   -- 结果：test@example.com
   

    

三、窗口函数（Window Functions）

MySQL 8.0 正式引入窗口函数，支持在 “行的集合（窗口）” 上进行计算，不影响整体聚合：

1. 排名函数
   • ROW_NUMBER()：为每行分配唯一序号（同组内不重复）。
   • RANK()：排名（并列时跳过后续名次，如 1,2,2,4...）。
   • DENSE_RANK()：密集排名（并列时不跳过后续名次，如 1,2,2,3...）。
     sql

      

      

     SELECT 
       score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK() OVER (ORDER BY score DESC) AS rk
     FROM students;
     

      
2. 聚合窗口函数
   如 SUM()、AVG() 等，结合 OVER() 计算累计值或滑动窗口值。
   sql

    

    

   SELECT 
     month,
     revenue,
     SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
   FROM sales;
   

    

四、JSON 聚合函数

专为 JSON 数据设计的聚合函数，用于将多行数据聚合为 JSON 格式：

1. JSON_ARRAYAGG
   将分组内的字段值聚合为 JSON 数组（区别于 GROUP_CONCAT 的字符串数组）。
   sql

    

    

   SELECT 
     user_id,
     JSON_ARRAYAGG(product) AS bought_products  -- 结果：["手机", "电脑"]
   FROM orders
   GROUP BY user_id;
   

    
2. JSON_OBJECTAGG
   将分组内的键值对聚合为 JSON 对象。
   sql

    

    

   SELECT 
     user_id,
     JSON_OBJECTAGG(product, price) AS product_prices  -- 结果：{"手机":3000, "电脑":5000}
   FROM orders
   GROUP BY user_id;
   

    

五、其他实用函数

1. GREATEST 与 LEAST 增强
   支持多参数比较，返回最大 / 最小值（处理 NULL 时忽略）。
   sql

    

    

   SELECT GREATEST(10, 5, 20, NULL); -- 20
   

    
2. STRCMP
   比较两个字符串的大小（区分大小写），返回 -1、0 或 1。
   sql

    

    

   SELECT STRCMP('Apple', 'apple'); -- -1（'Apple' 小于 'apple'）
   

    
3. CHARACTER_LENGTH
   返回字符串的字符数（而非字节数），适合多字节字符（如中文）。
   sql

    

    

   SELECT CHARACTER_LENGTH(' MySQL 8.0 '); -- 8（含空格）
   

    
4. SHA2
   提供更安全的哈希算法（支持 SHA-224、SHA-256、SHA-384、SHA-512）。
   sql

    

    

   SELECT SHA2('secret', 256); -- 生成 256 位哈希值
   

    

总结

MySQL 8.0+ 的新增函数重点强化了以下场景：

• JSON 数据处理：JSON_TABLE 实现 JSON 与表的无缝转换；
• 复杂文本分析：REGEXP_* 系列函数提供强大的正则处理；
• 高级数据分析：窗口函数支持排名、累计计算等精细化分析；
• 灵活聚合：JSON_ARRAYAGG 等函数让聚合结果更易用于 API 输出。

这些函数使 MySQL 在处理现代应用场景（如 JSON 存储、日志分析、报表生成）时更加高效和灵活。

------------------------------------------------------------------

------------------------------------------------------------------

------------------------------------------------------------------