What is the method of using string in in condition in mysql

The in condition in mysql uses a string

Scenario

When sql uses in as a condition, a parameter is used to satisfy the query condition, and passing in the string directly cannot satisfy the condition.

select id,name from user where id in(?)

Method

Use FIND_IN_SET(str,strlist) function

select id,name from user where FIND_IN_SET(id,#{strlist})

• ##str: Conditional field

• strlist: matching value set

select id,name from user where FIND_IN_SET(id,'111,22,333')

mysql query in condition parameter is a string with comma, the query result is wrong

If there is the following sql:

SELECT
    (
        SELECT
            GROUP_CONCAT(content)
        FROM
            account_limit_user ur
        WHERE
            ur.id IN (T1.limit_user)
        GROUP BY
            ur.id
    ) AS limit_user
FROM
    account T1
WHERE
    1 = 1

T1.limit_user is the value queried from the account table

‘9,8,4’

Query sql The correct query result should be:

Not recommended, promote customers, promote customers

The actual result is:

Not recommended

Reason:

If in in mysql is a string, it will be automatically converted into int type. The following method is used internally:

CAST('4,3' AS INT)

Because the comma is mistakenly written as a semicolon, "4,3" becomes "4;", so the result of the above query SQL only contains the first one.

Solution

FIND_IN_SET('查询的值（如:1）', '使用逗号隔开的字符串集合，如:（'1,2,3'）')

Correct sql

SELECT
    (
        SELECT
            GROUP_CONCAT(content)
        FROM
            account_limit_user ur 
        WHERE
            FIND_IN_SET(ur.id, T1.limit_user) > 0
    ) AS limit_user,
    T1.limit_user limit_userid
FROM
    account T1
WHERE
    1 = 1

The above is the detailed content of What is the method of using string in in condition in mysql. For more information, please follow other related articles on the PHP Chinese website!