mysql group_concat coalesce 等链接函数

group_concat coalesce在 SQL 中，GROUP_CONCAT 和 COALESCE 是两个常用的函数，它们可以结合使用来处理数据聚合和空值问题。1. GROUP_CONCAT

GROUP_CONCAT 用于将分组后的多行数据合并为一个字符串，通常与 GROUP BY 一起使用。
基本语法：
sql  

1. GROUP_CONCAT([DISTINCT] 列名 [ORDER BY 排序字段] [SEPARATOR '分隔符'])

复制代码

 
示例：
假设有一个 students 表：
idnameclass1AliceA2BobA3CharlieB
使用 GROUP_CONCAT 按班级合并学生姓名：
sql  

1. SELECT class, GROUP_CONCAT(name SEPARATOR ', ') AS students
2. FROM students
3. GROUP BY class;

复制代码

 
结果：
plaintext  

1. class | students
2. ------|----------------
3. A     | Alice, Bob
4. B     | Charlie

复制代码

 2. COALESCE

COALESCE 用于返回参数列表中第一个非 NULL 的值，如果所有参数都是 NULL，则返回 NULL。
基本语法：
sql  

1. COALESCE(表达式1, 表达式2, ..., 表达式N)

复制代码

 
示例：
处理可能为 NULL 的字段：
sql  

1. SELECT name, COALESCE(phone, '未提供') AS contact
2. FROM users;

复制代码

 
如果 phone 为 NULL，则返回 '未提供'。3. 结合使用 GROUP_CONCAT 和 COALESCE

当 GROUP_CONCAT 处理的分组中所有值都是 NULL 时，会返回 NULL。此时可以用 COALESCE 将其转换为更友好的默认值。
示例：
假设有一个 scores 表（部分学生某科目成绩为 NULL）：
student_idsubjectscore1Math901EnglishNULL2MathNULL2EnglishNULL
按学生分组，合并成绩并处理 NULL：
sql  

1. SELECT
2.   student_id,
3.   COALESCE(GROUP_CONCAT(score), '无成绩') AS all_scores
4. FROM scores
5. GROUP BY student_id;

复制代码

 
结果：
plaintext  

1. student_id | all_scores
2. -----------|------------
3. 1          | 90,NULL
4. 2          | 无成绩      -- 所有成绩为NULL时，COALESCE生效

复制代码

 通过这种组合，可以更灵活地处理聚合结果中的空值情况。 --------------------------------------------------------------------------------------------------------------- 在 MySQL 中，CASE WHEN 是一种条件判断语句，用于根据不同条件返回不同结果，类似于程序语言中的 if-else 或 switch 逻辑。它有两种常见用法：简单 CASE 表达式和搜索 CASE 表达式。1. 简单 CASE 表达式

用于判断单个表达式与多个值的匹配情况，语法如下：
sql  

1. CASE 表达式
2.   WHEN 值1 THEN 结果1
3.   WHEN 值2 THEN 结果2
4.   ...
5.   ELSE 默认结果
6. END

复制代码

 
示例：
假设有 products 表，根据 category 字段对商品分类：
sql  

1. SELECT
2.   product_name,
3.   category,
4.   CASE category
5.     WHEN 'electronics' THEN '电子设备'
6.     WHEN 'clothing' THEN '服装'
7.     WHEN 'food' THEN '食品'
8.     ELSE '其他'
9.   END AS category_cn
10. FROM products;

复制代码

 
结果：
product_namecategorycategory_cn手机electronics电子设备T 恤clothing服装苹果food食品花盆NULL其他2. 搜索 CASE 表达式

用于判断多个独立的条件（更灵活，可处理复杂逻辑），语法如下：
sql  

1. CASE
2.   WHEN 条件1 THEN 结果1
3.   WHEN 条件2 THEN 结果2
4.   ...
5.   ELSE 默认结果
6. END

复制代码

 
示例 1：根据分数评级
假设有 students 表，对 score 字段评级：
sql  

1. SELECT
2.   name,
3.   score,
4.   CASE
5.     WHEN score >= 90 THEN '优秀'
6.     WHEN score >= 80 THEN '良好'
7.     WHEN score >= 60 THEN '及格'
8.     ELSE '不及格'
9.   END AS grade
10. FROM students;

复制代码

 
示例 2：结合聚合函数
统计不同分数段的学生人数：
sql  

1. SELECT
2.   CASE
3.     WHEN score >= 90 THEN '优秀'
4.     WHEN score >= 80 THEN '良好'
5.     WHEN score >= 60 THEN '及格'
6.     ELSE '不及格'
7.   END AS grade,
8.   COUNT(*) AS count
9. FROM students
10. GROUP BY grade;

复制代码

 3. 注意事项

• 执行顺序：WHEN 条件按顺序判断，找到第一个满足条件的分支后立即返回，不再执行后续判断。
  
• ELSE 可选：如果没有 ELSE 且所有条件都不满足，返回 NULL。
  
• 空值处理：若判断字段可能为 NULL，需显式处理（例如 WHEN field IS NULL THEN ...）。
  
• 适用场景：可用于 SELECT、WHERE、ORDER BY、UPDATE 等语句中。
  

4. 实际应用示例

更新商品价格，根据分类调整折扣：
sql  

1. UPDATE products
2. SET price = price * CASE
3.   WHEN category = 'electronics' THEN 0.9  -- 电子产品9折
4.   WHEN category = 'clothing' THEN 0.8     -- 服装8折
5.   ELSE 1                                  -- 其他不打折
6. END;

复制代码

CASE WHEN 是 MySQL 中处理条件逻辑的核心工具，灵活运用可满足各种复杂的业务判断需求。case when exist在 SQL 中，CASE WHEN 与 EXISTS 结合使用可以实现复杂的条件判断，尤其适合根据子查询结果返回不同值的场景。基本用法

