How to Correctly Format MySQL Variables for Use in a 'NOT IN' Clause?

MySQL Variable Format for a "NOT IN" List of Values

When attempting to exclude certain values in a MySQL query using a "NOT IN" list, assigning a variable to the excluded values can be a convenient solution. However, ensuring the correct variable format is crucial.

As described in your query, you tried assigning the variable '@idcamposexcluidos' with different formats:

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

However, none of these formats work as expected. The reason is that the "IN" clause requires separate values, not a single string. Therefore, the query:

WHERE id_campo not in (@idcamposexcluidos)

effectively becomes:

WHERE id_campo not in ('817,803,495')

which is incorrect. The values should be provided as separate string literals:

WHERE id_campo not in ('817','803','495')

To overcome this issue, there are two options:

1. Dynamic SQL: Create the query dynamically using a concatenation of the excluded values.
2. MySQL FIND_IN_SET() Function: Use the FIND_IN_SET() function to check if the value exists in the comma-separated list of excluded values, as shown below:

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

Note that using the FIND_IN_SET() function may impair index usage.

The above is the detailed content of How to Correctly Format MySQL Variables for Use in a 'NOT IN' Clause?. For more information, please follow other related articles on the PHP Chinese website!