MYSQ & Comma Seperated values


empno | loc_ids [varchar]

38084 | 12,10,20
38085 | 18,12

Now if issue the following query

FROM `table`
WHERE `loc_ids` =12

it returns 38084. but i feel this is wrong :rolleyes:
how come “12,10,20” = 12?

It isn’t. I’m sure that query doesn’t return that row if the data you posted here is correct.
Are you sure there isn’t a row in that table with loc_ids = ‘12’ ?

Hai guido,

pls see these screen shots :

Maybe it has to do with the column being varchar, and the value being numeric.
Try this:

FROM `table`
WHERE `loc_ids` = '12'

You might want to consider normalizing that table though.

absolutely. that is the reason. Wrapping up that 12 returns 0 records.

btw, normalizing mean?
but with FIND_IN_SET() i have no problem.


In this case, instead of storing multiple location ids separated by comma in a column, you’d have one empno-locid pair per row:

empno | loc_id

38084 | 12
38084 | 10
38084 | 20
38085 | 18
38085 | 12

Easier to maintain (what do you do if you need to remove loc_id 10 from empno 38084?), and easier to query.

Thank you for the valuable advise!

however, this will always require a table scan, so it gets slower and slower the more rows you have