EXISTS 用于检查子查询是否返回结果（只要有一行结果就返回 TRUE），常与 CASE WHEN 结合实现 "如果存在某种记录则返回 A，否则返回 B" 的逻辑。
语法结构：
sql  

1. CASE
2.   WHEN EXISTS (子查询) THEN 结果1
3.   ELSE 结果2
4. END

复制代码

 示例说明

假设有以下两张表：

• orders（订单表）：order_id, user_id, order_date
  
• users（用户表）：user_id, name
  

需求：查询所有用户，并标记该用户是否有过订单。
sql  

1. SELECT
2.   u.user_id,
3.   u.name,
4.   CASE
5.     WHEN EXISTS (
6.       SELECT 1 FROM orders o
7.       WHERE o.user_id = u.user_id
8.     ) THEN '有订单'
9.     ELSE '无订单'
10.   END AS has_order
11. FROM users u;

复制代码

 
结果：
plaintext  

1. user_id | name  | has_order
2. --------|-------|-----------
3. 1       | 张三  | 有订单
4. 2       | 李四  | 无订单
5. 3       | 王五  | 有订单

复制代码

 进阶用法：多条件判断

可以在 CASE 中使用多个 WHEN EXISTS 实现多分支判断：
sql  

1. SELECT
2.   u.user_id,
3.   CASE
4.     WHEN EXISTS (
5.       SELECT 1 FROM orders
6.       WHERE user_id = u.user_id AND order_date >= '2023-01-01'
7.     ) THEN '今年有订单'
8.     WHEN EXISTS (
9.       SELECT 1 FROM orders
10.       WHERE user_id = u.user_id
11.     ) THEN '历史有订单'
12.     ELSE '无任何订单'
13.   END AS order_status
14. FROM users u;

复制代码

这种组合的优势在于：

• 效率高：EXISTS 只要找到匹配记录就会停止查询，不需要扫描全部数据
  
• 逻辑清晰：将 "存在性判断" 与 "结果返回" 直接关联
  
• 灵活性强：可嵌套复杂条件的子查询
  

注意：EXISTS 子查询中通常使用 SELECT 1 而非具体列名，因为只需要判断是否存在记录，不需要返回实际数据。--------------------------------------------------------------------------------------------------------------- mysql 链接函数 CONCAT SUBSTRING 在 MySQL 中，CONCAT 和 SUBSTRING 是处理字符串的常用函数，常结合使用来实现复杂的字符串拼接与截取需求。1. CONCAT 函数：字符串拼接

CONCAT 用于将多个字符串连接成一个字符串。
语法：
sql  

1. CONCAT(字符串1, 字符串2, ..., 字符串N)

复制代码

 
特点：

• 若任何一个参数为 NULL，则返回 NULL
  
• 可以连接字段、常量或表达式
  

示例：
假设有 users 表，包含 first_name 和 last_name 字段：
sql  

1. -- 拼接姓和名
2. SELECT
3.   first_name,
4.   last_name,
5.   CONCAT(first_name, ' ', last_name) AS full_name
6. FROM users;

复制代码

 
结果：
plaintext  

1. first_name | last_name | full_name
2. -----------|-----------|-----------
3. John       | Doe       | John Doe
4. Jane       | Smith     | Jane Smith

复制代码

 
处理 NULL：
若字段可能为 NULL，可结合 COALESCE 避免结果为 NULL：
sql  

1. SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
2. FROM users;

复制代码

 2. SUBSTRING 函数：字符串截取

SUBSTRING（别名 SUBSTR）用于从字符串中截取子串，有两种常用语法。语法 1：指定起始位置和长度

sql  

1. SUBSTRING(字符串, 起始位置, 长度)

复制代码

 

• 起始位置：正数表示从左向右（1 开始计数），负数表示从右向左
  
• 长度：可选，不指定则截取到字符串末尾
  

语法 2：指定起始位置（兼容 SQL 标准）

sql  

1. SUBSTRING(字符串 FROM 起始位置 FOR 长度)

复制代码

 
示例：
sql  

1. -- 截取前3个字符
2. SELECT SUBSTRING('Hello World', 1, 3); -- 结果：'Hel'
4. -- 从第7个字符开始截取到末尾
5. SELECT SUBSTRING('Hello World', 7); -- 结果：'World'
7. -- 从右侧第5个字符开始截取3个字符
8. SELECT SUBSTRING('Hello World', -5, 3); -- 结果：'orl'
10. -- 标准语法示例
11. SELECT SUBSTRING('Hello World' FROM 1 FOR 5); -- 结果：'Hello'

复制代码

 3. 结合使用 CONCAT 和 SUBSTRING

实际场景中常需要先截取再拼接，或先拼接再截取。
示例 1：隐藏手机号中间 4 位
sql  

1. SELECT
2.   phone,
3.   CONCAT(
4.     SUBSTRING(phone, 1, 3),  -- 前3位
5.     '****',                  -- 中间替换为*
6.     SUBSTRING(phone, 8)      -- 从第8位开始取末尾
7.   ) AS hidden_phone
8. FROM users;

复制代码

 
结果：
plaintext  

1. phone        | hidden_phone
2. -------------|--------------
3. 13812345678  | 138****5678
4. 13987654321  | 139****4321

复制代码

 
示例 2：拼接截取后的字符串
sql  

1. -- 取邮箱@前的部分作为用户名，并拼接固定后缀
2. SELECT
3.   email,
4.   CONCAT(SUBSTRING(email, 1, LOCATE('@', email)-1), '_user') AS username
5. FROM users;

复制代码

 
结果：
plaintext  

1. email               | username
2. --------------------|----------
3. john.doe@example.com| john.doe_user
4. jane@test.com       | jane_user

复制代码

 4. 注意事项

• SUBSTRING 的起始位置是从 1 开始（而非 0），与多数编程语言不同
  
• 处理中文字符时，SUBSTRING 按字符数截取（与字节无关）
  
• 若截取长度超出字符串实际长度，只会返回到字符串末尾
  
