Mysql select IN子句字符串以逗号分隔 [英] MySql select IN clause string comma delimited

问题描述

我需要以以下方式执行选择查询:

I need to perform a select query in the following manner:

select * from my_table where id NOT IN (comma_delimited_string);

实现该目标的正确方法是什么?

What is the correct way to achieve that?

考虑到我可以控制发送字符串的客户端代码这一事实，有没有更好的方法? (该字符串将包含大约30个id，因此我试图避免发送30个参数，每个id一个).

Considering the fact that I am in control of the client code which sends the string, is there a better approach? (the string will hold approximately 30 id's so I am trying to avoid sending 30 parameters, one for each id).

谢谢大家

推荐答案

您可以使用MySQL

You can use the MySQL FIND_IN_SET function:

SELECT *
FROM my_table
WHERE FIND_IN_SET(id, comma_delimited_string) = 0

---

附录:请注意，上面的查询不是 可优化的，因此，如果在id上有索引，MySQL将不会使用它.您必须确定使用FIND_IN_SET的相对简单性是否值得承受潜在的性能损失(我之所以说是潜在的，是因为我不知道id是否已建立索引或您的表是否足够大以至于不能成为索引)关注).

---

Addendum: Note that the query above is not optimizable, so if you have an index on id MySQL won't use it. You'll have to decide if the relative simplicity of using FIND_IN_SET is worth taking a potential performance hit (I say potential because I don't know if id is indexed or if your table is large enough for this to be a concern).

这篇关于Mysql select IN子句字符串以逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！