MySQL如何拆分字符串？详细教程 – wiki基地

MySQL字符串拆分完全指南：方法、实战与解析

在数据库操作中，我们经常会遇到需要处理存储在单个字段中的列表状数据的情况。例如，一个订单表中的某个字段可能存储了购买的商品ID列表，或者一个用户表中的某个字段存储了用户的兴趣标签，这些数据通常使用逗号、分号或其他分隔符连接在一起。当我们需要对这些单个项进行查询、统计或关联时，就需要将这个字符串“拆分”成单独的元素。

然而，与某些其他数据库系统（如SQL Server的STRING_SPLIT函数）不同，MySQL并没有提供一个直接内置的、能够轻松将字符串按分隔符拆分成多行的函数。这给开发者带来了一定的挑战。但这并不意味着我们无法在MySQL中实现字符串拆分。通过结合使用MySQL提供的其他字符串函数、控制流语句、辅助表、递归CTE甚至JSON函数，我们可以构建出多种有效的字符串拆分解决方案。

本文将深入探讨在MySQL中拆分字符串的各种方法，从简单的场景到复杂的应用，提供详细的教程、代码示例以及每种方法的优缺点分析，帮助您在实际工作中选择最适合的方案。

理解挑战：为什么MySQL没有直接的SPLIT函数？

在开始学习如何拆分之前，了解为什么这个问题在MySQL中显得有些棘手是重要的。许多数据库系统遵循ANSI SQL标准，但对于字符串处理函数，各家有不同的实现。SQL Server在较新的版本中引入了STRING_SPLIT函数，它非常直观：传入字符串和分隔符，它就返回一个包含拆分后元素的单列表。

sql -- SQL Server 示例 SELECT value FROM STRING_SPLIT('apple,banana,cherry', ',');

这个函数直接返回一个“表”结构，非常符合关系型数据库处理集合数据的思想。

但MySQL的标准函数库设计哲学不同，它提供了丰富的单个字符串处理函数（如截取、查找、替换），但缺乏直接将一个标量值（字符串）转换成一个集合值（多行）的内置函数。因此，在MySQL中实现字符串拆分，本质上是需要我们通过组合现有功能来模拟这个转换过程。

方法一：使用 `SUBSTRING_INDEX` 获取指定位置的元素

在某些简单的场景下，我们并不需要将整个字符串拆分成所有元素，而只需要获取第一个、最后一个或者特定位置的元素。这时，SUBSTRING_INDEX 函数就显得非常方便。

SUBSTRING_INDEX(str, delim, count) 函数的作用是：

如果 count 是正数，它返回 str 中在第 count 个 delim 出现之前的子字符串。
如果 count 是负数，它返回 str 中在第 count 个 delim (从右边开始计数) 出现之后的子字符串。

示例：获取第一个元素

sql SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', 1); -- 结果: apple

这里，count 为 1，表示查找第一个逗号，并返回其左边的部分。

示例：获取最后一个元素

sql SELECT SUBSTRING_INDEX('apple,banana,cherry', ',', -1); -- 结果: cherry

这里，count 为 -1，表示从右边开始查找第一个逗号，并返回其右边的部分。

示例：获取第二个元素

获取第二个元素稍微复杂一些。我们可以先使用 SUBSTRING_INDEX 截取到第二个分隔符之前的部分，然后再对结果使用 SUBSTRING_INDEX 结合负数 count 来获取最后一个（即第二个）元素。

“`sql
— 截取到第二个逗号之前的部分: ‘apple,banana’
SELECT SUBSTRING_INDEX(‘apple,banana,cherry’, ‘,’, 2);
— 结果: apple,banana

— 对 ‘apple,banana’ 使用 SUBSTRING_INDEX 结合 -1:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘apple,banana,cherry’, ‘,’, 2), ‘,’, -1);
— 结果: banana
“`

示例：获取倒数第二个元素

类似地，获取倒数第二个元素可以先截取到倒数第二个分隔符之后的部分，再取其第一个元素。

“`sql
— 截取到倒数第二个逗号之后的部分: ‘banana,cherry’
SELECT SUBSTRING_INDEX(‘apple,banana,cherry’, ‘,’, -2);
— 结果: banana,cherry

— 对 ‘banana,cherry’ 使用 SUBSTRING_INDEX 结合 1:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘apple,banana,cherry’, ‘,’, -2), ‘,’, 1);
— 结果: banana
“`

在查询中使用 SUBSTRING_INDEX

假设我们有一个表 products，其中 tags 字段存储了以逗号分隔的标签：

“`sql
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
tags VARCHAR(255)
);

INSERT INTO products (name, tags) VALUES
(‘Laptop’, ‘electronics,computer,portable’),
(‘Book’, ‘literature,fiction,novel’),
(‘T-Shirt’, ‘apparel,clothing,cotton’);
“`

我们可以使用 SUBSTRING_INDEX 获取每个商品的第一个或最后一个标签：

sql SELECT name, SUBSTRING_INDEX(tags, ',', 1) AS first_tag, SUBSTRING_INDEX(tags, ',', -1) AS last_tag FROM products;

