将逗号分隔的单独列转换为唯一列

Question

我有一张桌子

 ID      name
 1       john, peter, ken

如何使用MYSQL将名称列(值以逗号分隔)分隔为单独的列？

ID       name
1        john
1        peter
1        ken 

Answer 1

您可以使用如下查询：

SELECT
  id,
  n.digit,
  name,
  SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', n.digit+1), ',', -1)
FROM
  mytable
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON LENGTH(REPLACE(name, ',' , '')) <= LENGTH(name)-n.digit

(请注意，这最多只能分隔4个名称)。

Fiddle是here。

说明

我正在使用一个查询，它返回从0到3的所有数字(例如，您可以使用包含10或100个数字的索引表)：

SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

然后我用mytable加入这个数字，使用这个加入条件：

ON LENGTH(REPLACE(name, ',' , '')) <= LENGTH(name)-n.digit

这是一种检查name列是否至少包含n.digit名称的“脏”方法(我将删除所有逗号，并检查完整字符串的长度差异)。

使用SUBSTRING_INDEX可以获取n.digit+1逗号左侧的字符串部分：

SUBSTRING_INDEX(name, ',', n.digit+1)

再使用一个带有负参数的SUBSTRING_INDEX，您只能将字符串的一部分保留到第一个逗号：

SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', n.digit+1), ',', -1)

Answer 2

如果你需要把数据放到mysql中，那么你可以使用explode创建一个php文件，为每个id创建一个数组。

这些可能会有所帮助：

How can I split a comma delimited string into an array in PHP?

http://php.net/manual/en/function.explode.php