Difference between FIND_IN_SET () and IN Mysql: mysqlfind_in_set

Difference between FIND_IN_SET () and IN Mysql: mysqlfind_in_set

The FIND_IN_SET function of Mysql was used in the project some time ago. After a while, the boss found me and said, "This needs to be changed to" IN ". Haha, you can only change it. The reason will be analyzed below!

Make a test table to explain the differences between the two. copy the test data directly in the Q & A area to explain the problem. Haha, If you infringe on your copyright, please forgive me, Internet, you need to share it!

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'). Three data items are obtained.

Speaking with the above experiment data, the result obtained by test1 is blank. Why? Because In mysql is a comparison, Here 'LIST' is a field In the table, that is, a variable, unless its value is exactly the same as the name value, otherwise, the returned results are empty. For test1, the first record is matched only when 'daodao' is changed to 'daodao, xiaohu, and xiaoqin.

Test2 returns three pieces of data, which may be exactly 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.In MySQL, the original type is 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 substrings 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. Str can also be a variable, such as a field in the table.

Of course, this is not why we need to replace FIND_IN_SET with IN our project, because both of them can implement functions.Only IN has higher performance than FIND_IN_SET. The field we want to query is the primary key. When IN is used, indexes will be used, and only some data IN the table will be queried.. FIND_IN_SET will query all the data IN the table. Because the data volume is large and the performance is definitely not high, replace it with IN. If you want to check whether the query is partial or ALL, you can use the EXPLAIN function to check whether the type is range or ALL ), another type is const, Which is constant-level...

Best practices:

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

2. If both IN and FIND_IN_SET can meet the conditions, you 'd better use IN for the same reason, especially when the query field is a primary key or has an index.

3. If IN cannot meet the functional requirements, you can only use FIND_IN_SET. Haha, sometimes adding a % number to the conditions IN can solve the problem, adding the "%" sign does not just compare whether it is equal!

Summary

The above is all the differences between FIND_IN_SET () and IN Mysql. For more information, see: mySQL database table partition considerations Daquan [recommended], several important MySQL variables, SQL and MySQL statement execution sequence analysis, etc., hope to help everyone. You are welcome to leave a message for discussion. If there are any deficiencies, the editor will promptly correct and supplement them.