mysql group_concat coalesce 等链接函数

group_concat coalesce

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

1. GROUP_CONCAT

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

基本语法：

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

示例：
假设有一个 students 表：

id	name	class
1	Alice	A
2	Bob	A
3	Charlie	B

使用 GROUP_CONCAT 按班级合并学生姓名：

sql
 
SELECT class, GROUP_CONCAT(name SEPARATOR ', ') AS students
FROM students
GROUP BY class;

结果：

plaintext
 
class | students
------|----------------
A     | Alice, Bob
B     | Charlie

2. COALESCE

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

基本语法：

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

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

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

如果 phone 为 NULL，则返回 '未提供'。

3. 结合使用 GROUP_CONCAT 和 COALESCE

当 GROUP_CONCAT 处理的分组中所有值都是 NULL 时，会返回 NULL。此时可以用 COALESCE 将其转换为更友好的默认值。

示例：
假设有一个 scores 表（部分学生某科目成绩为 NULL）：

student_id	subject	score
1	Math	90
1	English	NULL
2	Math	NULL
2	English	NULL

按学生分组，合并成绩并处理 NULL：

sql
 
SELECT 
  student_id,
  COALESCE(GROUP_CONCAT(score), '无成绩') AS all_scores
FROM scores
GROUP BY student_id;

结果：

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

通过这种组合，可以更灵活地处理聚合结果中的空值情况。

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

在 MySQL 中，CASE WHEN 是一种条件判断语句，用于根据不同条件返回不同结果，类似于程序语言中的 if-else 或 switch 逻辑。它有两种常见用法：简单 CASE 表达式和搜索 CASE 表达式。

1. 简单 CASE 表达式

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

sql
 
CASE 表达式
  WHEN 值1 THEN 结果1
  WHEN 值2 THEN 结果2
  ...
  ELSE 默认结果
END

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

sql
 
 

SELECT 
  product_name,
  category,
  CASE category
    WHEN 'electronics' THEN '电子设备'
    WHEN 'clothing' THEN '服装'
    WHEN 'food' THEN '食品'
    ELSE '其他'
  END AS category_cn
FROM products;

 

结果：

product_name	category	category_cn
手机	electronics	电子设备
T 恤	clothing	服装
苹果	food	食品
花盆	NULL	其他

2. 搜索 CASE 表达式

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

sql
 
CASE
  WHEN 条件1 THEN 结果1
  WHEN 条件2 THEN 结果2
  ...
  ELSE 默认结果
END

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

sql
 
 

SELECT 
  name,
  score,
  CASE
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 80 THEN '良好'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS grade
FROM students;

 

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

sql
 
 

SELECT
  CASE
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 80 THEN '良好'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS grade,
  COUNT(*) AS count
FROM students
GROUP BY grade;

 

3. 注意事项

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

4. 实际应用示例

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

sql
 
 

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

CASE WHEN 是 MySQL 中处理条件逻辑的核心工具，灵活运用可满足各种复杂的业务判断需求。

case when exist

在 SQL 中，CASE WHEN 与 EXISTS 结合使用可以实现复杂的条件判断，尤其适合根据子查询结果返回不同值的场景。

基本用法

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

语法结构：

sql
 
CASE 
  WHEN EXISTS (子查询) THEN 结果1
  ELSE 结果2
END

示例说明

假设有以下两张表：

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

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

sql
 
 

SELECT 
  u.user_id,
  u.name,
  CASE 
    WHEN EXISTS (
      SELECT 1 FROM orders o 
      WHERE o.user_id = u.user_id
    ) THEN '有订单'
    ELSE '无订单'
  END AS has_order
FROM users u;

 

结果：

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

进阶用法：多条件判断

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

sql
 
 

SELECT 
  u.user_id,
  CASE 
    WHEN EXISTS (
      SELECT 1 FROM orders 
      WHERE user_id = u.user_id AND order_date >= '2023-01-01'
    ) THEN '今年有订单'
    WHEN EXISTS (
      SELECT 1 FROM orders 
      WHERE user_id = u.user_id
    ) THEN '历史有订单'
    ELSE '无任何订单'
  END AS order_status
FROM users u;

这种组合的优势在于：

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

注意：EXISTS 子查询中通常使用 SELECT 1 而非具体列名，因为只需要判断是否存在记录，不需要返回实际数据。

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

mysql 链接函数 CONCAT SUBSTRING

在 MySQL 中，CONCAT 和 SUBSTRING 是处理字符串的常用函数，常结合使用来实现复杂的字符串拼接与截取需求。

1. CONCAT 函数：字符串拼接

CONCAT 用于将多个字符串连接成一个字符串。

