The difference between Find_in_set () and in MySQL

Get a test sheet to illustrate the difference.

CREATE TABLE ' test ' (  ' id ' int (8) NOT NULL auto_increment,  ' name ' varchar (255) is not NULL,  ' list ' varchar (255) N OT NULL,  PRIMARY KEY  (' id ')) INSERT INTO ' Test ' VALUES (1, ' name ', ' Daodao,xiaohu,xiaoqin '); insert INTO ' test ' VAL UES (2, ' name2 ', ' xiaohu,daodao,xiaoqin '); INSERT into ' Test ' VALUES (3, ' Name3 ', ' Xiaoqin,daodao,xiaohu ');

Originally thought that MySQL can make such a query:

Select ID, list, name from table where ' Daodao ' in (list); A

In fact, this is not possible, so that only if the name is the first element in the list, the query is valid, otherwise it will not get results, even if ' Daodao ' is really in the list.

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.

Summarize:

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

The difference between Find_in_set () and in MySQL