在MySQL中实现多行字符串分割并整体去重

引言

在数据库处理中，我们偶尔会遇到需要处理和分析存储在单一列中的多值字符串的情况。但是MySQL中并没有类似分割字符的函数，导致我们需要通过其他的手段来达成目标。

问题描述

以下面数据为例：

期望查询的结果是：

要达成目标，核心的问题是如何将每行的 user_name 值拆分为多行的值，再整体去重。

解决方案一：使用数字序列表

首先，我们可以创建一个t_number表，这是一个包含一系列连续数字的辅助表，用于基于分隔符的位置来拆分字符串。通过创建如下SQL表和插入语句来建立该表：

CREATE TABLE t_number (number INT PRIMARY KEY);
INSERT INTO t_number (number) VALUES (1), (2), (3), (4), (5), ...;

接下来，我们通过关联 t_number 表使得行数按照分隔符数量增加，再通过 t_number 表中数字，结合 SUBSTRING_INDEX 函数来定位和提取每个分隔值，并通过 DISTINCT 关键字实现去重。查询如下：

SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(u.user_name, ',', n.number), ',', -1)) AS 'name'
FROM t_user u
JOIN t_number n ON CHAR_LENGTH(u.user_name) - CHAR_LENGTH(REPLACE(u.user_name, ',', '')) >= n.number - 1;

解决方案二：使用临时数字序列

如果不想创建一个持久的表，可以使用SQL来动态生成一个临时的数字序列。查询如下：

SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(u.user_name, ',', n.number), ',', -1)) AS 'name'
FROM
    (SELECT a.i number
     FROM (SELECT 1 i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) a
     ORDER BY number) n
JOIN t_user u ON CHAR_LENGTH(u.user_name) - CHAR_LENGTH(REPLACE(u.user_name, ',', '')) >= n.number - 1;

如果担心临时数字序列长度不够，可以通过多个 JOIN 操作来创建，如下：

SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(u.user_name, ',', n.number), ',', -1)) AS 'name'
FROM
    (SELECT a.i * 10 + b.i + 1 number
     FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
     JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
     ORDER BY number) n
JOIN t_user u ON CHAR_LENGTH(u.user_name) - CHAR_LENGTH(REPLACE(u.user_name, ',', '')) >= n.number - 1;

需要注意两个子数字序列是以 0 开头。