The use of the Find_in_set () function in MySQL

First of all, for 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 and so on.
Now there is an article that he is a headline, hot, or graphic, type in the format of 1,3,4 storage. So how do we use SQL to find 4 of all types of text in the type??
It's time for our find_in_set to come. The following is the referenced content:

Select *  from where Find_in_set ('4', type)

----------------------------------------------------------
The syntax for the Find_in_set function in the MySQL manual:
Find_in_set (Str,strlist)

STR string to query for
Strlist field name parameters are separated by "," as (1,2,6,8)
Query field (strlist) contains the result of (str), the result is null or the record

If the string str is in a string list of n Strlist, 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 (', ').

--------------------------------------------------------

Example:
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);

--------------------------------------------------------

The difference between Find_in_set () and in:

Get a test sheet to illustrate the difference.

CREATE TABLE' tb_test ' (' ID ')int(8) not NULLauto_increment, ' name 'varchar(255) not NULL, ' list 'varchar(255) not NULL,  PRIMARY KEY(' id '));
INSERT  into' Tb_test 'VALUES(1,'name','Daodao,xiaohu,xiaoqin');INSERT  into' Tb_test 'VALUES(2,'name2','Xiaohu,daodao,xiaoqin');INSERT  into' Tb_test 'VALUES(3,'Name3','Xiaoqin,daodao,xiaohu');

Originally thought that MySQL can make such a query:

SELECT  from WHERE ' Daodao ' inch --

In fact, this is not possible, so that only if the value of the list field equals ' Daodao ' (and the string in front of it exactly), 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  from WHERE ' Daodao ' inch ('libk'zyfon'daodao'  --  (ii)

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  from WHERE Find_in_set ('daodao'--  (i) 's improved version

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

--------------------------------------------------------

The difference between find_in_set () and like:

In MySQL, sometimes when we are doing database queries, we need to get a record of a certain value in a field, but it is not solved with like, we can find the record that we do not want, and it is more accurate than the find_in_. The set function comes in handy, let's take a look at an example.

To create a table and insert a statement:

CREATE TABLEUsers (IDint(6) not NULLauto_increment, nameVARCHAR( -) not NULL, LimitsVARCHAR( -) not NULL,--Permissions    PRIMARY KEY(ID));INSERT  intoUsers (name, limits)VALUES('Xiao Zhang','1,2,12'); INSERT  intoUsers (name, limits)VALUES('Xiao Wang','11,22,32');

Where limits represents the permissions that the user has (separated by commas) and now wants to query a user with a permission number of 2, the query results are as follows if you use the LIKE keyword:

SELECT *  from WHERE  like ' %2% ';

So that the second data does not have permission ' 2 ' user also found out, does not meet the expectations. The following is solved using MySQL function find_in_set ().

SELECT *  from WHERE Find_in_set (2, limits);

This will be able to achieve our expected effect, the problem is solved!

Note : The MySQL string function find_in_set (STR1,STR2) function is the index of the location where STR1 is returned in str2, and the str2 must be separated by ",".

Summary:like is a wide range of fuzzy matches, there are no delimiters in the string, Find_in_set is exact match, field values are delimited in English "," and the results of the Find_in_set query are smaller than the results of the as query.

--------------------------------------------------------

The use of the Find_in_set () function in MySQL