FIND_IN_SET() vs IN()

PHP
1

Hi,

Just wondering what the difference was in the two mysql functions?

I’m noticing that this works:
FIND_IN_SET(‘1’, somefield);
but this does not:
FIND_IN_SET(‘3,5,9,2,1’, somefield);

I noticing that this works:
IN(3,5,9,2,1)
but this does not:
IN(3)

Could someone help me with the above two? I would really like to use one or the other. Could it be the way I’m passing the values to these functions?

Thanks in advance

2

I believe that you have more control over the FIND_IN_SET when you are doing your queries, but that IN is faster. But then again I am a beginner when it comes to databases. Also I think I read something about an indexing problem with FIND_IN_SET. But I will leave that determination up the database experts.

As for your problem with IN and a single variable, it has to be a problem with your coding. As I am constantly using IN with single variables.

3

Thanks for the tip.

IN() works for what I need it to do now. There was a small issue with the variable I had overlooked. Also, it appears that IN() does not require any single quotes like (‘1,2,3’) it works fine like (1,2,3).

Regards,
Peter

4

“x IN (a, b, c)” is the same as “FIND_IN_SET(x, ‘a,b,c’)”. IN takes any number of arguments, comma separated. FIND_IN_SET takes only two.

5

Here’s the difference.

Use IN() when you have a list of possible valuables and a single value in your database.
Example: WHERE status_id IN (1,2,3)

Use FIND_IN_SET where you’re storing a comma separated list of values, possibly from a checkboxlist, and want to see if a certain value exists in that list.
Example: WHERE FIND_IN_SET( ‘dog’, animals_i_own )