优点：

简单易懂，语法直观。
效率较高，适合获取少数特定位置的元素。
是MySQL内置函数，无需额外创建对象。

缺点：

只能获取指定位置的元素，无法一次性获取所有元素并作为多行返回。
获取中间元素需要嵌套 SUBSTRING_INDEX，代码可读性下降。
无法处理连续的分隔符（'a,,b'），SUBSTRING_INDEX 会将其视为一个空元素之间的分隔符，但难以精确控制获取哪个空元素。

适用场景：

只需要获取字符串的第一个或最后一个元素，或者少量已知位置的元素。

方法二：结合 `SUBSTRING` 和 `LOCATE` 手动查找与截取（基础逻辑）

更底层的方法是使用 LOCATE 函数查找分隔符的位置，然后使用 SUBSTRING 函数根据位置截取子字符串。这个方法是许多高级拆分技术的基础逻辑，但单独使用时非常繁琐，通常需要在存储过程、函数或应用代码中通过循环来实现。

LOCATE(substr, str) 函数返回 substr 在 str 中第一次出现的位置。LOCATE(substr, str, pos) 返回 substr 在 str 中从位置 pos 开始第一次出现的位置。

SUBSTRING(str, pos, len) 函数返回从 str 的 pos 位置开始，长度为 len 的子字符串。SUBSTRING(str, pos) 返回从 str 的 pos 位置开始到末尾的子字符串。

基本逻辑示例（获取第一个元素）：

找到第一个分隔符的位置。
如果找到，截取从开始到分隔符位置的子字符串。
如果没找到，整个字符串就是一个元素。

“`sql
SET @str = ‘apple,banana,cherry’;
SET @delim = ‘,’;

SET @pos = LOCATE(@delim, @str);

SELECT
IF(@pos > 0, — 如果找到分隔符
SUBSTRING(@str, 1, @pos – 1), — 截取分隔符之前的部分
@str — 否则，整个字符串就是结果
);
— 结果: apple
“`

基本逻辑示例（获取第二个元素）：

找到第一个分隔符的位置 (pos1).
从 pos1 + 1 的位置开始，找到第二个分隔符的位置 (pos2).
如果 pos1 和 pos2 都找到，截取从 pos1 + 1 到 pos2 - 1 的子字符串。
如果只找到 pos1，且后面没有分隔符，则从 pos1 + 1 到末尾是第二个元素。

“`sql
SET @str = ‘apple,banana,cherry’;
SET @delim = ‘,’;

SET @pos1 = LOCATE(@delim, @str);
SET @result = NULL;

IF @pos1 > 0 THEN
SET @remaining_str = SUBSTRING(@str, @pos1 + 1);
SET @pos2 = LOCATE(@delim, @remaining_str);

SELECT
    IF(@pos2 > 0, -- 如果找到第二个分隔符
       SUBSTRING(@remaining_str, 1, @pos2 - 1), -- 截取第二个分隔符之前的部分
       @remaining_str -- 否则，剩余部分就是第二个元素
    );

ELSE
SELECT NULL; — 没有第一个分隔符，就没有第二个元素
END IF;
— 结果: banana
“`

优点：

这是所有更高级方法的基础原理。
灵活性高，可以精确控制截取逻辑。

缺点：

手动实现获取所有元素非常繁琐，需要大量的 LOCATE 和 SUBSTRING 调用，难以写成简洁的SQL查询。
通常需要结合循环（在存储过程/函数中）或外部应用代码实现，无法作为纯粹的SQL查询一次性返回所有行。

适用场景：

理解拆分底层原理，或者需要在存储过程/函数中通过循环逐个处理元素时。不适合作为独立的查询语句来获取所有拆分结果。

方法三：利用辅助表（Numbers Table）进行拆分

这是在MySQL中将字符串拆分成多行的经典且强大的方法之一，尤其在MySQL 8.0之前。其核心思想是创建一个包含一系列连续数字的“辅助表”（或称为“数字表”），然后将包含待拆分字符串的表与这个辅助表进行交叉连接。辅助表中的每个数字代表字符串中可能出现的一个元素的位置（或索引）。通过结合 SUBSTRING_INDEX 或 LOCATE/SUBSTRING 和辅助表中的数字，我们可以提取出对应位置的元素。

创建辅助表

最简单的方法是手动创建或使用一种方法生成一个足够大的数字序列。数字的数量应该大于你预期的字符串中可能包含的最大元素数。

“`sql
— 创建一个简单的数字表
CREATE TABLE numbers (n INT PRIMARY KEY);

— 插入数字，例如从 1 到 100
INSERT INTO numbers (n) VALUES
(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); — 可以插入更多…

— 或者使用更动态的方法生成数字 (MySQL 8.0+)
/
INSERT INTO numbers (n)
WITH RECURSIVE seq (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 100
)
SELECT n FROM seq;
/

— 或者利用系统表生成 (不同版本或配置可能不同)
/
— 示例: 利用information_schema.columns表（假设它的行数够多）
INSERT INTO numbers (n)
SELECT @row_number := @row_number + 1
FROM information_schema.columns, (SELECT @row_number := 0) AS t
LIMIT 100;
/
“`

