difference between find_in_set and and locate

Question

I have product tables in my database

Product table structure:

product_id     |     testid
------------------------------------
   1                     11,12,13
   2                     2,4

Below is my FIND_IN_SET query:

SELECT product_id FROM product
WHERE FIND_IN_SET(3, testid) > 0;

Output

0

Below is my LOCATE query:

 SELECT product_id FROM product
    WHERE LOCATE(3, testid) > 0;

output

1

My question

What is difference between FIND_IN_SET and LOCATE and what is the best way to find id in column

Answer 1

To put it in simple technical terms(PHP terminology), find_in_set is like substring function of PHP. It will accept a substring and a string as parameters, and return 1 if the substring is found within the string. It will return 0 if substring is not found.

On the contrary, LOCATE() returns the position of the first occurrence of a string within a string. It accepts, a substring and a string as parameters.

I think in your use case, find_in_set is the one you should go for. Because this is the one. find_in_set will return 1 if 3 is found in a row, where as locate will first occurance of 3 in the string even if it finds 31 or 300 as first element.