• CONCAT_WS 是 CONCAT 的变体，可指定分隔符（CONCAT_WS('|', a, b, c)）
  

这两个函数配合使用，能满足大多数字符串处理需求，如格式化输出、数据脱敏、提取关键信息等。SUBSTRING_INDEX在 MySQL 中，SUBSTRING_INDEX 是一个按分隔符截取字符串的专用函数，比 SUBSTRING 更适合处理 “以特定字符分割的字符串”（如逗号分隔值、邮箱 @分隔、URL 路径分隔等），无需计算分隔符的位置，使用更简洁。一、基本语法

sql  

1. SUBSTRING_INDEX(原始字符串, 分隔符, 计数N)

复制代码

 

• 原始字符串：需要处理的目标字符串（如 'a,b,c,d'、'user@example.com'）。
  
• 分隔符：用于分割字符串的特定字符（如 ','、'@'、'/'），必须是单个字符或固定字符串。
  
• 计数 N：控制截取的范围，规则如下：
  
  
  
  • 当 N > 0 时：从左侧开始，截取到第 N 个分隔符左侧的内容。
    
  • 当 N < 0 时：从右侧开始，截取到第 |N| 个分隔符右侧的内容。
    
  • 当 N = 0 时：返回空字符串（无实际意义）。
    
  
  

二、核心用法示例

以常见的 “分隔符场景” 为例，理解 SUBSTRING_INDEX 的截取逻辑：场景 1：逗号分隔的字符串（如标签、多选值）

假设有字符串 'apple,banana,orange,grape'，分隔符为 ','：
sql  

1. -- 1. N=2（左侧第2个逗号左侧）：取前2个元素
2. SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', 2);
3. -- 结果：'apple,banana'
5. -- 2. N=-2（右侧第2个逗号右侧）：取后2个元素
6. SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', -2);
7. -- 结果：'orange,grape'
9. -- 3. N=1（左侧第1个逗号左侧）：取第1个元素
10. SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', 1);
11. -- 结果：'apple'
13. -- 4. N=-1（右侧第1个逗号右侧）：取最后1个元素
14. SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', -1);
15. -- 结果：'grape'

复制代码

 场景 2：邮箱地址（@分隔）

提取邮箱的 “用户名”（@左侧）和 “域名”（@右侧）：
sql  

1. -- 1. 取用户名（@左侧，N=1）
2. SELECT SUBSTRING_INDEX('zhangsan@qq.com', '@', 1);
3. -- 结果：'zhangsan'
5. -- 2. 取域名（@右侧，N=-1）
6. SELECT SUBSTRING_INDEX('zhangsan@qq.com', '@', -1);
7. -- 结果：'qq.com'
9. -- 3. 进一步取域名的主域名（如从'qq.com'中取'qq'，分隔符为'.'，N=1）
10. SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('zhangsan@qq.com', '@', -1), '.', 1);
11. -- 结果：'qq'

复制代码

 场景 3：URL 路径（/ 分隔）

提取 URL 中的特定路径段，如 'https://www.example.com/blog/2024/05'：
sql  

1. -- 1. 取协议后的主机名（第3个'/'右侧，N=-4）
2. SELECT SUBSTRING_INDEX('https://www.example.com/blog/2024/05', '/', 3);
3. -- 结果：'https://www.example.com'
5. -- 2. 取最后一个路径段（年份+月份，N=-1）
6. SELECT SUBSTRING_INDEX('https://www.example.com/blog/2024/05', '/', -1);
7. -- 结果：'05'
9. -- 3. 取倒数第二个路径段（年份，N=-2 后再取右侧）
10. SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('https://www.example.com/blog/2024/05', '/', -2), '/', 1);
11. -- 结果：'2024'

复制代码

 三、实际业务场景应用

示例 1：处理用户标签（从表中提取标签）

假设有 user_tags 表，tags 字段存储逗号分隔的标签（如 '体育,音乐,阅读'）：
sql  

1. SELECT
2.   user_id,
3.   tags,
4.   -- 取第一个标签
5.   SUBSTRING_INDEX(tags, ',', 1) AS first_tag,
6.   -- 取最后一个标签
7.   SUBSTRING_INDEX(tags, ',', -1) AS last_tag,
8.   -- 取中间第二个标签（先取前2个，再取最后1个）
9.   SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 2), ',', -1) AS second_tag
10. FROM user_tags;

复制代码

 
结果：
user_idtagsfirst_taglast_tagsecond_tag1体育，音乐，阅读体育阅读音乐2电影，旅行电影旅行旅行示例 2：数据脱敏（截取手机号前 3 位和后 4 位，中间用 * 替换）

结合 CONCAT 和 SUBSTRING_INDEX 实现手机号脱敏：
sql  

1. SELECT
2.   phone,
3.   CONCAT(
4.     SUBSTRING_INDEX(phone, '', 3),  -- 前3位（分隔符为空字符串，按单个字符分割）
5.     '****',
6.     SUBSTRING_INDEX(phone, '', -4)  -- 后4位
7.   ) AS hidden_phone
8. FROM users;

复制代码

 
结果：
phonehidden_phone13812345678138****567813987654321139****4321四、注意事项

• 分隔符匹配问题：若原始字符串中不存在指定的分隔符，SUBSTRING_INDEX 会直接返回整个原始字符串（而非 NULL）。
  例：SELECT SUBSTRING_INDEX('hello', ',', 2); → 结果为 'hello'。
  
• 空值处理：若原始字符串为 NULL，函数返回 NULL。若需避免，可结合 COALESCE 处理：
  例：SELECT SUBSTRING_INDEX(COALESCE(tags, ''), ',', 1);
  
• 分隔符为多字符：支持多字符分隔符（如 '--'），但需确保分隔符在原始字符串中存在。
  例：SELECT SUBSTRING_INDEX('a--b--c', '--', 2); → 结果为 'a--b'。
  
