This article introduces the FIND_IN_SET function in mysql, which is used to accurately query comma-separated data in a field

And how it differs from like and in

1. Problem discovery

There was a requirement when doing the work task before: it was necessary to query whether there was this information in this table with the service provider number passed by the interface and the product type opened to make the return result.

However, the company has multiple product types, and each service provider may open multiple different product types, which are stored in a single field separated by commas.

In this scenario, it is necessary to find the product type accurately. I initially thought of using in and like to achieve this, but in practice, this is not the effect… Please see the explanation below

2. Solve problems

Implementation using IN

The SQL statement is as follows:

SELECT
	agentNum
FROM
	p4_agent_limit_merc_access a
WHERE
	agentNum = #{agentNum} (incoming service provider number)
AND #{productType} (passed productType)
IN(a.productType);
Copy the code

Select * from a.productType; select * from A. productType; select * from A. productType; select * from A. productType;

Implement with like

The SQL statement is as follows:

SELECT
	agentNum
FROM
	p4_agent_limit_merc_access a
WHERE
	agentNum = #{agentNum} 
AND  a.productType
LIKE concat(The '%'.#{productType}, '%');
Copy the code

Like is a broad fuzzy query, but it can also be detected if a string contains the value you want to pass in. For example, our productType productType has QPOS and POS. However, the service provider of this query only has QPOS products. However, I passed in the POS type, which can be found even after using the above statement to query, which is the problem. Therefore, the query range obtained by using like will be wider, which is obviously unreasonable and not the result we want…

The FIND_IN_SET function is used

Let’s start with the FIND_IN_SET function:

FIND_IN_SET(str,strlist)

STR Specifies the string to be queried

Strlist field names are separated by commas (,), such as (1,2,6,8).

The query field (strList) contains the result of (STR), returning null or a record

If the string STR is in strList, a string list of N subchains, the return value ranges from 1 to N. A list of strings is a string composed of subchains separated by the ‘, ‘symbol. 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 bits. If STR is not in strlist or strlist is an empty string, the return value is 0. If either parameter is NULL, the value is NULL. This function will not run properly if the first argument contains a comma (‘, ‘).

Introduce a simple understanding of the good, see how to use, my SQL is as follows:

SELECT
	agentNum
FROM
	p4_agent_limit_merc_access a
WHERE
	agentNum = #{agentNum} 
AND FIND_IN_SET(#{productType},a.productType);
Copy the code

Use the above statement can accurately query the data, to achieve the requirements.

FIND_IN_SET = FIND_IN_SET

SELECT FIND_IN_SET('b'.'a,b,c,d'); Return to 2Copy the code

Because b is placed at position 2 in the strlist set starting at 1

select FIND_IN_SET(‘1’, ‘1’); So the return is 1 and then the strList is a little bit special because it only has one string and that means that the previous string must return something greater than 0 in the next string set

select FIND_IN_SET('2'.'1, 2,'); Return to 2select FIND_IN_SET('6'.'1'); Return 0Copy the code

3. Summary

When the A. productType field is constant, it can be implemented with in.

When it is a variable, it must be implemented using FIND_IN_SET.

More wonderful features please pay attention to my personal blog site: liujian.cool

Welcome to my personal public account: Program ape Liu Chuanfeng