Mysql get comma separated list of columns

Question

I have a column with accessories of products as below example:

TABLE1

ProductID    accessories
1            2,3  
2            1,4,5
3
4            1
5            2

It means that for the PRODUCT 2, it has the accessories product ids 1,4 and 5

and i have THE FINAL table 2 below which look like this

TABLE2

GRP     ProductID     accessories 
a       2             
b       3             
c       1             
d       4             
e       5

so actually if using UPDATE it would be like this

TABLE2

UPDATE table t2
INNER JOIN table1 t1
On t2.ProductID = t1.ProductID
set t2.accessories = t1.accessories 

GRP     ProductID     accessories
a       2             1,4,5
b       3              
c       1             2,3
d       4             1
e       5             2

but i want to change the productIDs in the t2.accessories with the GRP character instead according to the t2.ProductID so that the FINAL table looks like this .

TABLE2

GRP     ProductID     accessories
a       2             c,d,e
b       3              
c       1             a,b
d       4             c
e       5             a

iam really confused how to do that correctly i was able to handle this using php code, but it takes loong time becouse i have 100k products therefore i prefer to use some group_concat or concat_ws to handle this

and some t2.accessories may already be stored, therefore it should be distinct values, so there is no duplicate t2.accessories on table2

Here is the tables http://sqlfiddle.com/#!9/bfddf

This is going to be very hard to do, because you're going to have to parse the accessories string to get each integer value. If you can change your database schema at all, you should try not to store comma separated lists. — AdamMc331, Jun 18 '15 at 18:21
actually i already have 2m rows with comma seperated accessories. so it could be great if it was possible to handle — inventor, Jun 18 '15 at 18:22
Hopefully someone will know how, but in the future, try not to do that. [Here](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) is a really good question on the topic. — AdamMc331, Jun 18 '15 at 18:25
Change the structure of your DB. You should be using a many-to-many relationship. — claydwg, Jun 18 '15 at 18:28
So You recommend to split the values of the comma seperated string into another table ? — inventor, Jun 18 '15 at 18:36
Yes, we recommend splitting the values of the comma-separated strings into another table. See this question that @McAdam331 posted for more about why: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Andy Lester, Jun 18 '15 at 19:28
Yes, read this http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561 — claydwg, Jun 18 '15 at 20:00

score 1 · Answer 1 · answered Jun 18 '15 at 18:54

1

You should fix your data structure to have a junction table. Don't store lists as strings. SQL has a perfectly good way to store lists; it is called a table. Don't store numbers as strings. SQL has very good data types for numbers. They do not contain "char" in their name.

There is a way to do what you want in the database, but you can't complain about performance. Structure your data correctly and it will go faster.

The select query that does the concatenation looks like this:

select t1.productid,
       group_concat(t2.productid order by find_in_set(t2.ProductId, t1.accessories))
from table1 t1 join
     table2 t2
     on find_in_set(t2.ProductId, t1.accessories) > 0
group by t1.productid;

But, you should really fix your data structure.

answered Jun 18 '15 at 18:54

Gordon Linoff

1,242,037
58
646
786

Thanks for this, it is almost correct, this worked for me
select t1.productid, group_concat(t2.GRP order by find_in_set(t2.GRP, t1.accessories)) from table1 t1 join table2 t2 on find_in_set(t2.ProductId, t1.accessories) > 0 group by t1.productid just need to change it as update something like UPDATE table2 t2 set t2.accessories = (select.. – inventor Jun 18 '15 at 23:35
the table structure i provided is just dummy data. the tables i use are correctly structured – inventor Jun 19 '15 at 00:00
How can your data be correctly structured if you have comma-delimited lists of identifiers stored as strings? If you have a different data structure, you should ask a new question. – Gordon Linoff Jun 19 '15 at 00:55
I thougt you meant another stucture. So as i understand. In this case. the comma-delimited accessory column should be integer ? And If the accessory column contains characters then it is Best to store Them on seperate table one by one? – inventor Jun 19 '15 at 01:49
@inventor . . . It is better to store them in a separate table -- called a junction table -- with one row per `ProductId` and `accessory`. – Gordon Linoff Jun 19 '15 at 01:56
Lindoff i have created new question. please take a look http://stackoverflow.com/q/30944345/4188937 – inventor Jun 19 '15 at 17:52

score 0 · Answer 2 · answered Jun 19 '15 at 16:47

This worked as i wanted.

UPDATE table2 
INNER JOIN ( 
            select t1.productid, group_concat(t2.GRP order by find_in_set(t2.GRP, t1.accessories)) as test from table1 t1 
            join table2 t2 on find_in_set(t2.ProductId, t1.accessories) > 0 group by t1.productid
            ) as temp on table2.ProductId=temp.productid
set table2.accessories=temp.test

The only problem is, if you have large amount of rows, it will take loong time

Mysql get comma separated list of columns

2 Answers2