Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the find_in_set () function in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The syntax explanation of the find_in_set function in the MySQL manual:

FIND_IN_SET (str,strlist)

String to be queried by str

The strlist field name parameter is separated by "," such as (1, 2, 6, 8, 10, 22)

Query field (strlist) contains the result of (str), and the returned result is null or record

If the string str is in the string list strlist consisting of N subchains, the return value ranges from 1 to N. A list of strings is a string made up of subchains 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 be calculated in bits. 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 (',').

It's okay if you don't understand the concept, according to the following categories:

Example 1:

SELECT FIND_IN_SET ('biculture,' a recorder breco c recorder d')

Results: 2

Because b is placed in the position of 2 in the strlist collection starting with 1

Select FIND_IN_SET ('1string,' 1'); return is 1. At this time, the strlist collection is a little special. Only one string actually requires that the previous string be in the latter string set before returning a number greater than 0.

Select FIND_IN_SET ('2records,' 1recorder 2'); returns 2select FIND_IN_SET ('6levels,' 1'); returns 0 because there is no str in 0 strlist.

The difference between find_in_set () and in:

Get a test table to show the difference between the two.

CREATE TABLE `tb_ test` (`id`int (8) NOT NULL auto_increment, `name` varchar (255th) NOT NULL, `list`varchar (2555NOT 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')

It was thought that mysql could make such a query:

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

In fact, this doesn't work, so the query is valid only if the value of the list field is equal to 'daodao' (which exactly matches the string before IN), otherwise you won't get the result, even if the' daodao' is actually in the list.

And take a look at this:

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

It's okay to do this.

What's the difference between these two? Why the first article can not get the right result, while the second can get the result. The reason is that (list) list is a variable in (1), and ('libk',' zyfon', 'daodao') in (2) is a constant.

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 (1)

Summary:

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

That is, the effect of the two sql queries is the same:

SELECT * from C_PURCHASINGMASTERDATA where FIND_IN_SET (EKGRP,'C54,C02,C14,C60,C06,C61,C53,C51,C12,C08,C03,C07') SELECT * from C_PURCHASINGMASTERDATA where EKGRP in ("C54"," C02", "C14"," C60", "C061"," C53", "C51"," C12", "C08"," C03", "C07")

But if the value in the second sentence sql is a variable field passed into sql, then the second sentence sql doesn't work. Decide whether to use in or the find_in_set () function based on the actual situation.

The difference between find_in_set () and like:

The main difference is that like is a broad fuzzy query, while find_in_set () is an exact match, and field values are separated by','.

Now to query the user with role number 2, query it with the like keyword:

SELECT userid,username,userrole role FROM `user`WHERE userrole LIKE'% 2%'

Results:

Query with find_in_set ():

SELECT userid,username,userrole role FROM `user` WHERE find_in_set ('2mom dint userrole)

Results:

Obviously, the result from the find_in_set () query is what we want. So both of them

The main difference is that like is a wide range of fuzzy queries; while find_in_set () is an exact match and field values are separated by',', the result of the Find_IN_SET query is smaller than that of the like query.

Comparison of find_in_set () and in () usage in mysql

In mysql, in can include specified numbers, while find_in_set () is used for specific data types.

How to use find_in_set function

For example:

There is a type field in the article table, which stores the article type, with 1 headline, 2 recommendations, 3 hotspots, 4 pictures and texts. .1, 12, 13, and so on.

Now there is an article in which he is not only a headline, but also a hot spot, or a picture and text.

In type, it is stored in the format of 1pm, 3pm and 4.

So how do we use sql to find all the articles with 4 picture and text standards in type?

It's time for our find_in_set to come out.

The following is the referenced content:

Select * from article where FIND_IN_SET ('44th century type)

The syntax of the find_in_set function in the MySQL manual:

FIND_IN_SET (str,strlist)

If the string str is in the string list strlist consisting of N subchains, the return value ranges from 1 to N.

A list of strings is a string made up of subchains 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 be calculated in bits.

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 (',').

Mysql > SELECT FIND_IN_SET ('bachelors,' ameme brecrium cMagned')

-> 2 because b is placed in the position of 2 in the strlist collection starting at 1

Select FIND_IN_SET ('1string,' 1'); return is 1. At this time, the strlist collection is a little special. Only one string actually requires that the previous string be in the latter string set before returning a number greater than 0.

Select FIND_IN_SET ('2records,' 1jue 2'); return 2select FIND_IN_SET ('6cycles,' 1'); return 0

Note:

Select * from treenodes where FIND_IN_SET (id, '1dy2, 3re4, 5')

Use the find_in_set function to return more than one record at a time

Id is a field of a table, and then each record is when id equals 1, 2, 3, 4, 5.

Somewhat similar to in (collection)

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

Get a test table to show the difference between the two.

CREATE TABLE `test` (`id`int (8) NOT NULL auto_increment, `name` varchar (255NOT NULL), `list`varchar (255NOT NULL,PRIMARY KEY (`id`)) INSERT INTO `test`VALUES (1, 'name',' daodao,www.jb51.net,xiaoqin'); INSERT INTO `test`VALUES (2, 'name2',' xiaohu,daodao,xiaoqin'); INSERT INTO `test`VALUES (3, 'name3',' xiaoqin,daodao,www.jb51.net')

It was thought that MySQL could make such a query:

Select id, list, name from table where 'daodao' IN (list)

(1)

In fact, this doesn't work, so the query is valid only if name is the first element in list, otherwise you won't get a result, even if 'daodao' is really in list.

And take a look at this:

Select id, list, name from table where 'daodao' IN (' libk', 'zyfon',' daodao')

(2)

It's okay to do this.

What's the difference between these two? Why the first article can not get the right result, while the second can get the result.

The reason is that (list) list is a variable in (1), and ('libk',' zyfon', 'daodao') in (2) is a constant.

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

Select id, list, name from table where find_in_set ('daodao',list); on how to use the find_in_set () function in mysql to share here, I hope the above can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.