• 与 SUBSTRING 的区别：SUBSTRING 按 “位置和长度” 截取，SUBSTRING_INDEX 按 “分隔符数量” 截取，后者更适合结构化分隔的字符串（如 CSV 格式数据）。
  

五、常见组合函数

SUBSTRING_INDEX 常与其他字符串函数配合使用，满足复杂需求：

• 结合 TRIM：去除截取后字符串的前后空格（如 TRIM(SUBSTRING_INDEX(tags, ',', 1))）。
  
• 结合 LOWER/UPPER：统一大小写（如 LOWER(SUBSTRING_INDEX(email, '@', 1))）。
  
• 结合 COUNT：统计分隔符数量（如 LENGTH(tags) - LENGTH(REPLACE(tags, ',', '')) + 1，计算标签总数）。
  

总之，SUBSTRING_INDEX 是 MySQL 中处理 “分隔符字符串” 的高效工具，尤其适合标签、邮箱、URL、CSV 等场景，灵活运用可大幅简化字符串截取逻辑。  --------------------------------------------------------------------------------------------------------------- mysql REGEXP 匹配函数mysql REGEXP 匹配函数在 MySQL 中，REGEXP 是用于正则表达式匹配的运算符，用于判断字符串是否符合指定的正则模式。它提供了比普通字符串匹配（如 LIKE）更灵活、更强大的模式匹配能力，适合处理复杂的文本检索场景。一、基本语法

sql  

1. 字段名 REGEXP '正则模式'

复制代码

 

• 若字符串匹配正则模式，返回 1（真）；否则返回 0（假）。
  
• 匹配默认不区分大小写（若需区分，可使用 REGEXP BINARY）。
  
• 支持的正则语法与标准正则表达式基本一致，但有少量 MySQL 特有规则。
  

二、常用匹配规则及示例

假设有 users 表，包含 username 和 email 字段，以下是常见用法：1. 基础字符匹配

匹配包含特定字符或子串的记录（类似 LIKE '%xxx%'）：
sql  

1. -- 查找 username 中包含 'zhang' 的用户
2. SELECT username FROM users WHERE username REGEXP 'zhang';

复制代码

 2. 位置匹配

• ^：匹配字符串开头
  
• $：匹配字符串结尾
  

sql  

1. -- 查找 username 以 'li' 开头的用户（类似 LIKE 'li%'）
2. SELECT username FROM users WHERE username REGEXP '^li';
4. -- 查找 email 以 '.com' 结尾的用户（类似 LIKE '%.com'）
5. SELECT email FROM users WHERE email REGEXP '.com$';
7. -- 精确匹配（整个字符串完全等于模式）
8. SELECT username FROM users WHERE username REGEXP '^wangwu$'; -- 等价于 username = 'wangwu'

复制代码

 3. 字符集与范围

• [abc]：匹配 a、b 或 c 中的任意一个
  
• [a-z]：匹配任意小写字母（[0-9] 匹配数字，[A-Z] 匹配大写字母）
  
• [^abc]：匹配除 a、b、c 外的任意字符
  

sql  

1. -- 查找 username 中包含 'a'、'b' 或 'c' 的用户
2. SELECT username FROM users WHERE username REGEXP '[abc]';
4. -- 查找手机号以 138、139 或 188 开头的用户（假设 phone 字段为手机号）
5. SELECT phone FROM users WHERE phone REGEXP '^1(38|39|88)';
7. -- 查找 email 中包含非数字字符的用户
8. SELECT email FROM users WHERE email REGEXP '[^0-9]';

复制代码

 4. 量词匹配

• *：匹配前面的元素 0 次或多次
  
• +：匹配前面的元素 1 次或多次
  
• ?：匹配前面的元素 0 次或 1 次
  
• {n}：匹配前面的元素恰好 n 次
  
• {n,}：匹配前面的元素至少 n 次
  
• {n,m}：匹配前面的元素 n 到 m 次
  

sql  

1. -- 查找 username 中包含连续 2 个及以上 'a' 的用户
2. SELECT username FROM users WHERE username REGEXP 'a{2,}';
4. -- 查找 6-11 位数字的手机号（简化版）
5. SELECT phone FROM users WHERE phone REGEXP '^[0-9]{6,11}$';
7. -- 查找 email 中包含字母后跟 0 个或 1 个数字的用户
8. SELECT email FROM users WHERE email REGEXP '[a-z][0-9]?';

复制代码

 5. 特殊字符匹配

• .：匹配任意单个字符（除换行符）
  
• |：逻辑 “或”，匹配两边任意一个模式
  
• \\：转义字符（需转义的特殊字符：.^$*+?()[]|）
  

sql  

1. -- 查找 username 中包含 'a' 后跟任意字符再跟 'c' 的用户（如 'abc'、'a1c' 等）
2. SELECT username FROM users WHERE username REGEXP 'a.c';
4. -- 查找 email 域名为 qq.com 或 163.com 的用户
5. SELECT email FROM users WHERE email REGEXP '@(qq|163)\\.com$';
6. -- 注意：`.` 需转义为 `\\.`（MySQL 中需双重转义）

复制代码

 6. 区分大小写匹配

使用 REGEXP BINARY 强制区分大小写：
sql  

1. -- 只匹配大写字母开头的 username
2. SELECT username FROM users WHERE username REGEXP BINARY '^[A-Z]';

复制代码

 三、实际业务场景示例

1. 验证手机号格式（中国大陆手机号规则）

sql  

1. SELECT phone
2. FROM users
3. WHERE phone REGEXP '^1[3-9][0-9]{9}$';
4. -- 规则：以 1 开头，第二位为 3-9，后面跟 9 位数字，总长度 11 位

复制代码

 2. 提取包含特定格式的邮箱

sql  

1. SELECT email
2. FROM users
3. WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$';
4. -- 简单的邮箱格式验证（用户名@域名.后缀）

复制代码

 3. 查找包含敏感词的内容