使用辅助表拆分字符串

现在，我们可以使用辅助表和 SUBSTRING_INDEX 来拆分字符串。

“`sql
— 示例数据
CREATE TEMPORARY TABLE StringTable (id INT, str VARCHAR(255));
INSERT INTO StringTable VALUES
(1, ‘apple,banana,cherry’),
(2, ‘one;two;three;four’),
(3, ‘a|b’),
(4, ‘singleitem’),
(5, NULL), — Test NULL
(6, ”); — Test empty string

— 辅助表 numbers 已经创建并填充

— 拆分查询
SELECT
st.id,
st.str AS original_string,
n.n AS element_index,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(st.str, ‘,’, n.n), ‘,’, -1)) AS element — 使用SUBSTRING_INDEX
FROM
StringTable st
JOIN
numbers n ON n.n <= LENGTH(st.str) – LENGTH(REPLACE(st.str, ‘,’, ”)) + 1 — 限制数字表的范围
WHERE st.str IS NOT NULL AND st.str != ”; — 排除 NULL 和空字符串
ORDER BY st.id, n.n;
“`

解释：

StringTable st JOIN numbers n: 我们将待拆分字符串的表 StringTable 与 numbers 表进行交叉连接。对于 StringTable 中的每一行，它都会与 numbers 表中的每一行 n 进行匹配。
ON n.n <= LENGTH(st.str) - LENGTH(REPLACE(st.str, ',', '')) + 1: 这是一个关键的过滤条件。
- LENGTH(st.str) 是原始字符串的总长度。
- REPLACE(st.str, ',', '') 是移除所有分隔符后的字符串。
- LENGTH(REPLACE(st.str, ',', '')) 是移除分隔符后的字符串长度。
- LENGTH(st.str) - LENGTH(REPLACE(st.str, ',', '')) 得到的是分隔符 ‘,’ 的数量。
- 分隔符的数量 + 1 就是元素的数量。这个条件确保我们只连接到小于或等于元素总数的数字 n，避免生成过多的 NULL 行。
SUBSTRING_INDEX(st.str, ',', n.n): 获取到第 n.n 个分隔符之前的所有内容。
SUBSTRING_INDEX(..., ',', -1): 对上一步的结果，从右边开始获取第一个分隔符之后的内容，这正是第 n.n 个元素。
TRIM(...): 移除元素可能存在的首尾空格。
WHERE st.str IS NOT NULL AND st.str != '': 过滤掉 NULL 或空字符串，避免计算错误或返回不期望的结果。

处理其他分隔符：

只需要将查询中的 ‘,’ 替换为你的实际分隔符即可。

处理连续分隔符 (e.g., ‘a,,b’)：

上述 SUBSTRING_INDEX 方法会自动将连续分隔符之间的空字符串识别为一个元素。如果你的逻辑需要这样处理（例如 'a,,b' 拆分成 a, '', b），这个方法是合适的。如果不需要，你可能需要在拆分前使用 REPLACE(REPLACE(string, delimiter, '##'), '##,##', '##') 之类的方法先清理连续分隔符（将连续的 , 替换成单个 ,），或者在结果中过滤掉空字符串。

优点：

兼容性好，在MySQL 8.0之前是拆分多行的主要方法。
作为纯SQL查询，可以在各种客户端工具中直接执行。
性能通常不错，特别是当辅助表被正确索引且大小适中时。

缺点：

需要一个预先存在的辅助表，需要维护（确保数字足够大）。
对于非常长的字符串或包含大量分隔符的字符串，计算元素的数量 (LENGTH - LENGTH(REPLACE)) 可能有性能开销。
计算元素时需要嵌套 SUBSTRING_INDEX 或结合 LOCATE/SUBSTRING，代码相对复杂。

适用场景：

在MySQL 8.0之前的版本，或者需要将字符串拆分成多行并在SQL查询中直接使用的场景。适用于需要处理大量字符串拆分的ETL过程或报告查询。

方法四：使用递归CTE（Common Table Expressions）（MySQL 8.0+）

MySQL 8.0及更高版本支持递归CTE，这为字符串拆分提供了一种无需辅助表、更加标准SQL风格的解决方案。递归CTE的工作原理是定义一个初始结果集（锚成员），然后通过一个或多个递归成员不断地基于前一步的结果集生成新的行，直到满足终止条件。

对于字符串拆分，我们可以这样设计递归CTE：

锚成员： 提取字符串的第一个元素，并计算剩余的字符串。
递归成员： 从上一步剩余的字符串中提取下一个元素，并计算新的剩余字符串，直到剩余字符串为空。

递归CTE拆分查询示例：

