将结果中用逗号分隔的值从一列拆分为两行.的MySQL [英] Split comma separated values from one column to 2 rows in the results. MySQL

问题描述

MySQL.两列，同一张表.
第1列具有product_id
第2列具有category_ids(有时为2个类别，因此看起来像23,43)

如果与product_id关联的类别ID超过1个，如何编写查询以返回带有单独行的product_id，category_ids列表.

MySQL. Two columns, same table.
Column 1 has product_id
Column 2 has category_ids (sometimes 2 categories, so will look like 23,43)

How do i write a query to return a list of product_id, category_ids, with a seperate row if there is more than 1 category_id associated with a product_id.

即
表格:

product_id | category_ids 
100        | 200,300
101        | 201

查询结果:未尝试修改表

100 | 200
100 | 300
101 | 201

(注)我实际上根本不希望操纵该表.只是在PHP中进行查询，因此我可以根据需要使用数据.

(note) I don't actually wish to manipulate the table at all. Just doing a query in PHP, so i can use the data as needed.

推荐答案

您的数据库表实现似乎设计不好，但是在您的情况下，您需要的是GROUP_CONCAT的反向函数，但是不幸的是，它在MySQL中不存在.

Your database table implementation seems bad designed, however in your case what you need would be a reverse function of GROUP_CONCAT, but unfortunately it doesn't exist in MySQL.

您有两个可行的解决方案:

You have two viable solutions :

1. 更改存储数据的方式(允许在product_id字段上重复，并为不同的category_id放置具有相同product_id的多条记录)
2. 从应用程序内部处理查询结果(您在问题中提到了PHP)，在这种情况下，您必须拆分category_ids列值并由您自己的
组合结果集

1. Change the way you store the data (allow duplicate on the product_id field and put multiple records with the same product_id for different category_id)
2. Manipulate the query result from within your application (you mentioned PHP in your question), in this case you have to split the category_ids column values and assemble a result set by your own

还有我发现的第三个解决方案，就像一个把戏(使用临时表和存储过程)，首先，您必须声明此存储过程:

There is also a third solution that i have found that is like a trick (using a temporary table and a stored procedure), first of all you have to declare this stored procedure :

DELIMITER $$
CREATE PROCEDURE csv_Explode( sSepar VARCHAR(255), saVal TEXT )
body:
BEGIN

  DROP TEMPORARY TABLE IF EXISTS csv_Explode;
  CREATE TEMPORARY TABLE lib_Explode(
    `pos` int unsigned NOT NULL auto_increment,
    `val` VARCHAR(255) NOT NULL,
    PRIMARY KEY  (`pos`)
  ) ENGINE=Memory COMMENT='Explode() results.';

  IF sSepar IS NULL OR saVal IS NULL THEN LEAVE body; END IF;

  SET @saTail = saVal;
  SET @iSeparLen = LENGTH( sSepar );

  create_layers:
  WHILE @saTail != '' DO

    # Get the next value
    SET @sHead = SUBSTRING_INDEX(@saTail, sSepar, 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 1 + @iSeparLen );
    INSERT INTO lib_Explode SET val = @sHead;

  END WHILE;

END; $$
DELIMITER ;

然后，您必须调用传递要爆炸的列中的数组的过程:

Then you have to call the procedure passing the array in the column you want to explode :

CALL csv_explode(',', (SELECT category_ids FROM products WHERE product_id = 100));

此后，您可以通过以下方式在临时表中显示结果:

After this you can show results in the temporary table in this way :

SELECT * FROM csv_explode;

结果集将为:

+-----+-----+
| pos | val |
+-----+-----+
|   1 | 200 |
|   2 | 300 |
+-----+-----+

这可能是您的起点...

It could be a starting point for you ...

这篇关于将结果中用逗号分隔的值从一列拆分为两行.的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！