sql  

1. SELECT content
2. FROM articles
3. WHERE content REGEXP '敏感词1|敏感词2|敏感词3';
4. -- 匹配包含多个敏感词中任意一个的内容

复制代码

 四、注意事项

• 性能问题：REGEXP 匹配比 LIKE 更消耗性能，尤其在大数据量的表中使用时，建议结合索引或限制查询范围。
  
• 转义字符：MySQL 中需用 \\ 作为转义符（标准正则用 \），例如匹配 * 需写成 \\*。
  
• 空值处理：若字段为 NULL，REGEXP 会返回 0（不匹配）。
  
• 版本差异：MySQL 8.0 及以上支持更多正则功能（如 REGEXP_LIKE()、REGEXP_INSTR() 等函数），功能更完善。
  

五、扩展函数（MySQL 8.0+）

除了 REGEXP 运算符，MySQL 8.0 还提供了更灵活的正则函数：

• REGEXP_LIKE(str, pattern)：同 REGEXP 运算符，返回 1 或 0。
  
• REGEXP_INSTR(str, pattern)：返回匹配的子串在字符串中的起始位置（无匹配返回 0）。
  
• REGEXP_REPLACE(str, pattern, replacement)：替换匹配的子串为指定内容。
  
• REGEXP_SUBSTR(str, pattern)：提取第一个匹配的子串（无匹配返回 NULL）。
  

示例：
sql  

1. -- 替换手机号中间4位为*
2. SELECT REGEXP_REPLACE(phone, '^(1[3-9])([0-9]{4})([0-9]{4})$', '\\1****\\3') AS hidden_phone
3. FROM users;

复制代码

REGEXP 是处理复杂文本匹配的强大工具，适合数据校验、内容过滤、格式提取等场景，但需合理使用以避免性能问题。--------------------------------------------------------------------------------------------------------------- mysql union union all left join right join 函数在 MySQL 中，UNION、UNION ALL 用于合并多个查询结果集，而 LEFT JOIN、RIGHT JOIN 用于多表关联查询，它们都是处理多表数据的重要工具，但应用场景不同。一、UNION 与 UNION ALL：合并结果集

用于将多个 SELECT 语句的结果集合并为一个，要求各查询的列数、列顺序、数据类型必须一致。1. UNION

• 功能：合并结果集并去除重复行。
  
• 语法：sql  
  1. SELECT 列1, 列2 FROM 表1
  2. UNION
  3. SELECT 列1, 列2 FROM 表2;

  复制代码
   
  

2. UNION ALL

• 功能：合并结果集但保留所有行（包括重复行）。
  
• 语法：sql  
  1. SELECT 列1, 列2 FROM 表1
  2. UNION ALL
  3. SELECT 列1, 列2 FROM 表2;

  复制代码
   
  

示例对比

假设有 table_a 和 table_b 两张表：
table_a table_b idnameidname1a2b2b3c3c3c
sql  

1. -- UNION 结果（去重）
2. SELECT id, name FROM table_a
3. UNION
4. SELECT id, name FROM table_b;

复制代码

 
idname1a2b3c
sql  

1. -- UNION ALL 结果（保留重复）
2. SELECT id, name FROM table_a
3. UNION ALL
4. SELECT id, name FROM table_b;

复制代码

 
idname1a2b3c2b3c3c注意事项

• UNION 因去重会额外消耗性能，若确认无重复或无需去重，优先使用 UNION ALL。
  
• 可对合并后的结果排序：(SELECT ...) UNION ALL (SELECT ...) ORDER BY 列名;
  

二、LEFT JOIN 与 RIGHT JOIN：表关联查询

用于根据两个表的关联字段匹配数据，核心是确定 “主表” 和 “从表”。1. LEFT JOIN（左连接）

• 功能：以左表为基准，返回左表所有记录，以及右表中与左表匹配的记录；右表无匹配时返回 NULL。
  
• 语法：sql  
  1. SELECT 列名
  2. FROM 左表
  3. LEFT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

  复制代码
   
  

2. RIGHT JOIN（右连接）

• 功能：以右表为基准，返回右表所有记录，以及左表中与右表匹配的记录；左表无匹配时返回 NULL。
  
• 语法：sql  
  1. SELECT 列名
  2. FROM 左表
  3. RIGHT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

  复制代码
   
  

示例说明

假设有 users（用户表）和 orders（订单表）：
users orders idnameorder_iduser_id1张三10112李四10213王五1033
sql  

1. -- LEFT JOIN：查询所有用户及其订单（包括无订单的用户）
2. SELECT u.name, o.order_id
3. FROM users u
4. LEFT JOIN orders o ON u.id = o.user_id;

复制代码

 
nameorder_id 张三101 张三102 李四NULL-- 李四无订单，右表字段为 NULL王五103 
sql  

1. -- RIGHT JOIN：查询所有订单及对应用户（包括订单表中可能的无效用户）
2. SELECT u.name, o.order_id
3. FROM users u
4. RIGHT JOIN orders o ON u.id = o.user_id;

复制代码

 
nameorder_id张三101张三102王五103注意事项

• 关联条件通过 ON 指定，而非 WHERE（WHERE 会过滤已匹配的记录）。
  
• 可添加筛选条件：LEFT JOIN ... ON ... WHERE 右表.字段 IS NULL（查询左表中无匹配的记录）。
  
• LEFT JOIN 和 RIGHT JOIN 可互相转换（交换表顺序，左连接变右连接）。
  

三、核心区别总结