“`sql
— 示例数据 (与方法三相同)
/
CREATE TEMPORARY TABLE StringTable (id INT, str VARCHAR(255));
INSERT INTO StringTable VALUES
(1, ‘apple,banana,cherry’),
(2, ‘one;two;three;four’),
(3, ‘a|b’),
(4, ‘singleitem’),
(5, NULL), — Test NULL
(6, ”); — Test empty string
/

SET @delimiter = ‘,’; — 定义分隔符

WITH RECURSIVE SplitStringCTE AS (
— 锚成员：处理第一个元素
SELECT
id,
str,
SUBSTRING_INDEX(str, @delimiter, 1) AS element,
SUBSTRING(str FROM LENGTH(SUBSTRING_INDEX(str, @delimiter, 1)) + 1) AS remaining_str, — 截取第一个分隔符之后的部分
1 AS element_index — 元素索引
FROM StringTable
WHERE str IS NOT NULL AND str != ” — 排除 NULL 和空字符串

UNION ALL

-- 递归成员：处理后续元素
SELECT
    cte.id,
    cte.str,
    SUBSTRING_INDEX(cte.remaining_str, @delimiter, 1) AS element,
    SUBSTRING(cte.remaining_str FROM LENGTH(SUBSTRING_INDEX(cte.remaining_str, @delimiter, 1)) + 1) AS remaining_str,
    cte.element_index + 1 AS element_index
FROM SplitStringCTE cte
WHERE cte.remaining_str != '' -- 递归终止条件：剩余字符串为空

)
— 最终查询：从CTE中选择结果
SELECT
id,
original_string, — 可以保留原始字符串列，或者只选择id和element
element_index,
TRIM(element) AS element_value — 清理可能的空格
FROM SplitStringCTE
ORDER BY id, element_index;
“`

解释：

WITH RECURSIVE SplitStringCTE AS (...): 定义一个名为 SplitStringCTE 的递归CTE。
锚成员 (SELECT ... FROM StringTable ...):
- 选取原始 StringTable 中的行（排除NULL和空）。
- 使用 SUBSTRING_INDEX(str, @delimiter, 1) 提取第一个元素。
- 使用 SUBSTRING(str FROM LENGTH(SUBSTRING_INDEX(str, @delimiter, 1)) + 1) 计算并截取第一个分隔符之后的所有内容作为 remaining_str。
- 将 element_index 初始化为 1。
UNION ALL: 连接锚成员和递归成员的结果集。
递归成员 (SELECT ... FROM SplitStringCTE cte ... WHERE ...):
- 从上一步 SplitStringCTE 的结果集中选择行（别名为 cte）。
- 对 cte.remaining_str 重复锚成员的逻辑：提取其第一个元素 (SUBSTRING_INDEX(cte.remaining_str, @delimiter, 1)) 和其剩余字符串 (SUBSTRING(...))。
- 将 element_index 加一。
- WHERE cte.remaining_str != '': 这是递归的终止条件。当 remaining_str 为空时，表示当前行没有更多的分隔符，递归停止。
最终查询 (SELECT ... FROM SplitStringCTE ...): 从CTE生成的所有行中选择所需的列，并按 id 和 element_index 排序以保持原始顺序。TRIM 用于清理可能存在的空格。

处理连续分隔符 (e.g., ‘a,,b’)：

与辅助表方法类似，这个CTE也会将连续分隔符之间的空字符串识别为一个元素。如果 'a,,b' 中有两个逗号，第一个元素是 'a'，剩余 '',b'；递归后从 '',b' 中提取第一个元素是 ''，剩余 ',b'；再次递归从 ',b' 中提取第一个元素是 '' (这可能不是期望的)，剩余 'b'；最后提取 'b'。这可能需要调整递归逻辑来跳过空的 remaining_str 或者在最终结果中过滤。一个改进的递归逻辑可能需要更复杂的 LOCATE/SUBSTRING 组合或额外的逻辑来跳过连续分隔符。

优点：

无需辅助表，代码更加自包含。
符合标准SQL的递归概念，可读性相对较好（一旦熟悉CTE）。
在MySQL 8.0+ 中是推荐的拆分多行方法之一。

缺点：

只适用于 MySQL 8.0 及更高版本。
对于非常长的字符串或非常深的递归层级，性能可能不如辅助表方法（具体取决于MySQL的CTE实现优化）。
处理连续分隔符或首尾分隔符可能需要更精细的逻辑调整。

适用场景：

MySQL 8.0+ 用户，需要将字符串拆分成多行，并且希望避免使用或维护辅助表。适合在报告查询、数据转换等场景中使用。

方法五：使用存储函数或存储过程

将拆分逻辑封装在存储函数或存储过程中可以提高代码的重用性，并隐藏实现细节。

存储函数： 通常返回一个标量值。可以用来编写一个函数，返回字符串中 指定索引 的元素。
存储过程： 可以执行更复杂的操作，可以返回一个结果集（通过临时表或游标）。更适合用于将字符串拆分成多行。

存储函数示例（返回指定索引的元素）：

这个函数可以使用 SUBSTRING_INDEX 或 LOCATE/SUBSTRING 组合来实现。这里我们用 SUBSTRING_INDEX 简单实现。

