Mysql WHERE problem with comma-separated list

Question

I need help for this problem.

In MYSQL Table i have a field :

Field  : artist_list  
Values : 1,5,3,401

I need to find all records for artist uid 401

I do this

SELECT uid FROM tbl WHERE artist_list IN ('401');

I have all record where artist_list fields values are '401' only, but if i have 11,401 this query do not match.

Any idea ?

(I cant user LIKE method because if artist uid is 3 (match for 30, 33, 3333)...

Norma

You're storing your data in a really bad way, hence you're running into problems. – Albin

Best course of action would be to read up on normalization. I can imagine very costly REGEXP answers cropping up here which would be disastrous to performance. FIND_IN_SET is a little better, but no substitute for proper normalization. – Traitorous

Read my answer to Is storing a comma separated list in a database column really that bad?. – Gargoyle

Answer 1

Short Term Solution

Use the FIND_IN_SET function:

SELECT uid 
  FROM tbl 
 WHERE FIND_IN_SET('401', artist_list) > 0

Long Term Solution

Normalize your data - this appears to be a many-to-many relationship already involving two tables. The comma separated list needs to be turned into a table of it's own:

ARTIST_LIST

• artist_id (primary key, foreign key to ARTIST)
• uid (primary key, foreign key to TBL)

Hem

Definitely the best choice. +1 – Lymphoma

Answer 2

SELECT uid
FROM tbl
WHERE CONCAT(',', artist_list, ',') LIKE '%,401,%'

Although it would make more sense to normalise your data properly in the first place. Then your query would become trivial and have much better performance.

Bessel

@soulfaya: I'm adding commas to either end of artist_list in the query itself to handle that case. – Bessel

Fine answer, but + is a string concatenation operator in Microsoft SQL Server, not MySQL. In MySQL, use the CONCAT() function, or else SET SQL_MODE='PIPES_AS_CONCAT' and use ||. – Gargoyle

Answer 3

Your database organization is a problem; you need to normalize it. Rather than having one row with a comma-separated list of values, you should do one value per row:

uid    artist
1      401
1       11
1        5
2        5
2        4
2        2

Then you can query:

SELECT uid
  FROM table
 WHERE artist = 401

You should also look into database normalization because what you have is just going to cause more and more problems in the future.

Lymphoma

Yes sure... but it's not my question :) – Norma