1. Function Introduction
FIND_IN_SET (Str,Strlist)
Assume that the stringStrInNString list composed of substringsStrlist, The returned value range is 1N. A string list is a string consisting of several tokens separated by commas. If the first parameter is a constant string and the second parameter is the type SET column, the FIND_IN_SET () function is optimized and computed in bits. IfStrNoStrlistOrStrlistIf it is a null string, the return value is 0. If any parameter is NULL, the return value is NULL. This function cannot run normally when the first parameter contains a comma.
Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D ');
-> 2
2. Differences between FIND_IN_SET and IN
Use the code available on the Internet:
Create table 'test '(
'Id' int (8) not null auto_increment,
'Name' varchar (255) not null,
'LIST' varchar (255) not null,
Primary key ('id ')
);
Insert into 'test' VALUES (1, 'name', 'daodao, www.111cn.net, xiaoqin ');
Insert into 'test' VALUES (2, 'name2', 'xiaohu, daodao, xiaoqin ');
Insert into 'test' VALUES (3, 'name3', 'xiaoqin, daodao, www.111cn.net ');
The execution statement is as follows:
Sql1: SELECT id, LIST, name from test WHERE 'daodao 'IN (LIST );
Sql2: SELECT id, LIST, name from test WHERE 'daocao' IN ('libk', 'zyfon', 'daocao ');
The execution result is as follows:
Sql1 query result is blank
Sql2 can query data
Why does sql1 fail to get the correct result, while sql2 does?
IN sql1, IN (list) and list are variables, while IN ('libk ', 'zyfon', 'daodao'), ('libk', 'zyfon ', 'daodao') is a constant. In mysql, The In parameter is a comparison parameter. Here, 'LIST' is a field In the table, that is, a variable. unless its value is the same as the name value, the returned results are empty. If you want sql1 to investigate the correct results, use the FIND_IN_SET () function. The FIND_IN_SET function in mysql is used to compare whether the 'LIST' field is a variable or a given String constant.
Sql3: SELECT id, LIST, name from 'test' WHERE FIND_IN_SET ('daodao ', 'LIST ');