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

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.

88534 次浏览
小开
最佳答案

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.

小开

By using CONCAT(), a pipe-separator (instead of a comma), and a little "reverse logic", you can use a variable in your NOT IN list, but instead - by using 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 alike.

小开

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)

提交答案