“`sql
DELIMITER $$

CREATE FUNCTION GetElementByIndex(
p_str VARCHAR(255),
p_delim VARCHAR(10),
p_index INT
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
— 处理索引小于1的情况
IF p_index < 1 THEN
RETURN NULL;
END IF;

-- 使用SUBSTRING_INDEX获取
RETURN TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p_str, p_delim, p_index), p_delim, -1));

END$$

DELIMITER ;

— 调用示例
SELECT GetElementByIndex(‘apple,banana,cherry’, ‘,’, 1); — apple
SELECT GetElementByIndex(‘apple,banana,cherry’, ‘,’, 2); — banana
SELECT GetElementByIndex(‘apple,banana,cherry’, ‘,’, 3); — cherry
SELECT GetElementByIndex(‘apple,banana,cherry’, ‘,’, 4); — (空或原始字符串取决于SUBSTRING_INDEX行为)
SELECT GetElementByIndex(‘a|b’, ‘|’, 2); — b
SELECT GetElementByIndex(‘singleitem’, ‘,’, 1); — singleitem
“`

存储过程示例（将字符串拆分成多行）：

存储过程可以使用循环来迭代查找分隔符并插入到临时表中，然后返回临时表的结果。这通常比在纯查询中手动组合 LOCATE/SUBSTRING 更易读和维护。

“`sql
DELIMITER $$

CREATE PROCEDURE SplitStringIntoRows(
IN p_str VARCHAR(1000), — 允许较长字符串
IN p_delim VARCHAR(10)
)
BEGIN
— 确保临时表不存在，或者使用会话级别的临时表
DROP TEMPORARY TABLE IF EXISTS TempSplitTable;

-- 创建临时表存储拆分结果
CREATE TEMPORARY TABLE TempSplitTable (
    element_index INT AUTO_INCREMENT PRIMARY KEY,
    element_value VARCHAR(255)
);

-- 如果输入字符串为空或NULL，直接返回空结果集
IF p_str IS NULL OR p_str = '' THEN
    SELECT element_index, element_value FROM TempSplitTable;
    RETURN;
END IF;

SET @remaining_str = p_str;
SET @delim_len = LENGTH(p_delim);
SET @pos = 0;
SET @token = '';

-- 循环查找和截取
WHILE @remaining_str != '' DO
    SET @pos = LOCATE(p_delim, @remaining_str); -- 查找下一个分隔符

    IF @pos = 0 THEN
        -- 没有找到分隔符，剩余整个字符串是最后一个元素
        SET @token = @remaining_str;
        SET @remaining_str = ''; -- 终止循环
    ELSE
        -- 找到分隔符，截取分隔符之前的部分作为元素
        SET @token = SUBSTRING(@remaining_str, 1, @pos - 1);
        -- 更新剩余字符串为分隔符之后的部分
        SET @remaining_str = SUBSTRING(@remaining_str, @pos + @delim_len);
    END IF;

    -- 将提取的元素插入临时表
    INSERT INTO TempSplitTable (element_value) VALUES (TRIM(@token));
END WHILE;

-- 返回临时表的所有结果
SELECT element_index, element_value FROM TempSplitTable;

END$$

DELIMITER ;

— 调用示例
CALL SplitStringIntoRows(‘apple,banana,cherry’, ‘,’);
CALL SplitStringIntoRows(‘one;two;three;four’, ‘;’);
CALL SplitStringIntoRows(‘a|b’, ‘|’);
CALL SplitStringIntoRows(‘singleitem’, ‘,’); — 只有一个元素的情况
CALL SplitStringIntoRows(”, ‘,’); — 空字符串情况
CALL SplitStringIntoRows(NULL, ‘,’); — NULL情况
CALL SplitStringIntoRows(‘a,,b’, ‘,’); — 连续分隔符情况

— 连续分隔符 ‘a,,b’ 会被拆分成 ‘a’, ”, ‘b’
“`

解释：

创建临时表 TempSplitTable 来存储拆分结果。
使用 WHILE 循环，条件是 @remaining_str 不为空。
在循环内，使用 LOCATE 查找下一个分隔符的位置。
根据 LOCATE 的结果，使用 SUBSTRING 提取当前元素（分隔符之前的部分）和更新 @remaining_str（分隔符之后的部分）。
将提取的元素 INSERT 到临时表中。
循环结束后，从临时表中 SELECT 所有行返回结果集。

处理连续分隔符 (a,,b)：

这个存储过程的逻辑会正确处理连续分隔符。例如，a,,b：
* 第一次循环：@pos 找到第一个 ,。@token 是 'a'。@remaining_str 变成 ',b'。插入 'a'。
* 第二次循环：@pos 找到 '',b' 中的第一个 ,。@token 是空字符串 '' (SUBSTRING('', 1, 0))。@remaining_str 变成 'b'。插入 ''。
* 第三次循环：@pos 在 'b' 中找不到 ,，为 0。@token 是 'b'。@remaining_str 变成 ''。插入 'b'。
* 循环终止。结果是 a, '', b。

优点：

