Example of "MySQL" MySQL find_in_set use

Excellent articles for > reference

Find_in_set (Str,strlist)

> A simple example

The function is to look for the first parameter in the second argument and return the position where the first argument is, or 0 if it does not exist. Where the second parameter is a string separated by ",".

--1
Select Find_in_set (' 1 ', ' 1,2,3,4,5,6 ');

--2SelectFind_in_set ('2','1,2,3,4,5,6');
--0SelectFind_in_set ('7','1,2,3,4,5,6');
--0SelectFind_in_set ('2','1,21,3,4,5,6');
--0SelectFind_in_set ('2',"');
--0SelectFind_in_set ('2','3,4,5,6.');
--NULLSelectFind_in_set (NULL,'1,2,3,4,5,6');
--NULLSelectFind_in_set (1,NULL);
--0SelectFind_in_set ('2,3','1,2,3,4,5,6');

> Partial use function for replaceable delimited strings

Sometimes we get the "2,3,4" string, and we want to separate it into 5 rows, each of which is 2, 3, 4, respectively.

At this point, we naturally think of a Java-like string split function, but MySQL does not seem to have similar functions. (I did not find, if any, please inform me OH)

In fact, "2,3,4" is a certain business meaning, or from a table, then you can use the Find_in_set "2,3,4" from the corresponding table to find out. In general, the key is indexed, from a large amount of data to find a small amount of data, the efficiency is very high.

The following is a simple example:

SelectT.id from (Select 1  asId'Nick'  asnameUnion  AllSelect 2  asId'Viki'  asnameUnion  AllSelect 3  asId'Robin'  asnameUnion  AllSelect 4  asId'Teng'  asnameUnion  AllSelect 5  asId'Mike'  asnameUnion  AllSelect 6  asId' would'  asname) TwhereFind_in_set (T.id,'2,3,4')> 0;

View Code

Example of "MySQL" MySQL find_in_set use