MySQL FIND_IN_SET () and the IN comparison Tag: FIND_IN_SET, IN

Some time ago used to MySQL FIND_IN_SET function, I feel very good use. Over a period of time, the boss found me that this needs to be changed to IN, ha ha, can only be changed because in the following analysis to!

Get hold of the test table is that the difference between the two, the test data directly in the Q & A area to copy a description of the problem on the line, if the violation of your copyright, please forgive me, the Internet, you need to share!

view plain

1. ?   
2. CREATE TABLE `test` (  
3.   `id` int(8) NOT NULL auto_increment,  
4.   `name` varchar(255) NOT NULL,  
5.   `list` varchar(255) NOT NULL,  
6.   PRIMARY KEY  (`id`)  
7. )  
8.   
9.   
10. INSERT INTO `test` VALUES (1, 'name', 'daodao,xiaohu,xiaoqin');  
11. INSERT INTO `test` VALUES (2, 'name2', 'xiaohu,daodao,xiaoqin');  
12. INSERT INTO `test` VALUES (3, 'name3', 'xiaoqin,daodao,xiaohu');  
13.   
14.   
15. test1:sql = select * from `test` where 'daodao' IN (`list`);  
16. .  
17. test2:sql = select * from `test` where FIND_IN_SET('daodao',`list`);  
18. ?  

Take the above experimental data speak test1 get the result is empty, why? Because in mysql In comparison the range here 'list' is a field in the table, which is variable, unless it is just the same and the value of the name, or the results returned are empty. Test1, ie 'daodao' changed to 'daodao, xiaohu, xiaoqin' will match to the first article in mind.

The test2 Back three data, it may be that we just need. FIND_IN_SET function is used in the mysql is not included, regardless of the 'list' field is a variable or a given string constants can work well. MySQL in the prototype:FIND_IN_SET (str, strlist). Range between 1 to N if the string str the string list strlist by N sub-chain, the return value. 
A string list is a string ',' separate sub-chain. If the first argument is a constant string and the second is the type SET column, the FIND_IN_SET () function is optimized to use bit- If str is not strlist or strlist to an empty string, the return value is 0. If any parameter is NULL, the return value is NULL. This function in the first argument contains a comma (',') will not work correctly. str can be a variable, such as a field in the table.

Of course, this is not our project will need to replace FIND_IN_SET IN reason, because both functionality can be achieved in our project. Just IN FIND_IN_SET performance. We want to query the field is the primary key, IN will use the index, only part of the data in the lookup table. FIND_IN_SET will check all the data in the table, due to the large amount of data, the performance is certainly not high, so replaced IN. Want to see the query part or all, you can use EXPLAIN explain functional view, the part type range (range), all the type ALL (all), there is a type is const, constant level, huh, huh. . .

Best Practices:

1, if the conditions to be queried is constant then use IN, variable FIND_IN_SET can use the index, looks like, ha ha.

2, If you IN and FIND_IN_SET can meet the conditions, it is best to use IN the same reason, especially the query field-based key or index.

3, if you use IN can not meet the functional requirements, it can only use FIND_IN_SET, sometimes maybe IN the condition and% can also solve the problem, add IN% not just compare for equality!