Query problems for in and Find_in_set in MySQL

Originally thought that MySQL can make such a query
Select ID, list, name from table where ' Daodao ' in (list); A
Note: 1. Table contains three fields Id:int, List:varchar (255), Name:varchar (255)

In fact, this is not possible, so only when the ' Daodao ' is the first element in the list ( I test it seems to be the first and not, only when the value of the list field is equal to Daodao is the right), the query is valid, otherwise, not the results, even if ' Daodao ' Really in list

Test code:

CREATE TABLE ' Test ' (

' ID ' int (8) not NULL auto_increment,

' Name ' varchar (255) is not NULL,

' List ' varchar (255) is not NULL,

PRIMARY KEY (' id ')

)

INSERT into ' Test ' VALUES (1, ' name ', ' Daodao,xiaohu,xiaoqin ');

INSERT into ' Test ' VALUES (2, ' name2 ', ' xiaohu,daodao,xiaoqin ');

INSERT into ' Test ' VALUES (3, ' Name3 ', ' Xiaoqin,daodao,xiaohu ');

Test1:sql = select * from ' Test ' where ' Daodao ' in (' list ');

Gets the result null value.

Test2:sql = select * from ' Test ' where Find_in_set (' Daodao ', ' list ');

Get three data.

			1	Name	Daodao,xiaohu,xiaoqin
			2	Name2	Xiaohu,daodao,xiaoqin
			3	Name3	Xiaoqin,daodao,xiaohu

modifying table Data

Update ' test ' set ' list ' = ' Daodao ' where ' id ' = ' 1 ';

Then execute the test1 SQL, and you can return a result.

Take a look at this again:
Select ID, list, name from table where ' Daodao ' in (' LIBK ', ' Zyfon ', ' Daodao '); Two
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 ():
Select ID, list, name from table where Find_in_set (' Daodao ', list); (i) an improved version.

Summary: So if the list is a constant, you can use in directly, otherwise use the Find_in_set () function

					Find_in_set (str,strlist) If the string str is in a string list strlist consisting of N substrings, the return value ranges from 1 to N . A list of strings is a string of self-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 (', '). Mysql> SELECT find_in_set (' B ', ' a,b,c,d '); 2 Extend usage, sort by find_in_set eg $ids = ' 9,3,45,1,8,2,6 '; $sql = "... WHERE goods_id in (' {$ids} ') ORDER by Find_in_set (goods_id, ' {$ids} ') ";