语法：

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

特点：

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

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

sql
 
-- 拼接姓和名
SELECT 
  first_name, 
  last_name,
  CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

结果：

plaintext
 
first_name | last_name | full_name
-----------|-----------|-----------
John       | Doe       | John Doe
Jane       | Smith     | Jane Smith

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

sql
 
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM users;

2. SUBSTRING 函数：字符串截取

SUBSTRING（别名 SUBSTR）用于从字符串中截取子串，有两种常用语法。

语法 1：指定起始位置和长度

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

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

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

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

示例：

sql
 
-- 截取前3个字符
SELECT SUBSTRING('Hello World', 1, 3); -- 结果：'Hel'

-- 从第7个字符开始截取到末尾
SELECT SUBSTRING('Hello World', 7); -- 结果：'World'

-- 从右侧第5个字符开始截取3个字符
SELECT SUBSTRING('Hello World', -5, 3); -- 结果：'orl'

-- 标准语法示例
SELECT SUBSTRING('Hello World' FROM 1 FOR 5); -- 结果：'Hello'

3. 结合使用 CONCAT 和 SUBSTRING

实际场景中常需要先截取再拼接，或先拼接再截取。

示例 1：隐藏手机号中间 4 位

sql
 
 

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

 

结果：

plaintext
 
phone        | hidden_phone
-------------|--------------
13812345678  | 138****5678
13987654321  | 139****4321

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

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

结果：

plaintext
 
email               | username
--------------------|----------
john.doe@example.com| john.doe_user
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
 
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. N=2（左侧第2个逗号左侧）：取前2个元素
SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', 2); 
-- 结果：'apple,banana'

-- 2. N=-2（右侧第2个逗号右侧）：取后2个元素
SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', -2); 
-- 结果：'orange,grape'

-- 3. N=1（左侧第1个逗号左侧）：取第1个元素
SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', 1); 
-- 结果：'apple'

-- 4. N=-1（右侧第1个逗号右侧）：取最后1个元素
SELECT SUBSTRING_INDEX('apple,banana,orange,grape', ',', -1); 
-- 结果：'grape'

 

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

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

sql
 
-- 1. 取用户名（@左侧，N=1）
SELECT SUBSTRING_INDEX('zhangsan@qq.com', '@', 1); 
-- 结果：'zhangsan'

-- 2. 取域名（@右侧，N=-1）
SELECT SUBSTRING_INDEX('zhangsan@qq.com', '@', -1); 
-- 结果：'qq.com'

-- 3. 进一步取域名的主域名（如从'qq.com'中取'qq'，分隔符为'.'，N=1）
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('zhangsan@qq.com', '@', -1), '.', 1); 
-- 结果：'qq'

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

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

sql
 
-- 1. 取协议后的主机名（第3个'/'右侧，N=-4）
SELECT SUBSTRING_INDEX('https://www.example.com/blog/2024/05', '/', 3); 
-- 结果：'https://www.example.com'

-- 2. 取最后一个路径段（年份+月份，N=-1）
SELECT SUBSTRING_INDEX('https://www.example.com/blog/2024/05', '/', -1); 
-- 结果：'05'

-- 3. 取倒数第二个路径段（年份，N=-2 后再取右侧）
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('https://www.example.com/blog/2024/05', '/', -2), '/', 1); 
-- 结果：'2024'

三、实际业务场景应用

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

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

sql
 
 

SELECT
  user_id,
  tags,
  -- 取第一个标签
  SUBSTRING_INDEX(tags, ',', 1) AS first_tag,
  -- 取最后一个标签
  SUBSTRING_INDEX(tags, ',', -1) AS last_tag,
  -- 取中间第二个标签（先取前2个，再取最后1个）
  SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', 2), ',', -1) AS second_tag
FROM user_tags;

 

结果：

user_id	tags	first_tag	last_tag	second_tag
1	体育，音乐，阅读	体育	阅读	音乐
2	电影，旅行	电影	旅行	旅行

示例 2：数据脱敏（截取手机号前 3 位和后 4 位，中间用 * 替换）

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

sql
 
 

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

 

结果：

phone	hidden_phone
13812345678	138****5678
13987654321	139****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 是用于正则表达式匹配的运算符，用于判断字符串是否符合指定的正则模式。它提供了比普通字符串匹配（如 LIKE）更灵活、更强大的模式匹配能力，适合处理复杂的文本检索场景。

一、基本语法

sql
 
字段名 REGEXP '正则模式'

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

二、常用匹配规则及示例

假设有 users 表，包含 username 和 email 字段，以下是常见用法：

1. 基础字符匹配

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

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

