How to use MySQL function Find_in_set ()

Https://www.cnblogs.com/gpfeisoft/p/5535450.html

With this function of Find_in_set. We can design one such as: A mobile phone is a smart machine, but also the andriod system.

For example: There is a product table has a Type field, he stores the product (cell phone) types, there are 1, intelligent machine, 2, andriod system, 3, Xiaomi System, 4, 1000 yuan or less

Now there is a cell phone record is 1000 yuan below, is the intelligent machine, or andriod system.

The type is stored in 4,1,2 format.

So how do we use SQL to find all the type's phones with 4 or less of $1000?

It's time for our find_in_set to come.

First look at the syntax of the Find_in_set function in the MySQL manual:

Find_in_set (Str,strlist)

If the string str is in a string list of n Strlist, the return value ranges from 1 to N. A list of strings is a string of self-chains that are 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 use bit calculations. If STR is not strlist or strlist is an empty string, the return value is 0. If either parameter is NULL, the return value is null. This function will not work correctly when the first parameter contains a comma (', ').

mysql> SELECT find_in_set (' B ', ' a,b,c,d ');

+----------------------------+

| Find_in_set (' B ', ' A,b,c,d ') |

+----------------------------+

| 2 |

+----------------------------+

1 row in Set (0.00 sec)

It's easy to use.

Let me give you an example of what I said above:

Here are the SQL statements in the actual application:

SELECT * FROM Mobile where Find_in_set (' 4 ', type);

This will be able to find all the records of the 1000-dollar phone!

______________________________________________________________________________________________________________

query table Field Pingid = (+/-)

SELECT * from ' linkinfo ' WHERE ' pingid ' REGEXP ' {ID}, ' and ' pingid ' not REGEXP ' [[: Alnum:]]+{id}, ' using the above statement, you can query it out

Simpler with Find_in_set ()

SELECT* from linkinfo WHEREfind_in_set(' 1 ',pingid )

Originally thought that MySQL can make such a query
Select ID, list, name from table where ' Daodao ' in (list); A
Note: 1. Table contains three fields Id:int, List:varchar (255), Name:varchar (255)

In fact, this is not possible, so that only if the name is the first element in the list, the query is valid, otherwise it will not be the result, even if ' Daodao ' really in the list

Take a look at this again:
Select ID, list, name from table where ' Daodao ' in (' LIBK ', ' Zyfon ', ' Daodao '); Two
This is possible.
---------------------------------------------------------

What is the difference between these two? Why the first one can't get the right result, and the second one can get the result.

The reason is (a) the (list) list is a variable, and (b) (' LIBK ', ' Zyfon ', ' Daodao ') is a constant

So if you want to get (a) to work correctly, you need to use Find_in_set ():
Select ID, list, name from table where Find_in_set (' Daodao ', list); (i) an improved version.


Summary: So if the list is a constant, you can use in directly, otherwise use the Find_in_set () function

How to use MySQL function Find_in_set ()