MySQL variable format for the NOT IN list of values

Question

Goes crazy trying to set a variable in a query like:

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

so i can use it on

 WHERE id_campo not in (@idcamposexcluidos) 

I tried to define the variable in different formats without any luck and did not seem to find a specific example for the above:

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

without success. It either returns an error or ignores the values.

Answer 1

You cannot use an IN clause like this. It compiles to a single string in your IN clause. But the IN clause requires 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 or use dynamic SQL or MySQL, you can use FIND_IN_SET :

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

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) ... 

otherwise you can use the trick:

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

Answer 3

Using CONCAT() , a channel separator (instead of a comma), and a bit of “inverse logic”, you can use the variable in your NOT IN list, but instead with NOT LIKE !

Example:

 SET @idcamposexcluidos = '|817|803|495|'; SELECT * FROM your_table WHERE @idcamposexcluidos NOT LIKE CONCAT('%|', id_campo, '|%'); 

This should work with both string and numeric columns.