The difference between find_in_set and and find

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 request:

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

Output

 0 

Below is my LOCATE request:

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

Output

 1 

My question

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

Answer 1

Difference between LOCATE() and FIND_IN_SET() Function

When using the LOCATE() function for integers, suppose we need 1 to return from LOCATE() , if the integer 3 is in the set 1,2,3,4,5,.. , the following MySQL commands can be written:

 mysql> SELECT IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0); +-----------------------------------------+ | IF(LOCATE(3,'1,2,3,4,5,6,7,8,9')>0,1,0) | +-----------------------------------------+ | 1 | +-----------------------------------------+ 1 row in set (0.06 sec) 

The above command works correctly because the set contains the number 3, but if we write the following commands, look what happened

 mysql> SELECT IF(LOCATE(3,'11,12,13,14,15')>0,1,0); +--------------------------------------+ | IF(LOCATE(3,'11,12,13,14,15')>0,1,0) | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.02 sec) 

Above 3 is not present as the number three (3) in the given set, although LOCATE() returns 1. To avoid this situation, you can use the FIND_IN_SET() function. The following is an example:

 mysql> SELECT IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0); +-------------------------------------------------+ | IF(FIND_IN_SET(3,'11,12,13,4,5,6,7,8,9')>0,1,0) | +-------------------------------------------------+ | 0 | +-------------------------------------------------+ 1 row in set (0.05 sec) 

So, the LOCATE () function is very suitable for a string, but not suitable for an integer.

You can find examples, loans and additional information here.

So, in your example FIND_IN_SET return 0 , because the specified set does not have 3 , but LOCATE() returns 1 , it treats the given set as a string, but not a comma-separated value, and 3 present in number 13

Answer 2

To express this in simple technical terms (PHP terminology), find_in_set is as a function of the PHP substring. It will take the substring and string as parameters and return 1 if the substring is inside the string. It will return 0 if the substring is not found.

Conversely, LOCATE () returns the position of the first occurrence of a string within a string. It takes a substring and a string as parameters.

I think in your use case find_in_set is the one you should go for. Because it is one. find_in_set will return 1 if 3 is found in the line, where, since the location will be the first meeting 3 in the line, even if it finds 31 or 300 as the first element.