MySQL中列值分割的几种方法

目录

• 1. 核心函数速览
• 2. 案例实验室
• 案例 1　SUBSTRING_INDEX 取第 1、2、3 个元素
• 案例 2　一行变多行（数字表法）
• 案例 3　jsON_TABLE（8.0 最优雅）
• 案例 4　正则切分（REGEXP_SUBSTR）
• 3. 课堂小结

版本：mysql 8.x

MySQL 没有 split() 这样的函数，但可以用 SUBSTRING_INDEX 或 JSON_TABLE 实现“按分隔符拆列”。

下面给出 官方推荐 + 实战写法，每个都能直接复制运行。

1. 核心函数速览

函数	作用一句话	语法
SUBSTRING_INDEX(str, delim, n)	返回第 n 个分隔符前/后的子串	SUBSTRING_INDEX('a,b,c',',',2) → ‘a,b’
JSON_TABLE(json, path COLUMNS(…))	把 JSON 数组拆成行	见案例 4
REGEXP_SUBSTR / REGEXP_REPLACE	正则切分/替换	MySQL 8 支持，见案例 5

2. 案例实验室

准备一张表：

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)        tVWkqTEl                AS item_last
FROM orders;

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

案例 2　一行变多行（数字表法）

用递归数字表（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, ',php', ''));

结果

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

案例 3　JSON_TABLE（8.0 最优雅）

把逗号串先转成 JSON，再拆成行。

SELECT o.id, t.item
FROM orders o,
JSON_TABLE(
  CONCAT('["', REPLACE(items, ',', '","'), '"]'),  -- 变成 ["苹果","香蕉","橙子"]
  "$[*]" COL编程客栈UMNS(item VARCHAR(20) PATH "$")
) AS t;

结果与案例 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. 课堂小结

场景	推荐方案
已知固定位置	SUBSTRING_INDEX 一步到位
任意长度串 → 行	递归 CTE + SUBSTRING_INDEX
MySQL 8.0	JSON_www.devze.comTABLE 最优雅
复杂正则	REGEXP_SUBSTR / REGEXP_REPLACE

到此这篇关于MySQL中列值分割的几种方法的文章就介绍到这了,更多相关MySQL 列值分割内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！