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

In this issue, the editor will bring you about how to use the find_in_set () function in mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The syntax 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)

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

Example:

Mysql > SELECT FIND_IN_SET ('bendry,' a ~);-> 2 because b is placed in the position of 2 in the strlist collection, and select FIND_IN_SET starts from 1 ('1cm,' 1'). The return is 1. At this time, the strlist collection is a little special. Only one string actually requires that the previous string must be in the latter string collection to return a number greater than 0 select FIND_IN_SET ('2numbers,' 1Magazine 2'); return 2 select FIND_IN_SET ('6values,' 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 one table, and then each record is a bit like in (collection) when id equals 1, 2, 3, 4, 5.

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

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

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);-- an improved version of

Summary:

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

The difference between find_in_set () and like:

In mysql, sometimes when we do a database query, we need to get a record that contains a certain value in a certain field, but it cannot be solved with like. Using like may find records we do not want, which is more accurate than like. At this time, the FIND_IN_SET function of mysql will come in handy. Let's take a look at an example.

Create the table and insert the statement:

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

Where limits represents the permissions the user has (separated by commas). Now you want to query the user with permission number 2. If you use the like keyword, the query result is as follows:

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

In this way, the users whose second piece of data does not have permission'2' have also been found out, which is not in line with expectations. Let's use the mysql function find_in_set () to solve the problem.

SELECT * FROM users WHERE FIND_IN_SET (2)

In this way, we can achieve the desired results, and the problem will be solved!

Note: the mysql string function find_in_set (str1,str2) function returns the index of the location of the str1 in the str2, and the str2 must be separated by ",".

Summary: like is a wide range of fuzzy matching, there is no delimiter in the string, Find_IN_SET is an exact match, field values are separated by English ",", and the result of Find_IN_SET query is smaller than that of like query.

This is how the find_in_set () function shared by the editor is used in mysql. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.