Query of IN and FIND_IN_SET IN mysql _ MySQL

Query of IN and FIND_IN_SET IN mysql bitsCN.com

Query of IN and FIND_IN_SET IN mysql

I thought mysql could perform such a query.

select id, list, name from table where 'daodao' IN (list);   

(1)

Note: 1. table contains three fields: int, list: varchar (255), name: varchar (255)

In fact, this is not the case, so only when 'Dao' is the first element in the list (it seems that the first element is not the case during the test, the query is valid only when the value of the list field is equal to that of daodao. otherwise, no result is returned, even if 'Dao' is actually in the list

Test code:

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,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`);

The result is null.

test2:sql = select * from `test` where FIND_IN_SET('daodao',`list`);

Get three pieces of data.

        1 name daodao,xiaohu,xiaoqin        2 name2 xiaohu,daodao,xiaoqin        3 name3 xiaoqin,daodao,xiaohu

Modify table data

update `test` set `list`='daodao' where `id`='1';

Run the SQL statement test1 and return a result.

Let's take a look at this:

select id, list, name from table where 'daodao' IN ('libk', 'zyfon', 'daodao');  

(2)

This is acceptable.

---------------------------------------------------------

What are the differences between the two? Why can't the first one get the correct result, but the second one get the result.

The reason is that (1) in (list) list is a variable, while in (2) ('libk', 'zyfon', 'daodao ') is a constant.

So if you want (1) to work correctly, use find_in_set ():

select id, list, name from table where FIND_IN_SET( 'daodao' , list);

(1) release version.

Conclusion: If list is a constant, you can use IN directly; otherwise, use the FIND_IN_SET () function.

FIND_IN_SET (str, strlist)

If the str string is in the strlist consisting of N substrings, the return value ranges from 1 to N. 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. If str is not in strlist or strlist 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

Extended usage, sorted 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}')";

BitsCN.com