mysql的函数（第二期）

九、窗口函数（MySQL 8.0+）​​

实用于对效果集的子集（窗口）进行盘算，常用于数据分析场景。

• ​​ROW_NUMBER()​​
  
  
  
  • ​​作用​​：为每一行天生唯一的序号。
    
  • ​​示例​​：按分数降序排名
    1. SELECT name, score,
    2.   ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
    3. FROM students;

    复制代码
  
  
• ​​RANK() 与 DENSE_RANK()​​
  
  
  
  • ​​区别​​：
    
    
    
    • RANK()：允许并列排名，后续序号跳过重复值（如 1,2,2,4）。
      
    • DENSE_RANK()：允许并列排名，但后续序号连续（如 1,2,2,3）。
      
    
    
  • ​​示例​​：
    1. SELECT name, score,
    2.   RANK() OVER (ORDER BY score DESC) AS rank,
    3.   DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
    4. FROM students;

    复制代码
  
  
• ​​LEAD() 与 LAG()​​
  
  
  
  • ​​作用​​：访问当前行之后（LEAD）或之前（LAG）的行的数据。
    
  • ​​示例​​：比力当前行与前一行的销售额
    1. SELECT order_date, amount,
    2.   LAG(amount) OVER (ORDER BY order_date) AS prev_amount
    3. FROM sales;

    复制代码
  
  
• ​​聚合函数 + OVER()​​
  
  
  
  • ​​作用​​：在窗口内进行聚合盘算。
    
  • ​​示例​​：盘算累计销售额
    1. SELECT order_date, amount,
    2.   SUM(amount) OVER (ORDER BY order_date) AS running_total
    3. FROM sales;

    复制代码
  
  

---

​​十、JSON 函数（MySQL 5.7+）​​

处理 JSON 格式数据，支持分析、查询和修改。

• ​​JSON_EXTRACT(json_doc, path)​​
  
  
  
  • ​​作用​​：提取 JSON 文档中的值。
    
  • ​​简写语法​​：->
    
  • ​​示例​​：
    1. SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name') AS name;
    2. -- 等效简写
    3. SELECT info->'$.name' FROM users;

    复制代码
  
  
• ​​JSON_SET(json_doc, path, value)​​
  
  
  
  • ​​作用​​：修改或添加 JSON 字段。
    
  • ​​示例​​：更新 JSON 字段
    1. UPDATE users
    2. SET info = JSON_SET(info, '$.age', 31)
    3. WHERE id = 1;

    复制代码
  
  
• ​​JSON_ARRAYAGG(col)​​ 与 ​​JSON_OBJECTAGG(key, value)​​
  
  
  
  • ​​作用​​：将多行数据聚合为 JSON 数组或对象。
    
  • ​​示例​​：将用户角色聚合为 JSON 数组
    1. SELECT user_id, JSON_ARRAYAGG(role) AS roles
    2. FROM user_roles
    3. GROUP BY user_id;

    复制代码
  
  

---

​​十一、空间函数（GIS）​​

处理地理空间数据（需使用 GEOMETRY 数据类型）。

• ​​ST_Distance(geom1, geom2)​​
  
  
  
  • ​​作用​​：盘算两个多少对象的距离（单位取决于坐标系）。
    
  • ​​示例​​：盘算两点距离
    1. SELECT ST_Distance(
    2.   ST_GeomFromText('POINT(10 20)'),
    3.   ST_GeomFromText('POINT(30 40)')
    4. ) AS distance;

    复制代码
  
  
• ​​ST_Contains(geom1, geom2)​​
  
  
  
  • ​​作用​​：判断 geom1 是否完全包罗 geom2。
    
  • ​​示例​​：查询某区域内的所有坐标点
    1. SELECT * FROM locations
    2. WHERE ST_Contains(area_polygon, point);

    复制代码
  
  

---

​​十二、高级字符串处理​​

• ​​REGEXP 正则表达式​​
  
  
  
  • ​​作用​​：使用正则匹配或更换字符串。
    
  • ​​示例​​：
    1. -- 匹配邮箱格式
    2. SELECT email FROM users
    3. WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
    5. -- 替换所有数字（需结合存储过程或程序代码）
    6. -- 注意：MySQL 8.0+ 支持 REGEXP_REPLACE
    7. SELECT REGEXP_REPLACE('abc123', '[0-9]', 'X'); → 'abcXXX'

    复制代码
  
  
• ​​FULLTEXT 全文搜索​​
  
  
  
  • ​​作用​​：对文本字段进行高效关键词搜索（需创建全文索引）。
    
  • ​​示例​​：
    1. -- 创建全文索引
    2. ALTER TABLE articles ADD FULLTEXT(title, content);
    4. -- 使用 MATCH AGAINST 查询
    5. SELECT * FROM articles
    6. WHERE MATCH(title, content) AGAINST('MySQL tutorial' IN NATURAL LANGUAGE MODE);

    复制代码
  
  

---

​​十三、体系信息函数​​

• ​​VERSION()​​
  
  
  
  • ​​作用​​：返回 MySQL 版本信息。
    
  • ​​示例​​：SELECT VERSION(); → 8.0.30
    
  
  
• ​​LAST_INSERT_ID()​​
  
  
  
  • ​​作用​​：获取最后插入的自增 ID。
    
  • ​​示例​​：
    1. INSERT INTO users (name) VALUES ('Bob');
    2. SELECT LAST_INSERT_ID(); → 1001

    复制代码
  
  
• ​​USER() 与 CURRENT_USER()​​
  
  
  
  • ​​区别​​：
    
    
    
    • USER()：返回客户端连接时使用的用户名和主机。
      
    • CURRENT_USER()：返回权限验证时的用户名和主机。
      
    
    
  
  

---

​​十四、性能优化与陷阱​​

• ​​制止索引失效​​
  
  
  
  • ​​错误示例​​：
    1. SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 索引失效

    复制代码
  • ​​优化方案​​：
    1. SELECT * FROM orders
    2. WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; -- 利用索引

    复制代码
  
  
• ​​谨慎使用 GROUP_CONCAT​​
  
  
  
  • ​​默认长度限定​​：group_concat_max_len 体系变量（默认 1024 字节），需调整：
    1. SET SESSION group_concat_max_len = 1000000;

    复制代码
  
  
• ​​隐式类型转换​​
  
  
  
  • ​​示例​​：字符串与数字比力大概导致全表扫描：
    1. SELECT * FROM products WHERE price = '100'; -- price 是数值类型

    复制代码
  
  

---

​​十五、实战案例​​

• ​​天生日期序列​​
  
  1. -- 生成最近 7 天的日期
  2. SELECT CURDATE() - INTERVAL n DAY AS date
  3. FROM (
  4.   SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
  5.   UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
  6. ) AS numbers;

  复制代码
• ​​数据分桶统计​​
  
  1. -- 将分数按区间分桶统计人数
  2. SELECT
  3.   CASE
  4.     WHEN score >= 90 THEN 'A'
  5.     WHEN score >= 80 THEN 'B'
  6.     ELSE 'C'
  7.   END AS bucket,
  8.   COUNT(*) AS count
  9. FROM students
  10. GROUP BY bucket;

  复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。