sql - MySQL variable format for a "NOT IN" list of values

Question

Going crazy trying to set a variable in a query of type:

SET @idcamposexcluidos='817,803,495';

so i can then use it on a

WHERE id_campo not in (@idcamposexcluidos)

I've tried defining the variable in different formats with no luck and don't seem to find an specific example for the above:

SET @idcamposexcluidos='(817,803,495)';
...
WHERE id_campo not in @idcamposexcluidos


SET @idcamposexcluidos=817,803,495;

with no success. It either returns an error or ignores the values.

Answer 1

You can't use the IN clause like that. It compiles to a single string in your IN clause. But an IN clause needs separate values.

WHERE id_campo not in (@idcamposexcluidos)

compiles to

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

but it should be

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

To overcome this either use dynamic SQL or in MySQL you could use FIND_IN_SET:

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

but using a function like FIND_IN_SET() can not make use of indexes.