Detailed explanation of the use of find_in_set() function in MySQL

First, let’s take an example:

There is a type field in the article table, which stores the article type, including 1 headline, 2 recommendation, 3 hot spot, 4 picture and text, etc.

Now there is an article that is both a headline, a hot topic, and a picture and text, and is stored in the format of 1,3,4 in type. So how do we use SQL to find all articles with type 4 that have pictures and texts? ?

This is where find_in_set comes in. The following is the quoted content:

select * from article where FIND_IN_SET('4',type)

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

Syntax of find_in_set function from MySQL manual:

FIND_IN_SET(str,strlist)

str The string to be searched

The strlist field name parameters are separated by "," such as (1,2,6,8)

Query the results containing (str) in the field (strlist), and return the result as null or record

If the string str is in the string list strlist consisting of N substrings, 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 (',').

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

example:

mysql> SELECT FIND_IN_SET('b', 'a,b,c,d'); 
-> 2 Because b is placed at position 2 in the strlist set, starting from 1, select FIND_IN_SET('1', '1'); The return value is 1. The strlist set at this time is a bit special. There is only one string. In fact, it requires that the previous string must be in the next string set to return a number greater than 0. select FIND_IN_SET('2', '1，2'); Return 2 
select FIND_IN_SET('6', '1'); returns 0

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

Notice:

select * from treenodes where FIND_IN_SET(id, '1,2,3,4,5');

Use the find_in_set function to return multiple records at once. id is a field of a table, and each record has id equal to 1, 2, 3, 4, 5. It is a bit like in (set)

select * from treenodes where id in (1,2,3,4,5);

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

The difference between find_in_set() and in:

Make a test table to illustrate the difference between the two

CREATE TABLE `tb_test` (
 `id` int(8) NOT NULL auto_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');

I originally thought that MySQL could perform such a query:

SELECT id,name,list from tb_test WHERE 'daodao' IN(list); -- (I) 

In fact, this does not work. The query is only valid when the value of the list field is equal to 'daodao' (completely matching the string before IN). Otherwise, no results will be obtained, even if 'daodao' is really in the list.

Let’s look at this:

SELECT id,name,list from tb_test WHERE 'daodao' IN ('libk', 'zyfon', 'daodao'); -- (II) 

That's fine.

What is the difference between these two? Why the first one cannot get the correct result, but the second one can. The reason is that (list) list in (1) is a variable, while ('libk', 'zyfon', 'daodao') in (2) are constants.

So if you want (1) to work correctly, you need to use find_in_set():

SELECT id,name,list from tb_test WHERE FIND_IN_SET('daodao',list); -- Improved version of (I) 

Summarize:

So if list is a constant, you can use IN directly, otherwise you need to use the find_in_set() function.

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

The difference between find_in_set() and like:

In MySQL, sometimes when we do database queries, we need to get records that contain a certain value in a certain field, but it cannot be solved by using LIKE. Using LIKE may find records we don’t want. It is more accurate than LIKE. At this time, MySQL’s FIND_IN_SET function comes in handy. Let’s take a look at an example.

Create table and insert statement:

CREATE TABLE users(
  id int(6) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  limits VARCHAR(50) NOT NULL, -- Privilege PRIMARY KEY (id)
);
INSERT INTO users(name, limits) VALUES('Xiao Zhang','1,2,12'); 
INSERT INTO users(name, limits) VALUES('Xiao Wang','11,22,32');

Among them, limits indicates the permissions that the user has (separated by commas). Now we want to query the user with permission number 2. If we use the like keyword, the query results are as follows:

SELECT * FROM users WHERE limits LIKE '%2%'; 

In this way, the second data is also found for users who do not have permission '2', which is not as expected. The following uses the MySQL function find_in_set() to solve the problem.

SELECT * FROM users WHERE FIND_IN_SET(2,limits); 

This way we can achieve the desired effect and the problem is solved!

Note: The MySQL string function find_in_set(str1,str2) returns the index of the position of str1 in str2. str2 must be separated by ",".

Summary: LIKE is a broad fuzzy match, there is no separator in the string, Find_IN_SET is an exact match, the field values ​​are separated by English "," and the result of Find_IN_SET query is smaller than the result of LIKE query.

Summarize

The above is a detailed explanation of the use of the find_in_set() function in MySQL introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!