SQL using WHERE with comma values

Question

I have product_id column which has data 30ed1476c6d04337b02f783079c2290f, a3b14b2d2603499393cde6bf120a059e in TEXT datatype. I combied the 2 product id and save it to db using implode. Now my problem is how to get the data using 1 product id.

For example SELECT * FROM table1 WHERE product_id = 30ed1476c6d04337b02f783079c2290f

Comma lists in mysql fields aren't something to search on. Look up normalization before you got too far with this structure. — danblack, Jun 17 '20 at 13:57
this looks like bad database design, read about [database normalization](https://en.wikipedia.org/wiki/Database_normalization) — Vickel, Jun 17 '20 at 13:57
SELECT * FROM table1 WHERE product_id like '%30ed1476c6d04337b02f783079c2290f%' — Vijay, Jun 17 '20 at 14:02

score 2 · Accepted Answer · answered Jun 17 '20 at 13:59

You first effort should go into fixing your data model; you should have a separate mapping table, where each value would be stored in a separate row. Storing csv data in a relational table is the root of many evils. See: Is storing a delimited list in a database column really that bad?

That said, for your current schema, you can use string function find_in_set():

select *
from table1
where find_in_set('30ed1476c6d04337b02f783079c2290f', product_id)

SQL using WHERE with comma values

1 Answers1