MySql select IN 子句字符串逗号分隔
我需要按以下方式执行选择查询:
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 FIND_IN_SET 函数:
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).
相关文章