MySQL变量格式为"NOT IN".值清单 [英] 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';

这样我就可以在

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.

推荐答案

您不能像这样使用IN子句.它在IN子句中编译为单个字符串.但是IN子句需要分开的值.

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)

编译为

WHERE id_campo not in ('817,803,495')

但应该是

WHERE id_campo not in ('817','803','495')

要克服此问题，请使用动态SQL或在MySQL中使用

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

但使用类似FIND_IN_SET()的功能不能使用索引.

but using a function like FIND_IN_SET() can not make use of indexes.

这篇关于MySQL变量格式为"NOT IN".值清单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！