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.

Answer 2

if you use mysql > 5.1, you can use:

CREATE TYPE lista as (     clave int4,     valor int4 );  CREATE OR REPLACE FUNCTION test(IN vArray lista[]) ...     WHERE FIND_IN_SET(id_campo, vArray) ... 

in other case you can use a trick:

WHERE id_campo IN ( SELECT 817 as valor UNION ALL                  SELECT 803 as valor UNION ALL                     SELECT 495 as valor)