操作用途关键点UNION合并结果集（去重）多查询列结构需一致，性能略低UNION ALL合并结果集（保留重复）性能优于 UNION，适合确定无重复的场景LEFT JOIN左表为基准的关联查询确保左表所有记录被返回RIGHT JOIN右表为基准的关联查询确保右表所有记录被返回
根据业务需求选择合适的操作：合并独立查询结果用 UNION/UNION ALL；关联多表数据用 LEFT JOIN/RIGHT JOIN。 --------------------------------------------------------------------------------------------------------------- mysql 行转列 列转行 函数 在 MySQL 中，并没有专门的 “行转列” 或 “列转行” 函数，但可以通过 CASE WHEN+ 聚合函数 实现行转列，通过 UNION ALL 或 JSON 函数（8.0+） 实现列转行。这两种操作本质是对数据结构的重塑，常用于报表展示、数据统计等场景。一、行转列（Row to Column）

概念：将表中 “同一维度的多行数据” 转换为 “同一行的多列数据”。
核心思路：用 CASE WHEN 按条件提取不同行的值作为列，再用 GROUP BY 聚合。示例场景

假设有一张学生成绩表 score，存储格式如下（行式存储，每行代表一个学生的一门成绩）：
studentsubjectscore张三语文80张三数学90张三英语85李四语文75李四数学95李四英语88
目标：转换为 “每个学生一行，各科成绩为列” 的格式（列式存储）：
student语文数学英语张三809085李四759588实现 SQL

sql  

1. SELECT
2.   student,
3.   -- 当科目为“语文”时，取分数作为“语文”列的值
4.   MAX(CASE WHEN subject = '语文' THEN score END) AS 语文,
5.   -- 当科目为“数学”时，取分数作为“数学”列的值
6.   MAX(CASE WHEN subject = '数学' THEN score END) AS 数学,
7.   -- 当科目为“英语”时，取分数作为“英语”列的值
8.   MAX(CASE WHEN subject = '英语' THEN score END) AS 英语
9. FROM score
10. GROUP BY student; -- 按学生分组，聚合出一行数据

复制代码

 
关键点：

• CASE WHEN 按条件将不同行的 score 映射到对应的列（如 “语文”“数学”）。
  
• 聚合函数 MAX()（或 SUM()，此处效果相同）用于将分组后的值合并为单行（非匹配条件的行返回 NULL，聚合时忽略 NULL）。
  
• GROUP BY student 确保每个学生只占一行。
  

动态行转列（列名不固定时）

如果 subject 字段的值不固定（如可能新增 “物理”“化学”），上述静态 SQL 需手动修改。此时可通过 存储过程动态生成 SQL 实现：
sql  

