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.
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.
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)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.)
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)
inlike that. You need seperate variables or use a dynamic SQL.FIND_IN_SETandLIKEare 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 fasterIN(...)clause, which can use an index.