A brief analysis of the difference between FIND_IN_SET of and IN in Mysql

Some time ago, the FIND_IN_SET function of Mysql was used in the project, which felt quite easy to use. After a period of time, the boss found me and said, this needs to be changed to IN, haha, it can only be changed, and the reasons will be analyzed below!

Get a test table to talk about the difference between the two, test data directly in the question and answer area copy1, can explain the problem, haha, if you infringe your copyright, please forgive me, the Internet, you need to share!

 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`); 
 Get a null result . 
test2:sql = select * from `test` where FIND_IN_SET('daodao',`list`); 
 Get 3 Items of data.  

Taking the above experimental data as an example, the result obtained by test1 is empty. Why? Because In in mysql is comparatively equal, here 'list' is a field in the table, that is, a variable, unless its value is exactly the same as the value of name, the returned results are all empty. Take test1 for example, that is, changing 'daodao' to 'daodao, xiaohu, xiaoqin' will match the first entry.

test2 returns three pieces of data, which may be just what we need. The FIND_IN_SET function in mysql is used to compare whether the 'list' field is a variable or a given string constant. The prototype in MySQL is: FIND_IN_SET (str, strlist). If the string str is in the string list strlist composed of N subchains, the return value ranges from 1 to N.

A string list is a string composed of 1 subchain separated by ',' symbols. If the first argument is a constant string and the second is an type SET column, the FIND_IN_SET () function is optimized to use bit calculation. If str is not in strlist or strlist is an empty string, the return value is 0. If any one parameter is NULL, the return value is NULL. This function will not work properly if the first argument contains a comma (','). str can also be a variable, such as a field in a table.

Of course, this is not the reason why we need to replace FIND_IN_SET with IN in our project, because both can be implemented in our project. Only IN has higher performance than FIND_IN_SET. The field we want to query is the primary key. When using IN, the index will be used, and only part of the data in the table will be queried. FIND_IN_SET will query all the data in the table. Because of the large amount of data, the performance is definitely not high, so it is replaced by IN. If you want to see part or all of the query, you can use EXPLAIN to view the interpretation function. If it is part, type is range (range), all of it is type is ALL (all), and there is an type is const, which is of constant order, hehe. . .

Best practices:

1. If the condition to be queried is constant, use IN, and if it is variable, use FIND_IN_SET. You can use index. It seems, haha.

2. If both IN and FIND_IN_SET satisfy the criteria, it is better to use IN for the same reasons as above, especially if the query field is a primary key or if there is an index.

3. If using IN can't meet the functional requirements, you can only use FIND_IN_SET. Haha, sometimes adding a% sign to the conditions in IN can also solve the problem. Adding a% sign IN is not just to compare whether it is equal!

Summarize

The above is the whole content of this article about the difference between FIND_IN_SET () and IN. Interested friends can refer to: MySQL database table partition precautions "Recommendation", several important MySQL variables, sql and MySQL statement execution sequence analysis, etc., hoping to be helpful to everyone. Welcome to leave a message to exchange and discuss. If there are any shortcomings, this site will correct and supplement in time.