2. 位置匹配

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

sql
 
-- 查找 username 以 'li' 开头的用户（类似 LIKE 'li%'）
SELECT username FROM users WHERE username REGEXP '^li';

-- 查找 email 以 '.com' 结尾的用户（类似 LIKE '%.com'）
SELECT email FROM users WHERE email REGEXP '.com$';

-- 精确匹配（整个字符串完全等于模式）
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
 
-- 查找 username 中包含 'a'、'b' 或 'c' 的用户
SELECT username FROM users WHERE username REGEXP '[abc]';

-- 查找手机号以 138、139 或 188 开头的用户（假设 phone 字段为手机号）
SELECT phone FROM users WHERE phone REGEXP '^1(38|39|88)';

-- 查找 email 中包含非数字字符的用户
SELECT email FROM users WHERE email REGEXP '[^0-9]';

4. 量词匹配

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

sql
 
-- 查找 username 中包含连续 2 个及以上 'a' 的用户
SELECT username FROM users WHERE username REGEXP 'a{2,}';

-- 查找 6-11 位数字的手机号（简化版）
SELECT phone FROM users WHERE phone REGEXP '^[0-9]{6,11}$';

-- 查找 email 中包含字母后跟 0 个或 1 个数字的用户
SELECT email FROM users WHERE email REGEXP '[a-z][0-9]?';

5. 特殊字符匹配

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

sql
 
-- 查找 username 中包含 'a' 后跟任意字符再跟 'c' 的用户（如 'abc'、'a1c' 等）
SELECT username FROM users WHERE username REGEXP 'a.c';

-- 查找 email 域名为 qq.com 或 163.com 的用户
SELECT email FROM users WHERE email REGEXP '@(qq|163)\\.com$'; 
-- 注意：`.` 需转义为 `\\.`（MySQL 中需双重转义）

6. 区分大小写匹配

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

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

三、实际业务场景示例

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

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

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

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

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

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

四、注意事项

性能问题：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
 
-- 替换手机号中间4位为*
SELECT REGEXP_REPLACE(phone, '^(1[3-9])([0-9]{4})([0-9]{4})$', '\\1****\\3') AS hidden_phone 
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
 
SELECT 列1, 列2 FROM 表1
UNION
SELECT 列1, 列2 FROM 表2;

2. UNION ALL

功能：合并结果集但保留所有行（包括重复行）。

语法：

sql
 
SELECT 列1, 列2 FROM 表1
UNION ALL
SELECT 列1, 列2 FROM 表2;

示例对比

假设有 table_a 和 table_b 两张表：

table_a		table_b
id	name	id	name
1	a	2	b
2	b	3	c
3	c	3	c

sql
 
-- UNION 结果（去重）
SELECT id, name FROM table_a
UNION
SELECT id, name FROM table_b;

id	name
1	a
2	b
3	c

sql
 
-- UNION ALL 结果（保留重复）
SELECT id, name FROM table_a
UNION ALL
SELECT id, name FROM table_b;

id	name
1	a
2	b
3	c
2	b
3	c
3	c

注意事项

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

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

用于根据两个表的关联字段匹配数据，核心是确定 “主表” 和 “从表”。

1. LEFT JOIN（左连接）

功能：以左表为基准，返回左表所有记录，以及右表中与左表匹配的记录；右表无匹配时返回 NULL。

语法：

sql
 
SELECT 列名
FROM 左表
LEFT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

2. RIGHT JOIN（右连接）

功能：以右表为基准，返回右表所有记录，以及左表中与右表匹配的记录；左表无匹配时返回 NULL。

语法：

sql
 
SELECT 列名
FROM 左表
RIGHT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例说明

假设有 users（用户表）和 orders（订单表）：

users		orders
id	name	order_id	user_id
1	张三	101	1
2	李四	102	1
3	王五	103	3

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

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

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

name	order_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，存储格式如下（行式存储，每行代表一个学生的一门成绩）：

student	subject	score
张三	语文	80
张三	数学	90
张三	英语	85
李四	语文	75
李四	数学	95
李四	英语	88

目标：转换为 “每个学生一行，各科成绩为列” 的格式（列式存储）：

student	语文	数学	英语
张三	80	90	85
李四	75	95	88

实现 SQL

sql
 
 

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

 

关键点：

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

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

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

sql
 
 

-- 生成动态行转列 SQL
SET @sql = NULL;
SELECT 
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(CASE WHEN subject = ''', subject, ''' THEN score END) AS ', subject
    )
  ) INTO @sql
FROM score;

SET @sql = CONCAT('SELECT student, ', @sql, ' FROM score GROUP BY student');

