The use of the Find_in_set () function in MySQL

The syntax explanation for the Find_in_set function in the MySQL manual:
Find_in_set (Str,strlist)

STR string to query for
Strlist field name parameters are separated by "," as (1,2,6,8,10,22)
Query field (strlist) contains the result of (str), the result is null or the record

If the string str is in a string list of n Strlist, the return value ranges from 1 to N. A string list is a string of sub-chains that are separated by ', ' symbols. If the first argument is a constant string and the second is a type set column, the Find_in_set () function is optimized to use bit calculations. If STR is not strlist or strlist is an empty string, the return value is 0. If either parameter is NULL, the return value is null. This function will not work correctly when the first parameter contains a comma (', ').

Don't understand the concept is also OK, according to the following class sub:

Example 1:
SELECT Find_in_set (' B ', ' a,b,c,d ');
Results: 2

Because B is placed at 2 in the Strlist set, starting from 1

Select Find_in_set (' 1 ', ' 1 '); The return is 1. The Strlist collection is a bit special. Only one string actually requires the previous string to return a number greater than 0 in the next string collection.
Select Find_in_set (' 2 ', ' up '); Returns 2
Select Find_in_set (' 6 ', ' 1 '); Returns 0 Strlist does not exist in STR, so returns 0.

The difference between Find_in_set () and in:

Get a test sheet to illustrate the difference.

CREATETABLE' Tb_test ' (' ID ')int8)NotNullAuto_increment, ' name 'varchar255)NotNull, ' list 'varchar255)NotNull,PRIMARYKEY(' id '));
INSERTInto ' tb_test 'VALUES (1,‘Name‘,‘Daodao,xiaohu,xiaoqin‘insert into ' tb_test '  VALUES (2, name2 ", " xiaohu,daodao,xiaoqin ' insert into ' tb_test ' values (3, " name3  ", " xiaoqin,daodao, Xiaohu           

Originally thought that MySQL can make such a query:

'Daodao--   

In fact, this is not possible, so that only if the value of the list field equals ' Daodao ' (and the string in front of it exactly), the query is valid, otherwise it will not get results, even if ' Daodao ' is really in the list.

Take a look at this again:

'Daodao in('libk'zyfon'daodao-- (ii)     

This is possible.

What is the difference between these two? Why the first one can't get the right result, and the second one can get the result. The reason is (a) the (list) list is a variable, and (b) (' LIBK ', ' Zyfon ', ' Daodao ') is a constant.
So if you want to get (a) to work correctly, you need to use find_in_set ():

WHERE find_in_set ('Daodao-- (i) 's improved version   

Summarize:
So if the list is a constant, you can use in directly or use the Find_in_set () function.

That is, the two SQL is the same as the effect of a query:

1.SELECT * from C_purchasingmasterdata where Find_in_set (ekgrp, ' c54,c02,c14,c60,c06,c61,c53,c51,c12,c08,c03,c07 ')

2.SELECT * from C_purchasingmasterdata where ekgrp in (' C54 ', ' C02 ', ' C14 ', ' C60 ', ' C06 ', ' C61 ', ' C53 ', ' C51 ', ' C12 ', ' C08 ', ' C03 ', ' C07 ')

But if the second sentence in SQL is a variable field of incoming SQL, then the second sentence of SQL is not good enough. Use the In or Find_in_set () function as a practical decision.

The difference between find_in_set () and like:

The main difference is that like is a wide range of fuzzy queries, while Find_in_set () is an exact match, and the field values are separated by ', '.

Now you want to query a user who has role number 2, use the LIKE keyword to query:

1.SELECT userid,username,userrole role from ' user ' WHERE userrole like '%2% ';

Results:

Query with find_in_set () :

2.SELECT userid,username,userrole role from ' User ' WHERE find_in_set (' 2 ', userrole)

Results:

Obviously the results from the find_in_set () query are the results we want. So the two of them

The main difference is that like is a broad fuzzy query, whereas Find_in_set () is an exact match, and the field values are separated by ', ', the result of the Find_in_set query is less than the result of the as query.

The use of the Find_in_set () function in MySQL