封装性好，代码整洁，易于重用。
可以处理更复杂的拆分逻辑（例如，跳过空的元素，处理引用字符串等）。
存储过程可以返回多行结果集。
无需辅助表（如果逻辑完全在过程内实现）。

缺点：

需要有创建存储过程/函数的权限。
调试相对不如纯SQL查询方便。
需要在每个需要拆分的字段上调用函数/过程，如果需要在大型表上对多行进行拆分，可能需要结合其他方法（例如，使用存储过程拆分一个变量，然后将结果JOIN回主表）。直接在SELECT语句中对大表每一行的字符串调用存储过程可能会有性能问题。

适用场景：

需要在一个地方集中管理拆分逻辑，方便在多个地方调用。适合对少量字符串进行拆分，或者在ETL过程中作为转换步骤使用。对于在大表上对大量字符串进行拆分以进行聚合或JOIN的场景，辅助表或CTE方法通常更高效。

方法六：利用 JSON 函数（MySQL 5.7+ for基本函数, MySQL 8.0+ for JSON_TABLE）

MySQL 5.7引入了JSON数据类型和一系列JSON函数。如果我们能够将待拆分的字符串转换为一个合法的JSON数组字符串，那么就可以利用JSON函数来解析它。

例如，将 'apple,banana,cherry' 转换为 ['apple','banana','cherry']。

转换方法：

简单的方法是使用 REPLACE 将分隔符替换为 ","，然后在字符串前后加上 [" 和 "]。需要注意的是，这只适用于元素本身不包含分隔符或需要特殊转义的情况。

sql SELECT CONCAT('["', REPLACE('apple,banana,cherry', ',', '","'), '"]'); -- 结果: ["apple","banana","cherry"]

使用 JSON 函数拆分：

在MySQL 5.7+ 中，可以使用 JSON_EXTRACT（或其别名 -> 和 ->>）结合数字索引来提取JSON数组的元素。但这也只能提取指定位置的元素，无法返回多行，类似于 SUBSTRING_INDEX 的限制。

sql SET @json_str = CONCAT('["', REPLACE('apple,banana,cherry', ',', '","'), '"]'); SELECT JSON_EXTRACT(@json_str, '$[0]'); -- "apple" (包含双引号) SELECT JSON_UNQUOTE(JSON_EXTRACT(@json_str, '$[0]')); -- apple (移除双引号) SELECT JSON_UNQUOTE(@json_str -> '$[1]'); -- banana (使用->别名)

使用 JSON_TABLE (MySQL 8.0+) 拆分：

JSON_TABLE 函数是MySQL 8.0+ 中用于从JSON数据中提取数据的强大工具，它可以将JSON数组或对象解析成关系型表格的行和列。这非常适合用于将JSON数组拆分成多行。

SET @delimiter = ‘,’; — 定义分隔符