-- 执行动态 SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

原理：用 GROUP_CONCAT 动态拼接 CASE WHEN 语句，适应未知的列名。

二、列转行（Column to Row）

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

示例场景

假设有一张学生成绩表 score_pivot，存储格式如下（列式存储，每行包含一个学生的所有成绩）：

student	语文	数学	英语
张三	80	90	85
李四	75	95	88

目标：转换为 “每个学生每科一行” 的格式（行式存储）：

student	subject	score
张三	语文	80
张三	数学	90
张三	英语	85
李四	语文	75
李四	数学	95
李四	英语	88

实现方法 1：`UNION ALL`（兼容所有版本）

sql
 
 

-- 将“语文”列转为行
SELECT student, '语文' AS subject, 语文 AS score FROM score_pivot
UNION ALL
-- 将“数学”列转为行
SELECT student, '数学' AS subject, 数学 AS score FROM score_pivot
UNION ALL
-- 将“英语”列转为行
SELECT student, '英语' AS subject, 英语 AS score FROM score_pivot
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
 
 

SELECT 
  s.student,
  j.subject,
  j.score
FROM score_pivot s
-- 将多列转为 JSON 数组
CROSS JOIN JSON_TABLE(
  JSON_ARRAY(
    JSON_OBJECT('subject', '语文', 'score', s.语文),
    JSON_OBJECT('subject', '数学', 'score', s.数学),
    JSON_OBJECT('subject', '英语', 'score', s.英语)
  ),
  '$[*]' COLUMNS (
    subject VARCHAR(20) PATH '$.subject',
    score INT PATH '$.score'
  )
) 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
 
GROUP_CONCAT([DISTINCT] 列名 [ORDER BY 排序字段] [SEPARATOR '分隔符'])

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

示例

假设有 student_courses 表（学生选课关系）：

student_id	course
1	数学
1	语文
2	英语
2	数学

需求：按学生分组，合并其选修的课程：

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

结果：

student_id	courses
1	数学；语文
2	数学；英语

二、JSON_OBJECT：创建 JSON 对象

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

语法

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

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

示例

假设有 users 表：

id	name	age
1	张三	20
2	李四	25

需求：将用户信息转换为 JSON 对象：

sql
 
SELECT 
  id,
  JSON_OBJECT('name', name, 'age', age, 'is_adult', age >= 18) AS user_info
FROM users;

结果：

id	user_info
1	{"name": "张三", "age": 20, "is_adult": 1}
2	{"name": "李四", "age": 25, "is_adult": 1}

三、JSON_ARRAY：创建 JSON 数组

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

语法

sql
 
JSON_ARRAY(value1, value2, ...)

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

示例

基础用法：

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

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

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

结果：

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

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

sql
 
SELECT 
  student_id,
  JSON_ARRAYAGG(course ORDER BY course) AS courses_arr  -- 直接聚合为JSON数组
FROM student_courses
GROUP BY student_id;

结果：

student_id	courses_arr
1	["数学", "语文"]	-- 数组内每个课程独立

四、JSON_TABLE：JSON 转关系表

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

语法

sql
 
 

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

 

示例

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

sql
 
 

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

 

结果：

id	name
1	苹果
2	香蕉

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

product_id	attrs
100	[{"color":"红","size":"M"}, {"color":"蓝","size":"L"}]

需求：解析 attrs 为多行属性：

sql
 
 

SELECT 
  p.product_id,
  jt.color,
  jt.size
FROM products p
CROSS JOIN JSON_TABLE(
  p.attrs,  -- 引用表中的JSON字段
  '$[*]' COLUMNS (
    color VARCHAR(20) PATH '$.color',
    size VARCHAR(10) PATH '$.size'
  )
) AS jt;

 

结果：

product_id	color	size
100	红	M
100	蓝	L

五、函数组合使用场景

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

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

sql
 
 

SELECT 
  student_id,
  JSON_ARRAYAGG(
    JSON_OBJECT('course_name', course)  -- 每个课程转为对象
  ) AS courses
FROM student_courses
GROUP BY student_id;

 

结果：

student_id	courses
1	[{"course_name": "数学"}, {"course_name": "语文"}]

场景 2：JSON 数组 → 关系表 + 聚合

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

sql
 
 

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

 

结果：

all_courses
数学；语文；英语

总结

函数	作用	核心场景	版本要求
`GROUP_CONCAT`	聚合多行为字符串	多值合并（如标签、选课列表）	全版本
`JSON_OBJECT`	创建 JSON 对象	关系数据转 JSON 对象（如单条记录序列化）	5.7+
`JSON_ARRAY`	创建 JSON 数组	多值转 JSON 数组（如列表数据序列化）	5.7+
`JSON_TABLE`	JSON 转关系表	解析 JSON 字段为行 / 列（如处理 JSON 存储的数据）	8.0+