1. -- 生成动态行转列 SQL
2. SET @sql = NULL;
3. SELECT
4.   GROUP_CONCAT(
5.     DISTINCT CONCAT(
6.       'MAX(CASE WHEN subject = ''', subject, ''' THEN score END) AS ', subject
7.     )
8.   ) INTO @sql
9. FROM score;
11. SET @sql = CONCAT('SELECT student, ', @sql, ' FROM score GROUP BY student');
13. -- 执行动态 SQL
14. PREPARE stmt FROM @sql;
15. EXECUTE stmt;
16. DEALLOCATE PREPARE stmt;

复制代码

 
原理：用 GROUP_CONCAT 动态拼接 CASE WHEN 语句，适应未知的列名。二、列转行（Column to Row）

概念：将表中 “同一行的多列数据” 转换为 “同一维度的多行数据”。
核心思路：用 UNION ALL 将多列数据 “纵向堆叠” 为多行，或用 MySQL 8.0+ 的 JSON_TABLE 函数实现。示例场景

假设有一张学生成绩表 score_pivot，存储格式如下（列式存储，每行包含一个学生的所有成绩）：
student语文数学英语张三809085李四759588
目标：转换为 “每个学生每科一行” 的格式（行式存储）：
studentsubjectscore张三语文80张三数学90张三英语85李四语文75李四数学95李四英语88实现方法 1：UNION ALL（兼容所有版本）

sql  

1. -- 将“语文”列转为行
2. SELECT student, '语文' AS subject, 语文 AS score FROM score_pivot
3. UNION ALL
4. -- 将“数学”列转为行
5. SELECT student, '数学' AS subject, 数学 AS score FROM score_pivot
6. UNION ALL
7. -- 将“英语”列转为行
8. SELECT student, '英语' AS subject, 英语 AS score FROM score_pivot
9. ORDER BY student, subject;

复制代码

 
关键点：

• 每一个 SELECT 语句将一列数据转为多行（指定 subject 名称，对应列的值作为 score）。
  
• UNION ALL 合并所有结果（保留重复行，性能优于 UNION）。
  
• 最后通过 ORDER BY 调整排序。
  

实现方法 2：JSON_TABLE（MySQL 8.0+，更灵活）

对于列数较多的场景，UNION ALL 会导致 SQL 冗长。MySQL 8.0 引入的 JSON_TABLE 可通过 JSON 格式动态解析列：
sql  

1. SELECT
2.   s.student,
3.   j.subject,
4.   j.score
5. FROM score_pivot s
6. -- 将多列转为 JSON 数组
7. CROSS JOIN JSON_TABLE(
8.   JSON_ARRAY(
9.     JSON_OBJECT('subject', '语文', 'score', s.语文),
10.     JSON_OBJECT('subject', '数学', 'score', s.数学),
11.     JSON_OBJECT('subject', '英语', 'score', s.英语)
12.   ),
13.   '$[*]' COLUMNS (
14.     subject VARCHAR(20) PATH '$.subject',
15.     score INT PATH '$.score'
16.   )
17. ) j;

复制代码

 
原理：

• 先用 JSON_ARRAY 和 JSON_OBJECT 将多列数据转为 JSON 数组（每个元素包含 subject 和 score）。
  
• 再用 JSON_TABLE 解析 JSON 数组为多行数据，指定列名和数据类型。
  

三、总结

操作核心方法适用场景注意事项行转列CASE WHEN + 聚合函数（MAX）多行数据按维度合并为单行多列列名固定用静态 SQL，不固定用动态 SQL列转行UNION ALL 或 JSON_TABLE（8.0+）单行多列数据拆分为多行同一维度UNION ALL 兼容旧版本，JSON_TABLE 更简洁
行转列和列转行是数据重塑的常用手段，核心是通过 SQL 逻辑将 “行” 与 “列” 的维度进行转换，具体选择哪种方法需根据 MySQL 版本和实际业务场景决定。mysql  GROUP_CONCAT JSON_TABLE  JSON_ARRAY  JSON_OBJECT 在 MySQL 中，GROUP_CONCAT、JSON_TABLE、JSON_ARRAY、JSON_OBJECT 是处理字符串聚合和 JSON 数据的重要函数，常结合使用以实现复杂的数据转换（如关系型数据与 JSON 格式的互转）。以下是详细说明：一、GROUP_CONCAT：聚合字符串

用于将分组内的多行数据合并为单个字符串，常与 GROUP BY 配合使用，适合将 “多值” 聚合为逗号分隔的字符串或自定义格式。语法

sql  

1. GROUP_CONCAT([DISTINCT] 列名 [ORDER BY 排序字段] [SEPARATOR '分隔符'])

复制代码

 

• DISTINCT：可选，去重后再合并。
  
• ORDER BY：可选，指定合并前的排序规则。
  
• SEPARATOR：可选，指定分隔符（默认逗号 ,）。
  

示例

假设有 student_courses 表（学生选课关系）：
student_idcourse1数学1语文2英语2数学
需求：按学生分组，合并其选修的课程：
sql  

1. SELECT
2.   student_id,
3.   GROUP_CONCAT(course ORDER BY course SEPARATOR ';') AS courses  -- 按课程名排序，用;分隔
4. FROM student_courses
5. GROUP BY student_id;

复制代码

 
结果：
student_idcourses1数学；语文2数学；英语二、JSON_OBJECT：创建 JSON 对象

用于将键值对组合为 JSON 对象（格式：{"key1": value1, "key2": value2, ...}），适合将关系型数据转换为 JSON 格式。语法

sql  

1. JSON_OBJECT(key1, value1, key2, value2, ...)

复制代码

 

• key：字符串（JSON 键名）。
  
• value：任意数据类型（字符串、数字、NULL 等，会自动转换为 JSON 类型）。
  

示例

假设有 users 表：
idnameage1张三202李四25
需求：将用户信息转换为 JSON 对象：
sql  

1. SELECT
2.   id,
3.   JSON_OBJECT('name', name, 'age', age, 'is_adult', age >= 18) AS user_info
4. FROM users;

复制代码

 
结果：
iduser_info1{"name": "张三", "age": 20, "is_adult": 1}2{"name": "李四", "age": 25, "is_adult": 1}三、JSON_ARRAY：创建 JSON 数组

用于将多个值组合为 JSON 数组（格式：[value1, value2, ...]），适合将多个字段或聚合结果转换为数组。语法

sql  

1. JSON_ARRAY(value1, value2, ...)

复制代码

 

• value：任意数据类型（支持嵌套 JSON 对象或数组）。
  

示例

• 基础用法：
  

sql  

1. SELECT JSON_ARRAY(1, 'apple', TRUE, JSON_OBJECT('k', 'v')) AS json_arr;
2. -- 结果：[1, "apple", true, {"k": "v"}]

复制代码

 

• 结合 GROUP_CONCAT 生成数组：
  基于 student_courses 表，将每个学生的课程转为 JSON 数组：
  

sql  

1. SELECT
2.   student_id,
3.   JSON_ARRAY(GROUP_CONCAT(course)) AS courses_arr  -- 先合并为字符串，再转为数组
4. FROM student_courses
5. GROUP BY student_id;

复制代码

 
结果：
student_idcourses_arr 1["数学，语文"]-- 注意：此时数组内是单个字符串（因 GROUP_CONCAT 先合并）

• 更精确的数组（避免字符串拼接）：
  若需数组内每个元素独立（而非逗号分隔的字符串），可结合子查询与 JSON_ARRAYAGG（MySQL 5.7+ 聚合 JSON 数组的函数）：
  

sql  

1. SELECT
2.   student_id,
3.   JSON_ARRAYAGG(course ORDER BY course) AS courses_arr  -- 直接聚合为JSON数组
4. FROM student_courses
5. GROUP BY student_id;

复制代码

 
结果：
student_idcourses_arr 1["数学", "语文"]-- 数组内每个课程独立四、JSON_TABLE：JSON 转关系表

MySQL 8.0+ 新增函数，用于将JSON 数据（数组或对象）转换为关系型表结构，实现 JSON 与行 / 列的互转，是处理 JSON 数据的核心工具。语法

sql  

1. JSON_TABLE(
2.   json_data,  -- 输入的JSON数据（字段或JSON表达式）
3.   '$.path' COLUMNS (  -- JSON路径（如数组用$[*]，对象用$.key）
4.     列名1 数据类型 PATH '$.subpath1',
5.     列名2 数据类型 PATH '$.subpath2' [DEFAULT 默认值]  -- 可选：默认值
6.   )
7. ) AS 表别名

复制代码

 示例

• 将 JSON 数组转为表：
  假设有一个 JSON 数组 [{"id":1,"name":"苹果"}, {"id":2,"name":"香蕉"}]，需转换为行：
  

sql  

1. SELECT jt.id, jt.name
2. FROM JSON_TABLE(
3.   '[{"id":1,"name":"苹果"}, {"id":2,"name":"香蕉"}]',
4.   '$[*]' COLUMNS (  -- $[*] 表示数组中的所有元素
5.     id INT PATH '$.id',
6.     name VARCHAR(50) PATH '$.name'
7.   )
8. ) AS jt;

复制代码

 
结果：
idname1苹果2香蕉

• 解析表中存储的 JSON 字段：
  假设有 products 表，attrs 字段存储 JSON 数组（产品属性）：
  

product_idattrs100[{"color":"红","size":"M"}, {"color":"蓝","size":"L"}]
需求：解析 attrs 为多行属性：
sql  

1. SELECT
2.   p.product_id,
3.   jt.color,
4.   jt.size
5. FROM products p
6. CROSS JOIN JSON_TABLE(
7.   p.attrs,  -- 引用表中的JSON字段
8.   '$[*]' COLUMNS (
9.     color VARCHAR(20) PATH '$.color',
10.     size VARCHAR(10) PATH '$.size'
11.   )
12. ) AS jt;

复制代码

 
结果：
product_idcolorsize100红M100蓝L五、函数组合使用场景

场景 1：关系表 → JSON 数组（带对象）

将 student_courses 表转换为 “学生 ID + 课程列表（JSON 对象数组）”：
sql  

1. SELECT
2.   student_id,
3.   JSON_ARRAYAGG(
4.     JSON_OBJECT('course_name', course)  -- 每个课程转为对象
5.   ) AS courses
6. FROM student_courses
7. GROUP BY student_id;

复制代码

 
结果：
student_idcourses1[{"course_name": "数学"}, {"course_name": "语文"}]场景 2：JSON 数组 → 关系表 + 聚合

解析 JSON 数组后，用 GROUP_CONCAT 合并结果：
sql  

1. -- 先解析JSON数组为表，再合并课程名
2. SELECT
3.   GROUP_CONCAT(jt.course SEPARATOR ';') AS all_courses
4. FROM JSON_TABLE(
5.   '[{"course":"数学"}, {"course":"语文"}, {"course":"英语"}]',
6.   '$[*]' COLUMNS (course VARCHAR(20) PATH '$.course')
7. ) AS jt;

复制代码

 
结果：
all_courses数学；语文；英语总结

函数作用核心场景版本要求GROUP_CONCAT聚合多行为字符串多值合并（如标签、选课列表）全版本JSON_OBJECT创建 JSON 对象关系数据转 JSON 对象（如单条记录序列化）5.7+JSON_ARRAY创建 JSON 数组多值转 JSON 数组（如列表数据序列化）5.7+JSON_TABLEJSON 转关系表解析 JSON 字段为行 / 列（如处理 JSON 存储的数据）8.0+
这些函数配合使用，可灵活实现 “关系型数据 ↔ JSON 数据” 的转换，满足报表生成、API 数据格式处理、半结构化数据存储等复杂需求。 ---------------------------------------------------------------------------------------------------------------  left join on where在 MySQL 的 LEFT JOIN 中，ON 和 WHERE 都用于筛选数据，但它们的作用时机和效果有本质区别，理解两者的差异是写出正确关联查询的关键。一、核心区别

关键字作用时机对左表的影响对右表的影响ON关联时筛选（匹配阶段）左表所有记录全部保留只匹配满足条件的右表记录，不匹配则为 NULLWHERE关联后筛选（结果集阶段）可能过滤掉左表的记录（若条件不满足）已匹配的右表记录若不满足条件，整行可能被过滤二、示例说明

用以下两张表举例：

• users（左表）：用户信息idname1张三2李四3王五
  
• orders（右表）：订单信息order_iduser_idamountstatus1011100已付款1021200... 
  

left join  on and where在 MySQL 的 LEFT JOIN 中，ON 后可以通过 AND 附加条件，与 WHERE 子句的筛选逻辑不同，具体区别和用法如下：一、LEFT JOIN ON AND 的作用

ON 后的 AND 用于在关联阶段对右表进行条件筛选，即只匹配右表中同时满足关联条件和附加条件的记录。
核心特点：左表的所有记录仍会被保留，右表不满足 AND 条件的记录会显示为 NULL。二、LEFT JOIN ... WHERE 的作用

WHERE 用于在关联完成后对整个结果集进行筛选，会过滤掉所有不满足条件的行（包括左表记录）。
核心特点：可能会过滤掉左表中原本应保留的记录（若条件不匹配）。三、对比示例

用以下两张表举例：

• users（左表）：用户信息idname1张三2李四3王五
  
• orders（右表）：订单信息order_iduser_idamountstatus1011100已付款1021200未付款1033150已付款
  

示例 1：LEFT JOIN ON AND（关联时筛选右表）

需求：查询所有用户，以及他们的已付款订单（未付款订单不显示，但用户仍保留）
sql  

1. SELECT
2.   u.id, u.name, o.order_id, o.status
3. FROM users u
4. LEFT JOIN orders o
5.   ON u.id = o.user_id  -- 关联条件
6.   AND o.status = '已付款';  -- 附加筛选右表的条件

复制代码

 
结果：
idnameorder_idstatus 1张三101已付款-- 只匹配右表中已付款的订单2李四NULLNULL-- 左表记录保留，右表无匹配3王五103已付款-- 右表匹配已付款订单示例 2：LEFT JOIN ... WHERE（关联后筛选结果集）

需求：查询所有用户，且只保留有已付款订单的记录（无已付款订单的用户会被过滤）
sql  

1. SELECT
2.   u.id, u.name, o.order_id, o.status
3. FROM users u
4. LEFT JOIN orders o
5.   ON u.id = o.user_id  -- 关联条件
6. WHERE o.status = '已付款';  -- 对关联后的结果集筛选

复制代码

 
结果：
idnameorder_idstatus 1张三101已付款-- 满足条件3王五103已付款-- 满足条件2李四NULLNULL-- 被过滤（因 o.status 为 NULL，不满足条件）四、关键结论

• 保留左表全部记录：用 ON AND 筛选右表（即使右表无匹配，左表记录仍保留）。
  
• 过滤左表记录：用 WHERE 筛选最终结果（左表中不满足条件的记录会被移除）。
  
• 常见错误：若想保留左表所有记录，却在 WHERE 中对右表字段加非 NULL 条件（如 o.status = 'xxx'），会导致左表中无匹配的记录被过滤，效果等同于 INNER JOIN。
  

根据业务需求选择合适的筛选方式：需保留所有左表记录用 ON AND，需严格过滤结果集用 WHERE。---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！