Comparison of Find_in_set () and in () usage in MySQL

Comparison of Find_in_set () and in () usage in MySQL

In MySQL, the in can include the specified number, while Find_in_set () is used for a specific data type.

How to use the Find_in_set function

An example:
There is an article table with a Type field, which stores the article type, there are 1 headlines, 2 recommendations, 3 hotspots, 4 graphics ... 1,12,13 and so on.
Now there is an article he is a headline, but also hot, or graphic,
The type is stored in 1,3,4 format.
So how do we use SQL to find all the 4 text standards in the type??
It's time for our find_in_set to come.
The following is the referenced content:

SELECT * from article where Find_in_set (' 4 ', type)

The syntax for the Find_in_set function in the MySQL manual:

Find_in_set (Str,strlist)

If the string str is in a string list strlist consisting of N substrings, the return value ranges from 1 to N.
A string list is a string of sub-chains that are separated by ', ' symbols. If the first argument is a constant string and the second is a type set column, the Find_in_set () function is optimized to use bit calculations.
If STR is not strlist or strlist is an empty string, the return value is 0. If either parameter is NULL, the return value is null. This function will not work correctly when the first parameter contains a comma (', ').

mysql> SELECT find_in_set (' B ', ' a,b,c,d ');

2 because B is placed at 2 in the Strlist collection starting from 1

Select Find_in_set (' 1 ', ' 1 '); The return is 1. The Strlist collection is a bit special. Only one string actually requires the previous string to return a number greater than 0 in the next string collection.

Select Find_in_set (' 2 ', ' up '); Returns 2
Select Find_in_set (' 6 ', ' 1 '); Returns 0

Attention:

SELECT * from TreeNodes where Find_in_set (ID, ' 1,2,3,4,5 ');

Return multiple records at once using the Find_in_set function
ID is a field of a table, and then each record is an ID equal to 1,2,3,4,5
Sort of like in (collection)

SELECT * from TreeNodes where ID in (1,2,3,4,5);

Get a test sheet to illustrate the difference.

CREATE TABLE ' Test ' (
' ID ' int (8) not NULL auto_increment,
' Name ' varchar (255) is not NULL,
' List ' varchar (255) is not NULL,
PRIMARY KEY (' id ')
)

INSERT into ' Test ' VALUES (1, ' name ', ' Daodao,www.111cn.net,xiaoqin ');
INSERT into ' Test ' VALUES (2, ' name2 ', ' xiaohu,daodao,xiaoqin ');
INSERT into ' Test ' VALUES (3, ' Name3 ', ' xiaoqin,daodao,www.111cn.net ');

Originally thought that MySQL can make such a query:

Select ID, list, name from table where ' Daodao ' in (list);

A
In fact, this is not possible, so that only if the name is the first element in the list, the query is valid, otherwise it will not get results, even if ' Daodao ' is really in the list.
Take a look at this again:

Select ID, list, name from table where ' Daodao ' in (' LIBK ', ' Zyfon ', ' Daodao ');

Two
This is possible.
----------------------------------------------------------------
What is the difference between these two? Why the first one can't get the right result, and the second one can get the result.
The reason is (a) the (list) list is a variable, and (b) (' LIBK ', ' Zyfon ', ' Daodao ') is a constant.
So if you want to get (a) to work correctly, you need to use Find_in_set ():

Select ID, list, name from table where Find_in_set (' Daodao ', list);

(i) an improved version.
Summarize:
So if the list is a constant, you can use in directly or use the Find_in_set () function.

Find_in_set () and in () usage comparison in MySQL