这些函数配合使用，可灵活实现 “关系型数据 ↔ JSON 数据” 的转换，满足报表生成、API 数据格式处理、半结构化数据存储等复杂需求。

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

left join on where

在 MySQL 的 LEFT JOIN 中，ON 和 WHERE 都用于筛选数据，但它们的作用时机和效果有本质区别，理解两者的差异是写出正确关联查询的关键。

一、核心区别

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

二、示例说明

用以下两张表举例：

users（左表）：用户信息

id name

1 张三

2 李四

3 王五
orders（右表）：订单信息

order_id user_id amount status

101 1 100 已付款

102 1 200...

id	name
1	张三
2	李四
3	王五

order_id	user_id	amount	status
101	1	100	已付款
102	1	200...

left join on and where

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

一、`LEFT JOIN ON AND` 的作用

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

二、`LEFT JOIN ... WHERE` 的作用

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

三、对比示例

用以下两张表举例：

users（左表）：用户信息

id name

1 张三

2 李四

3 王五
orders（右表）：订单信息

order_id user_id amount status

101 1 100 已付款

102 1 200 未付款

103 3 150 已付款

id	name
1	张三
2	李四
3	王五

order_id	user_id	amount	status
101	1	100	已付款
102	1	200	未付款
103	3	150	已付款

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

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

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

结果：

id	name	order_id	status
1	张三	101	已付款	-- 只匹配右表中已付款的订单
2	李四	NULL	NULL	-- 左表记录保留，右表无匹配
3	王五	103	已付款	-- 右表匹配已付款订单

示例 2：`LEFT JOIN ... WHERE`（关联后筛选结果集）

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

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

结果：

id	name	order_id	status
1	张三	101	已付款	-- 满足条件
3	王五	103	已付款	-- 满足条件
2	李四	NULL	NULL	-- 被过滤（因 o.status 为 NULL，不满足条件）

四、关键结论

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

根据业务需求选择合适的筛选方式：需保留所有左表记录用 ON AND，需严格过滤结果集用 WHERE。

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

posted @ 2025-08-28 14:39 hanease 阅读(29) 评论(0) 收藏举报

刷新页面返回顶部

hanease

mysql group_concat coalesce 等链接函数

1. GROUP_CONCAT

2. COALESCE

3. 结合使用 GROUP_CONCAT 和 COALESCE

1. 简单 CASE 表达式

2. 搜索 CASE 表达式

3. 注意事项

4. 实际应用示例

基本用法

示例说明

进阶用法：多条件判断

1. CONCAT 函数：字符串拼接

2. SUBSTRING 函数：字符串截取

语法 1：指定起始位置和长度

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

3. 结合使用 CONCAT 和 SUBSTRING

4. 注意事项

一、基本语法

二、核心用法示例

场景 1：逗号分隔的字符串（如标签、多选值）

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

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

三、实际业务场景应用

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

示例 2：数据脱敏（截取手机号前 3 位和后 4 位，中间用 * 替换）

四、注意事项

五、常见组合函数

一、基本语法

二、常用匹配规则及示例

1. 基础字符匹配

2. 位置匹配

3. 字符集与范围

4. 量词匹配

5. 特殊字符匹配

6. 区分大小写匹配

三、实际业务场景示例

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

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

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

四、注意事项

五、扩展函数（MySQL 8.0+）

一、UNION 与 UNION ALL：合并结果集

1. UNION

2. UNION ALL

示例对比

注意事项

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

1. LEFT JOIN（左连接）

2. RIGHT JOIN（右连接）

示例说明

注意事项

三、核心区别总结

一、行转列（Row to Column）

示例场景

实现 SQL

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

二、列转行（Column to Row）

示例场景

实现方法 1：UNION ALL（兼容所有版本）

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

三、总结

一、GROUP_CONCAT：聚合字符串

语法

示例

二、JSON_OBJECT：创建 JSON 对象

语法

示例

三、JSON_ARRAY：创建 JSON 数组

语法

示例

四、JSON_TABLE：JSON 转关系表

语法

示例

五、函数组合使用场景

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

场景 2：JSON 数组 → 关系表 + 聚合

总结

一、核心区别

二、示例说明

实现方法 1：`UNION ALL`（兼容所有版本）

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

一、`LEFT JOIN ON AND` 的作用

二、`LEFT JOIN ... WHERE` 的作用

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

示例 2：`LEFT JOIN ... WHERE`（关联后筛选结果集）