SELECT
st.id,
st.str AS original_string,
jt.element_index,
jt.element_value
FROM
StringTable st,
JSON_TABLE(
— 构造JSON数组字符串: 将分隔符替换为逗号并包装在方括号内
CONCAT(‘[“‘, REPLACE(st.str, @delimiter, ‘”,”‘), ‘”]’),
— 定义如何从JSON数组中提取数据
‘$[*]’ — 路径表达式: 表示遍历数组的所有元素
COLUMNS (
element_index FOR ORDINALITY, — 生成一个序列号作为索引
element_value VARCHAR(255) PATH ‘$’ — 提取当前元素的值
)
) AS jt
WHERE st.str IS NOT NULL AND st.str != ”; — 排除 NULL 和空字符串
ORDER BY st.id, jt.element_index;
“`

解释：

CONCAT('["', REPLACE(st.str, @delimiter, '","'), '"]'): 这部分将原始字符串 st.str 转换为一个JSON数组字符串。例如，'apple,banana,cherry' 变成 '"apple","banana","cherry"'，加上 CONCAT 后变成 ["apple","banana","cherry"]。
JSON_TABLE(json_doc, path COLUMNS (column_definition, ...)): 这是 JSON_TABLE 函数的语法。
- json_doc: 第一个参数是我们构造的JSON数组字符串。
- path: 第二个参数是JSON路径表达式，'$[*]' 表示从根路径开始，遍历数组的所有元素。* 是一个通配符，表示数组中的所有索引。
- COLUMNS (...): 定义如何从JSON数组的每个元素中提取数据并映射到关系型列。
  - element_index FOR ORDINALITY: FOR ORDINALITY 是 JSON_TABLE 的一个特殊子句，它会为遍历的每个元素生成一个从1开始的序号，非常适合作为元素的索引。
  - element_value VARCHAR(255) PATH '$': 定义一个名为 element_value 的 VARCHAR(255) 列。PATH '$' 表示提取当前遍历到的JSON元素（在 $[*] 路径下）的整个值。
FROM StringTable st, JSON_TABLE(...) AS jt: 将 StringTable 与 JSON_TABLE 的结果（视为一个表 jt）进行隐式交叉连接。由于 JSON_TABLE 是对 st.str 进行操作，这种连接实际上是为 StringTable 的每一行生成由 JSON_TABLE 产生的多行结果。
WHERE st.str IS NOT NULL AND st.str != '': 过滤无效输入。
ORDER BY st.id, jt.element_index: 按原始记录ID和拆分后的元素索引排序。

处理连续分隔符 (a,,b)：

CONCAT('["', REPLACE(st.str, @delimiter, '","'), '"]') 这个转换方法在遇到连续分隔符时，例如 'a,,b' 和分隔符 ,，会生成 'a","","b'，然后包装成 ["a","","b"]。JSON_TABLE 解析这个数组时会生成 'a', '', 'b' 三行，这符合将空元素也拆分出来的需求。

优点：

在MySQL 8.0+ 中语法相对简洁直观，特别是使用 JSON_TABLE。
无需辅助表或存储过程。
利用了JSON解析能力，理论上可以处理更复杂的嵌套结构（如果字符串本身是JSON格式的话）。
JSON_TABLE 直接返回多行，非常方便。

缺点：

需要 MySQL 5.7+ (基本JSON函数) 或 8.0+ (JSON_TABLE)。
需要将原始字符串转换为合法的JSON数组字符串，如果原始字符串本身包含引号、特殊字符或分隔符（不是简单的逗号），需要更复杂的转义或转换逻辑。
性能上，JSON解析可能不如纯字符串函数快，但通常对中等长度的字符串是可接受的。

适用场景：

MySQL 8.0+ 用户，字符串内容比较规范，可以方便地转换为JSON数组格式。希望使用现代SQL功能，并且直接在查询中获得多行结果。

方法七：应用程序层拆分

虽然本文重点是数据库内部拆分，但不能忽略一个重要的替代方案：在应用程序代码中进行字符串拆分。几乎所有编程语言都提供了强大的字符串拆分功能（如Java的split()，Python的split()）。

逻辑：

从数据库查询包含待拆分字符串的记录。
在应用程序代码中，对查询结果集的每一行的相应字段调用字符串拆分函数。
在应用程序内存中处理拆分后的元素（例如，存储到列表、数组中）。

优点：

实现简单直观，利用了编程语言成熟的字符串处理能力。
无需复杂的SQL语句或数据库对象（辅助表、存储过程）。
非常灵活，可以轻松处理各种边缘情况和复杂的解析需求。

缺点：

数据需要在应用程序和数据库之间传输两次： 一次查询原始字符串，一次处理拆分后的数据（如果需要写回数据库或进行进一步的基于单个元素的查询）。
无法直接在数据库层面进行基于元素的过滤、聚合或JOIN： 如果你需要查找包含特定标签的所有产品，或者统计某个标签出现的次数，或者将标签与另一个表关联，你需要在应用层获取所有数据并进行处理，这可能导致大量数据传输和处理开销，尤其对于大型数据集。在数据库内部完成这些操作通常更高效。

适用场景：

只需要将拆分结果用于展示或简单的逻辑处理，无需在数据库中进行复杂的基于元素的查询。
待处理的字符串数量不多，或者字符串长度有限，数据传输开销可以忽略。
数据库版本较低，不支持高效的内部拆分方法。

各种方法对比总结

方法	MySQL版本要求	是否需要额外对象	返回形式	易用性（SQL代码）	性能考虑	适合场景	处理连续分隔符
`SUBSTRING_INDEX`	4.0+	否	标量值	非常简单	高效	获取特定位置（首、尾、第n个）元素	通常视为分隔符
`SUBSTRING`/`LOCATE` (手动)	4.0+	否	标量值（单次）	复杂	取决于实现，通常低效作为纯查询	理解原理，存储过程/函数内部实现	需手动处理
辅助表 (Numbers Table)	4.0+	是 (表)	多行	中等复杂	良好，依赖表大小和索引	8.0之前主流多行拆分，纯SQL查询	自动处理
递归CTE	8.0+	否	多行	中等复杂	良好，依赖实现和深度	8.0+ 无需辅助表多行拆分，纯SQL查询	自动处理
存储函数/过程	5.0+	是 (函数/过程)	标量值/多行	易用（调用时）	取决于内部实现，调用开销	逻辑封装和复用，少量拆分或ETL	依赖实现
JSON 函数 (`JSON_TABLE`)	8.0+	否	多行	中等复杂	良好，依赖JSON解析效率	8.0+，字符串可转为JSON，纯SQL查询	自动处理
应用程序层拆分	所有	否	应用程序内存	N/A	数据传输开销大，CPU在应用层	简单处理，无需数据库内复杂操作，数据量小	依赖语言特性

选择合适的方法

选择哪种方法取决于您的具体需求和所使用的MySQL版本：

只需要第一个或最后一个元素？ -> 使用 SUBSTRING_INDEX。
需要获取固定位置的少数几个元素？ -> 使用嵌套的 SUBSTRING_INDEX 或编写一个简单的存储函数。
需要将字符串拆分成多行，并在MySQL 8.0+ 版本上？ -> 推荐使用 递归CTE 或 JSON_TABLE。它们是纯SQL方法，无需额外对象（除了临时表或CTE本身）。JSON_TABLE 可能在语法上更简洁，而递归CTE更符合标准SQL的集合处理思维。
需要将字符串拆分成多行，但在 MySQL 8.0 之前版本？ -> 辅助表（Numbers Table） 是最常用且高效的纯SQL方法。
需要封装拆分逻辑，方便复用，或者需要在存储过程内进行更复杂的流程控制？ -> 编写 存储过程。
只需要将数据取出后在应用层处理，无需在数据库内进行基于元素的复杂查询？ -> 在 应用程序层 拆分通常是最简单直观的选择。

关于性能：

对于少量或短字符串的拆分，各种方法的性能差异可能不明显。
对于大量字符串或很长的字符串，纯SQL方法（辅助表、递归CTE、JSON_TABLE）通常优于频繁调用存储函数或在应用层传输大量数据。
辅助表方法的性能很大程度上依赖于辅助表的大小、索引以及JOIN条件的效率。
递归CTE和JSON_TABLE的性能在MySQL 8.0+ 中经过了优化，通常表现良好，但极端情况下可能有栈深度或解析开销的限制。
存储过程中的循环方法效率取决于循环次数（元素数量）和内部操作。

在实际应用中，建议针对具体场景和数据量进行测试，以选择最优的拆分方案。

处理边缘情况

在实现字符串拆分时，需要考虑一些边缘情况：

NULL 或空字符串： 大多数方法可以通过 WHERE column IS NOT NULL AND column != '' 或在逻辑开始时检查来排除或特殊处理。
字符串不包含分隔符： 大多数方法（SUBSTRING_INDEX(..., 1)，递归CTE/JSON_TABLE的第一个元素，存储过程循环一次）会正确地将整个字符串视为一个元素。
字符串以分隔符开始或结束：
- ' ,apple,banana,'
- SUBSTRING_INDEX 会将开头的 , 视为一个空元素前的分隔符，返回空字符串。结尾的 , 不会影响 SUBSTRING_INDEX(..., positive_count) 的结果，但会影响 SUBSTRING_INDEX(..., -1) 返回空字符串。
- 辅助表、递归CTE、JSON_TABLE 和存储过程方法通常会按预期生成空元素（例如，对于 ',apple,banana,'，拆分结果可能是 '', 'apple', 'banana', ''）。如果不需要这些空元素，可以在拆分后过滤掉 element_value = '' 的行。
连续的分隔符：
- 'apple,,banana'
- 如前所述，大多数纯SQL方法（辅助表、递归CTE、JSON_TABLE）和本文中的存储过程会将其拆分成 apple, '', banana。这通常是期望的行为。如果不希望得到空字符串，需要在最终结果中添加 WHERE element_value != '' 进行过滤。
分隔符本身包含在元素中： 例如，分隔符是 ,，但某个元素是 "包含,逗号"。简单替换或 SUBSTRING_INDEX 方法无法正确处理这种情况。需要更复杂的解析逻辑，可能需要考虑引用符（如CSV格式），或者使用更高级的解析库（通常在应用程序层实现）。
分隔符是多字符： SUBSTRING_INDEX, LOCATE, REPLACE 函数都支持多字符分隔符，所以上述方法理论上都支持。只需要将分隔符参数替换即可。

总结

在MySQL中拆分字符串虽然不像其他数据库系统那样拥有一个简单的内置函数，但通过组合使用已有的功能，我们拥有多种灵活且强大的解决方案。从简单的 SUBSTRING_INDEX 到强大的辅助表、递归CTE和 JSON_TABLE，再到可复用的存储过程和灵活的应用层处理，您可以根据具体的MySQL版本、性能要求、代码维护需求和业务场景选择最合适的工具。

理解这些方法的原理和适用范围，掌握如何处理边缘情况，将帮助您有效地在MySQL中处理存储为分隔字符串的数据，更好地进行查询、分析和数据转换。希望这篇详细教程能为您在MySQL中的字符串拆分任务提供清晰的指导和帮助。

MySQL字符串拆分完全指南：方法、实战与解析

理解挑战：为什么MySQL没有直接的SPLIT函数？

方法一：使用 SUBSTRING_INDEX 获取指定位置的元素

方法二：结合 SUBSTRING 和 LOCATE 手动查找与截取（基础逻辑）

方法三：利用辅助表（Numbers Table）进行拆分

方法四：使用递归CTE（Common Table Expressions）（MySQL 8.0+）

方法五：使用存储函数或存储过程

方法六：利用 JSON 函数（MySQL 5.7+ for基本函数, MySQL 8.0+ for JSON_TABLE）

方法七：应用程序层拆分

各种方法对比总结

选择合适的方法

处理边缘情况

总结

发表评论 取消回复

方法一：使用 `SUBSTRING_INDEX` 获取指定位置的元素

方法二：结合 `SUBSTRING` 和 `LOCATE` 手动查找与截取（基础逻辑）

发表评论取消回复