“NOT IN"的 MySQL 变量格式值列表
尝试在类型查询中设置变量会发疯:
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 子句中编译为一个 single 字符串.但是 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 中使用 FIND_IN_SET:
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.
相关文章