MYSQL: FIND_IN_SET AND IN()
Using IN() for comma delimited and my query is something like SELECT * FROM TABLE WHERE FLD IN(’33, 148′)
TABLE
(ID) (FLD) COMMA DELIMITED FIELD
1 42, 148
2 234, 25423,148
It wont be able to retrieve any records at all coz it will only check for the exact values inside that quote ‘33, 148′. Even I put something like SELECT * FROM TABLE WHERE FLD IN(’148′) still won’t have any result coz I don’t have a record that’s only ‘148′
So instead I used something like SELECT * FROM TABLE WHERE FIND_IN_SET( ‘148′, FLD) and these 2 rows will be retrieved. So I guess IN() is not a good way to retrieve fields with comma delimited values unless it’s the exact string. But if ur sql is like this SELECT * FROM TABLE WHERE ID IN(1,2), it won’t be a prob at all.
Btw. I tried the issue below and it’s not a prob anymore. I only used the sql like SELECT * FROM TABLE WHERE FIND_IN_SET( ‘1487′, FLD) and it only retrieved 1487 .
SOURCE: http://bugs.mysql.com/bug.php?id=2047
I use FIND_IN_SET() to return results containing a certain number in a comma-delimmeted
list in a TEXT field. Sometimes it returns false positives,
for example:mysql> SELECT pssmID_list FROM bccs WHERE FIND_IN_SET(1487, pssmID_list);Tags: bug, find_in_set, in(), mysql
