83

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.

juergen d
  • 201,996
  • 37
  • 293
  • 362
luison
  • 1,850
  • 1
  • 19
  • 33
  • 2
    You can't use `in` like that. You need seperate variables or use a dynamic SQL. – juergen d Aug 14 '12 at 17:33
  • `FIND_IN_SET` and `LIKE` are OK for small tables. But they do not perform well because they have to look at every row. Most languages have no simple way (if any way) to construct the much faster `IN(...)` clause, which can use an index. – Rick James Jul 12 '19 at 06:12

3 Answers3

140

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.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Awesome! At first I thought this wasn't going to work because I both overlooked and was not familiar with the FIND_IN_SET method. I was afraid that '80' would produce a false positive because of '803' but it works wonderfully! – donL Aug 05 '15 at 19:29
  • 7
    And if you like to use a IN clause instead of NOT IN, just replace '= 0' with '> 0' --> WHERE FIND_IN_SET(id_campo, @idcamposexcluidos) > 0 – Bruno Dec 16 '15 at 13:20
  • 3
    @Bruno you could also do: `WHERE FIND_IN_SET(id_campo, @idcamposexcluidos)`. Which is to say, treat the result of `FIND_IN_SET` like a boolean. The answer for NOT IN equivalent expression could also be written like this: `WHERE NOT FIND_IN_SET(id_campo, @idcamposexcluidos)` – Kip Mar 30 '16 at 14:26
  • 2
    Important note about this: the database engine doesn't seem to be smart enough to optimize queries. `EXPLAIN EXTENDED SELECT * FROM users WHERE FIND_IN_SET(user_id, @userlist)` ==> Full table scan (very slow on large table). `EXPLAIN EXTENDED SELECT * FROM users WHERE user_id IN (1, 2, 3, 4)` ==> Uses primary key index (very fast). (Tested on MySQL 5.6/innodb.) – Kip Mar 30 '16 at 20:24
  • 1
    @Kip: Using functions generally prevent using indexes. – juergen d Mar 30 '16 at 21:11
4

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)
randiel
  • 290
  • 1
  • 16
3

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.

newfurniturey
  • 37,556
  • 9
  • 94
  • 102
  • seems to work as well but not sure why I get more rows on the same search ! Any case juergen d solution works great. Thanks – luison Aug 15 '12 at 16:48