Basic usage of find_in_set function in mysql

Preface

This is a new function I came across recently.

My usage scenario in the project is as follows:

There is an application type table, which contains parentId field and parentIds field. The former is the parent id, and the latter is the multi-level parent id. Both are varchar type in the database. parentIds is composed of multiple parent ids and separated by "".

The team leader told me that I could use this function, so I went to learn about it and use it.

grammar

FIND_IN_SET(str,strlist)

definition

1. If the string str is in the string list strlist consisting of multiple substrings, the return value ranges from 1 to N.
2. A string list is a string consisting of a series of self-links separated by ',' characters.
3. If the first argument is a constant string and the second is a typeSET column, the FIND_IN_SET() function is optimized to use bitwise arithmetic.
4. If str is not in strlist or strlist is an empty string, the return value is 0.
5. If any parameter is NULL, the return value is NULL. This function will not work properly if the first argument contains a comma (',').
   

strlist: A string linked by English commas "," for example: "a,b,c,d". The string is similar to the SET type value linked by commas.

Example: SELECT FIND_IN_SET('b','a,b,c,d'); //The return value is 2, which is the second value

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 handy. The following is the quoted content:

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

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

This is the end of this article about the basic usage of the find_in_set function in MySQL. For more information about the usage of the MySQL find_in_set function, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!