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