Search code examples
mysqlsqldenormalized

Mysql WHERE problem with comma-separated list

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)...

Solution

  • 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)