MySQL - 分割字符串

Question

我的问题与这篇文章类似：在MySQL中“Reverse GROUP_CONCAT”？ 然而，与其反转group_concat，是否有一种方法将字符串拆分成自己的列，如下所示：

  id | colors1      | color 2      | color 3 | color 4
+----+-----------------------------+---------+----------
| 1  | Red          | Green        | Blue    | Black
| 2  | Orangered    | Periwinkle   | Black   |
| 3  | Orange       | Black        |         |

我也查看了这篇帖子：如何在MySQL中拆分名称字符串？。但我无法找出如何获得所需的输出结果。

- PMa

---

你的连接字段中有4种颜色，或者可能更多或更少吗？ - Mobasher Fasihy

每个ID最多可以有4种或更少的颜色，每个ID具有的最大颜色数量为4。 - PMa

Answer 1

这可能是你要查找的查询：

第一个表结构：

CREATE TABLE color (
  id int AUTO_INCREMENT,
  col_type varchar(255),
  PRIMARY KEY (id)
);
INSERT INTO color (col_type)
  VALUES(
         'GREEN,RED,BLACK'
);

SELECT
      SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, ',', 1), ',', -1) AS first_color,
        If(  length(col_type) - length(replace(col_type, ',', ''))>1,  
             SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, ',', 2), ',', -1) ,NULL) 
               as second_color,
             SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, ',', 3), ',', -1) AS last_color
FROM color

结果如下： first_color | second_color | third_color 绿色 | 红色 | 黑色 fiddle 进入链接描述

但是如果有超过3种颜色并且每种颜色都按照自己的顺序排列，我认为以下查询语句是正确的。

SELECT
   COLOR,
   SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 1), ',', -1) AS first_color,
   If(  length(COLOR) - length(replace(COLOR, ',', ''))>=1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 2), ',', -1) ,NULL) 
       as second_color,
   If(  length(COLOR) - length(replace(COLOR, ',', ''))>=2,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 3), ',', -1) ,NULL) 
       AS third_color,
   If(  length(COLOR) - length(replace(COLOR, ',', ''))>=3,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(COLOR, ',', 4), ',', -1) ,NULL) 
       AS fourth_color
FROM COLOR;

要知道在COLOR字段中发生的最大串联次数，可以执行以下操作：

select (length(COLOR) - length(replace(COLOR, ',', '')) as NumColors

然后使用循环来根据表中颜色数量的最大值制作查询中的if部分。 示例参见这里

- Mobasher Fasihy

---

当我在我的数据集上测试时，发生了这种情况：对于只有1种颜色的数据-->第一列和第三列填充相同的值，第二列为空；对于有2种颜色的数据-->第一列填充第一种颜色，第二列为空，第三列填充第二种颜色；对于有3种颜色的数据，运行正确。我很快会添加一个fiddle链接来复制。 - PMa

我不知道如何选择第四个颜色。 - PMa

@PerriMa 我修改了查询语句，以便按照您的要求获取结果。 - Mobasher Fasihy