mysql 按符号分隔|极客笔记

mysql 按符号分隔

在数据库中，有时候我们需要对文本字段进行按符号分隔的操作，比如将一串逗号分隔的字符串拆分成多个值，然后进行相应的处理。在 MySQL 中，我们可以使用内置的函数来实现这个功能，本文将详细介绍如何在 MySQL 中按符号分隔字符串。

准备工作

在进行分隔字符串之前，我们先创建一个示例表，用于演示：

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
);

INSERT INTO test_table (id, data) VALUES
(1, 'apple,banana,orange'),
(2, '1,2,3,4,5'),
(3, 'red|green|blue');

上面的 SQL 语句创建了一个名为 test_table 的表，包含两个字段 id 和 data。我们在表中插入了三条数据，分别是逗号分隔、竖线分隔以及数字之间逗号分隔的字符串。

使用 `SUBSTRING_INDEX` 函数分隔字符串

MySQL 提供了 SUBSTRING_INDEX 函数来实现按符号分隔字符串的功能。该函数的语法如下：

SUBSTRING_INDEX(str, delim, count)

str：要分隔的字符串。
delim：分隔符号。
count：指定分隔的次数，正数表示从左往右分隔，负数表示从右往左分隔。

示例

我们使用 SUBSTRING_INDEX 函数来分隔 data 字段中的字符串，并查询结果：

SELECT id, 
       SUBSTRING_INDEX(data, ',', 1) AS part1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 2), ',', -1) AS part2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 3), ',', -1) AS part3
FROM test_table
WHERE id = 1;

运行结果如下：

id	part1	part2	part3
1	apple	banana	orange

上面的查询将逗号分隔的字符串 data 拆分成了三部分，分别存储在 part1、part2 和 part3 字段中。

使用 `REGEXP` 函数分隔字符串

除了 SUBSTRING_INDEX 函数外，我们还可以使用正则表达式函数 REGEXP 来进行字符串分隔。REGEXP 函数可以根据正则表达式模式来匹配字符串，并返回结果。

示例

我们创建一个查询，使用 REGEXP 函数按竖线 | 分隔字符串：

SELECT id, 
       SUBSTRING_INDEX(data, '|', 1) AS part1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(data, '|', 2), '|', -1) AS part2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(data, '|', 3), '|', -1) AS part3
FROM test_table
WHERE id = 3;

运行结果如下：

id	part1	part2	part3
3	red	green	blue

处理多个不定数量的分隔符

如果字符串中包含多个不定数量的分隔符，我们可以使用 REGEXP 函数结合 GROUP_CONCAT 和 UNION 来处理。

示例

我们查询出 data 字段中的所有数字，并使用 GROUP_CONCAT 函数将结果合并为一个字符串：

SELECT id, 
       GROUP_CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', n), ',', -1)) AS numbers
FROM test_table
CROSS JOIN (
    SELECT 1 AS n UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
) numbers
WHERE id = 2;

运行结果如下：