mysql的函数（第二期）
九、窗口函数（MySQL 8.0+）实用于对效果集的子集（窗口）进行盘算，常用于数据分析场景。
[*] ROW_NUMBER()
[*]作用：为每一行天生唯一的序号。
[*]示例：按分数降序排名 SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students;
[*] RANK() 与 DENSE_RANK()
[*]区别：
[*]RANK()：允许并列排名，后续序号跳过重复值（如 1,2,2,4）。
[*]DENSE_RANK()：允许并列排名，但后续序号连续（如 1,2,2,3）。
[*]示例： SELECT name, score,
RANK() OVER (ORDER BY score DESC) AS rank,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;
[*] LEAD() 与 LAG()
[*]作用：访问当前行之后（LEAD）或之前（LAG）的行的数据。
[*]示例：比力当前行与前一行的销售额 SELECT order_date, amount,
LAG(amount) OVER (ORDER BY order_date) AS prev_amount
FROM sales;
[*] 聚合函数 + OVER()
[*]作用：在窗口内进行聚合盘算。
[*]示例：盘算累计销售额 SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM sales;
十、JSON 函数（MySQL 5.7+）
处理 JSON 格式数据，支持分析、查询和修改。
[*] JSON_EXTRACT(json_doc, path)
[*]作用：提取 JSON 文档中的值。
[*]简写语法：->
[*]示例： SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name') AS name;
-- 等效简写
SELECT info->'$.name' FROM users;
[*] JSON_SET(json_doc, path, value)
[*]作用：修改或添加 JSON 字段。
[*]示例：更新 JSON 字段 UPDATE users
SET info = JSON_SET(info, '$.age', 31)
WHERE id = 1;
[*] JSON_ARRAYAGG(col) 与 JSON_OBJECTAGG(key, value)
[*]作用：将多行数据聚合为 JSON 数组或对象。
[*]示例：将用户角色聚合为 JSON 数组 SELECT user_id, JSON_ARRAYAGG(role) AS roles
FROM user_roles
GROUP BY user_id;
十一、空间函数（GIS）
处理地理空间数据（需使用 GEOMETRY 数据类型）。
[*] ST_Distance(geom1, geom2)
[*]作用：盘算两个多少对象的距离（单位取决于坐标系）。
[*]示例：盘算两点距离 SELECT ST_Distance(
ST_GeomFromText('POINT(10 20)'),
ST_GeomFromText('POINT(30 40)')
) AS distance;
[*] ST_Contains(geom1, geom2)
[*]作用：判断 geom1 是否完全包罗 geom2。
[*]示例：查询某区域内的所有坐标点 SELECT * FROM locations
WHERE ST_Contains(area_polygon, point);
十二、高级字符串处理
[*] REGEXP 正则表达式
[*]作用：使用正则匹配或更换字符串。
[*]示例： -- 匹配邮箱格式
SELECT email FROM users
WHERE email REGEXP '^+@+\.{2,}$';
-- 替换所有数字（需结合存储过程或程序代码）
-- 注意：MySQL 8.0+ 支持 REGEXP_REPLACE
SELECT REGEXP_REPLACE('abc123', '', 'X'); → 'abcXXX'
[*] FULLTEXT 全文搜索
[*]作用：对文本字段进行高效关键词搜索（需创建全文索引）。
[*]示例： -- 创建全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);
-- 使用 MATCH AGAINST 查询
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL tutorial' IN NATURAL LANGUAGE MODE);
十三、体系信息函数
[*] VERSION()
[*]作用：返回 MySQL 版本信息。
[*]示例：SELECT VERSION(); → 8.0.30
[*] LAST_INSERT_ID()
[*]作用：获取最后插入的自增 ID。
[*]示例： INSERT INTO users (name) VALUES ('Bob');
SELECT LAST_INSERT_ID(); → 1001
[*] USER() 与 CURRENT_USER()
[*]区别：
[*]USER()：返回客户端连接时使用的用户名和主机。
[*]CURRENT_USER()：返回权限验证时的用户名和主机。
十四、性能优化与陷阱
[*] 制止索引失效
[*]错误示例： SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 索引失效
[*]优化方案： SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; -- 利用索引
[*] 谨慎使用 GROUP_CONCAT
[*]默认长度限定：group_concat_max_len 体系变量（默认 1024 字节），需调整： SET SESSION group_concat_max_len = 1000000;
[*] 隐式类型转换
[*]示例：字符串与数字比力大概导致全表扫描： SELECT * FROM products WHERE price = '100'; -- price 是数值类型
十五、实战案例
[*] 天生日期序列
-- 生成最近 7 天的日期
SELECT CURDATE() - INTERVAL n DAY AS date
FROM (
SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
) AS numbers;
[*] 数据分桶统计
-- 将分数按区间分桶统计人数
SELECT
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END AS bucket,
COUNT(*) AS count
FROM students
GROUP BY bucket;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]