快捷導(dǎo)航

MySQL中列值分割的幾種方法

更新時間：2025年09月29日 10:28:00 作者：一一Null

這篇文章主要介紹了MySQL中列值分割的幾種方法,針對不同場景提供SUBSTRING_INDEX、JSON_TABLE、正則表達(dá)式等多種解決方案,下面就來詳細(xì)的介紹一下,感興趣的可以了解一下

1. 核心函數(shù)速覽

函數(shù)	作用一句話	語法
SUBSTRING_INDEX(str, delim, n)	返回第 n 個分隔符前/后的子串	SUBSTRING_INDEX('a,b,c',',',2) → ‘a,b’
JSON_TABLE(json, path COLUMNS(…))	把 JSON 數(shù)組拆成行	見案例 4
REGEXP_SUBSTR / REGEXP_REPLACE	正則切分/替換	MySQL 8 支持，見案例 5

2. 案例實驗室

準(zhǔn)備一張表：

CREATE TABLE orders (
  id INT PRIMARY KEY,
  items VARCHAR(100)   -- 用逗號分隔的商品串
);

INSERT INTO orders VALUES
(1,'蘋果,香蕉,橙子'),
(2,'芒果'),
(3,'桃子,葡萄'),
(4,'');

案例 1　SUBSTRING_INDEX 取第 1、2、3 個元素

SELECT id,
       SUBSTRING_INDEX(items, ',', 1)                         AS item1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(items, ',', 2), ',', -1) AS item2,
       SUBSTRING_INDEX(items, ',', -1)                        AS item_last
FROM orders;

id	item1	item2	item_last
1	蘋果	香蕉	橙子
2	芒果	芒果	芒果
3	桃子	葡萄	葡萄
4

案例 2　一行變多行（數(shù)字表法）

用遞歸數(shù)字表（MySQL 8 CTE）把任意長度的逗號串拆成行。

WITH RECURSIVE nums(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n<20
)
SELECT o.id, o.items,
       TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(o.items, ',', n), ',', -1)) AS item
FROM orders o
JOIN nums
  ON n <= 1 + LENGTH(o.items) - LENGTH(REPLACE(o.items, ',', ''));

結(jié)果

id	items	item
1	蘋果,香蕉,橙子	蘋果
1	蘋果,香蕉,橙子	香蕉
1	蘋果,香蕉,橙子	橙子
2	芒果	芒果
3	桃子,葡萄	桃子
3	桃子,葡萄	葡萄

案例 3　JSON_TABLE（8.0 最優(yōu)雅）

把逗號串先轉(zhuǎn)成 JSON，再拆成行。

SELECT o.id, t.item
FROM orders o,
JSON_TABLE(
  CONCAT('["', REPLACE(items, ',', '","'), '"]'),  -- 變成 ["蘋果","香蕉","橙子"]
  "$[*]" COLUMNS(item VARCHAR(20) PATH "$")
) AS t;

結(jié)果與案例 2 完全一致，但寫法更短更清晰。

案例 4　正則切分（REGEXP_SUBSTR）

按任意正則分隔符拆列。

SELECT id,
       REGEXP_SUBSTR(items, '[^,]+', 1, 1) AS item1,
       REGEXP_SUBSTR(items, '[^,]+', 1, 2) AS item2,
       REGEXP_SUBSTR(items, '[^,]+', 1, 3) AS item3
FROM orders;

id	item1	item2	item3
1	蘋果	香蕉	橙子
2	芒果	NULL	NULL
3	桃子	葡萄	NULL
4	NULL	NULL	NULL

3. 課堂小結(jié)

場景	推薦方案
已知固定位置	SUBSTRING_INDEX 一步到位
任意長度串 → 行	遞歸 CTE + SUBSTRING_INDEX
MySQL 8.0	JSON_TABLE 最優(yōu)雅
復(fù)雜正則	REGEXP_SUBSTR / REGEXP_REPLACE

到此這篇關(guān)于MySQL中列值分割的幾種方法的文章就介紹到這了,更多相關(guān)MySQL 列值分割內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: