Comma separated columns in MySQL

Question

Let's say I have these MySQL tables:

USERS:

user-ID	Name
1	Saoirse
2	Gal
3	Margaret

PAYMENTS

pay-ID	user-IDs	Names
1		Margaret
2		Saoirse, Gal, Margaret
3		Gal, Saoirse

What I need is to update through MySQL the column user-IDs of the payments table with a comma separated user-ID of each user, searching by the name. This should be the result:

pay-ID	user-IDs	Names
1	3	Margaret
2	1, 2, 3	Saoirse, Gal, Margaret
3	2, 1	Gal, Saoirse

Does anyone have a clue?

Thanks a million!

Does this answer your question? [How to use GROUP BY to concatenate strings in MySQL?](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) — Stu, Mar 30 '22 at 07:54
Is it too late to redesign the database properly with normalisation applied? — RiggsFolly, Mar 30 '22 at 07:57
[Is storing a delimited list in a database column really that bad? "**TL:DR- YES**"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — RiggsFolly, Mar 30 '22 at 08:01

score 1 · Answer 1 · answered Mar 30 '22 at 07:55

You should seriously move away from storing CSV data like this. It goes against how the database wants you to persist your data. Here is the PAYMENTS table, refactored to be normalized:

payID | userID | Name
1     |        | Margaret
2     |        | Saoirse
2     |        | Gal
2     |        | Margaret
3     |        | Gal
3     |        | Saoirse

Now if you need to update this table to bring in the user IDs, you may do a simple update join:

UPDATE PAYMENT p
INNER JOIN USERS u ON u.Name = p.Name
SET userID = u.userID;

Note that more typically you would always have the primary and foreign key IDs persisted already in the table, and rather you would be bringing in the names.

I know this is not the way to work with databases, but it's an old table that I need to fix like this. Thanks anyway. — cesaret_2000, Mar 30 '22 at 08:02

score 0 · Answer 2 · answered Mar 30 '22 at 09:10

You can use FIND_IN_SET to accomplish the task, even though I agree that you should normalize your data.

Try:

update payments p 
inner join (
             select pay_id,group_concat(u.user_id separator ',') as user_id
             from payments p1
             inner join users u on FIND_IN_SET(u.name,p1.names) 
             group by pay_id
         ) as p2 on p.pay_id=p2.pay_id
set p.user_id=p2.user_id ;

Demo

If Names columns have spaces you can change the join condition to FIND_IN_SET(u.name,replace(p1.names,' ','')) or better run an update first replacing the spaces , because find_in_set has low performance.

Check the Demo

Thanks so much. That's exactly what I needed. – cesaret_2000 Mar 30 '22 at 09:54 — cesaret_2000, Mar 30 '22 at 09:54

Comma separated columns in MySQL

2 Answers2