Use of FIND_IN_SET() function in MYSQL

The article mainly introduces the use of the FIND_IN_SET() function in mysql and the detailed explanation of the usage of in(). Friends who need it can refer to

1. Brief introduction

Syntax explanation of the FIND_IN_SET function in the MySQL manual


str The field to be queried
strlist The field name parameters are separated by "," such as (1,2,6,8,10,22) The
query field (strlist) contains the result of (str), the return result is null or record
if string If str is in the string list strlist composed of N sub-chains, the return value ranges from 1 to N. A string list is a string composed of some sub-chains separated by',' symbols. If the first parameter 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 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 run normally when the first parameter contains a comma (',').

SELECT FIND_IN_SET('b', 'a,b,c,d');

The return result is 2

select FIND_IN_SET('1', '1'); The return is 1. At this time, the strlist collection is a bit special. There is only one string. In fact, the previous string must be in the latter string collection before returning a number greater than 0

select FIND_IN_SET('2', '1,2'); 返回2
select FIND_IN_SET('6', '1'); 返回0 strlist中不存在str,所以返回0。

2. The difference between find_in_set() and in:

Create a table for testing

CREATE TABLE `tb_test` (
 `id` int(8) NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `list` varchar(255) NOT NULL,
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');
Insert picture description here

Use in to query the name

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

Use find_in_set(): query

SELECT id,name,list from tb_test WHERE FIND_IN_SET('daodao',list); -- (一)的改进版

Use in can only query constants, find_in_set can query a field

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 the field values ​​are separated by','

I am concerned about the number of micro-channel public

Insert picture description here