How Can I Use a MySQL Variable with a Comma-Separated List in a 'NOT IN' Clause?

Establishing a MySQL Variable for an Excluded Value List in a "NOT IN" Query

In MySQL, constructing a query that excludes specific values based on a predefined list can be tricky. This question delves into the issue of assigning a variable to contain a comma-separated list of excluded values and using it in the "NOT IN" clause.

Incorrect Variable Formats

As mentioned in the question, assigning the variable in various formats has been unsuccessful, either resulting in errors or ignored values. Here are the attempted formats:

• SET @idcamposexcluidos='(817,803,495)';
• SET @idcamposexcluidos=817,803,495;

The Problem

The challenge lies in how the "IN" clause compiles in the query. When using the variable, it compiles into a single string: WHERE id_campo not in ('817,803,495'). However, the correct format for a "NOT IN" clause requires separate values: WHERE id_campo not in ('817','803','495').

Solution: Using FIND_IN_SET()

To overcome this issue, MySQL offers the FIND_IN_SET() function, which allows for exclusion based on a list defined in a variable. The modified query would look like this:

SET @idcamposexcluidos='817,803,495';
...
WHERE FIND_IN_SET(id_campo, @idcamposexcluidos) = 0

This solution successfully excludes rows where the id_campo values match any value in the @idcamposexcluidos variable.

Note: While FIND_IN_SET() resolves the issue, it prevents the use of indexes on the id_campo column.

The above is the detailed content of How Can I Use a MySQL Variable with a Comma-Separated List in a 'NOT IN' Clause?. For more information, please follow other related articles on the PHP Chinese website!