A brief analysis of the difference between FIND_IN_SET() and IN in MySQL

I used the Mysql FIND_IN_SET function in a project some time ago, and it felt very useful. After a while, the boss came to me and said that this needed to be changed to IN. Haha, it had no choice but to change it. The reason will be analyzed below!

Make a test table to explain the difference between the two. Just copy the test data in the Q&A area. It's fine as long as it can illustrate the problem. Haha, please forgive me if it infringes on your copyright. It's the Internet, so we 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`); 
The result is null value. 
test2:sql = select * from `test` where FIND_IN_SET('daodao',`list`); 
Get three pieces of data.

Taking the experimental data above as an example, the result of test1 is empty. Why? Because, In in MySQL is a comparison of equality, 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 result is empty. Take test1 as an example, that is, changing 'daodao' to 'daodao, xiaohu, xiaoqin' will match the first record.

Test2 returns three pieces of data, which may be what we need. The FIND_IN_SET function in MySQL is used to compare whether it is contained or not. It works well regardless of 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 consisting of N subchains, the return value ranges from 1 to N.

A string list is a string consisting of substrings separated by ',' characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bitwise arithmetic. If str is not in strlist or strlist is an empty string, the return value is 0. If any 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 achieve the same function in our project. It’s just that 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. Since the amount of data is large, the performance is definitely not high, so it is replaced by IN. If you want to see whether the query is partial or complete, you can use EXPLAIN, the explain function. If it is partial, the type is range, and if it is complete, the type is ALL. There is also a type of const, which is constant level, haha. . .

Best Practices:

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

2. If both IN and FIND_IN_SET can meet the conditions, it is better to use IN for the same reason as above, especially when the query field is the primary key or has an index.

3. If using IN cannot meet the functional requirements, you can only use FIND_IN_SET. Haha, sometimes adding a % sign to the IN condition can also solve the problem. Adding a % sign to IN is not just a comparison for equality!

Summarize

The above is all the content of this article about the difference between FIND_IN_SET() and IN in Mysql. Friends who are interested can refer to: MySQL database table partitioning precautions [recommended], several important MySQL variables, sql and MySQL statement execution order analysis, etc., I hope it will be helpful to everyone. Everyone is welcome to leave a message for exchange and discussion. If there are any deficiencies, the editor will correct and supplement them in time.