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 閱讀(34) 評論(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`（關聯后篩選結果集）