5

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

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
soulfaya
  • 65
  • 4
  • 1
    You're storing your data in a really bad way, hence you're running into problems. – chigley Oct 15 '10 at 23:23
  • 4
    Best course of action would be to [read up on normalization](http://en.wikipedia.org/wiki/Database_normalisation). 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. – Wrikken Oct 15 '10 at 23:24
  • 1
    Read my answer to [Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad/3653574#3653574). – Bill Karwin Sep 25 '11 at 00:16

3 Answers3

11

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)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
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.

LukeH
  • 263,068
  • 57
  • 365
  • 409
  • @soulfaya: I'm adding commas to either end of `artist_list` in the query itself to handle that case. – LukeH Oct 15 '10 at 23:51
  • 2
    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 `||`. – Bill Karwin Sep 25 '11 at 00:17
2

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.

JoshD
  • 12,490
  • 3
  • 42
  • 53