MySQL IN() for two value/array?

MySQL IN() for two value/array?

旧伤慢歌 发布于 2021-11-25 字数 685 浏览 832 回复 3 原文

I'm having trouble finding a better way to search MySQL for a pair of values in a table. I have the value pairs in an array, and would like to duplicate the IN() function, but for more than 1 value.

For example purposed; I have the following 3 pairs:

foo,1
boo,2
goo,3

The current solution puts me at:

SELECT * FROM [table] WHERE 
(column1 = 'foo' AND column2 = 1) OR
(column1 = 'boo' AND column2 = 2) OR
(column1 = 'goo' AND column2 = 3);

I'd like to think there's a more "sexy" solution seeing that I could have as many as a hundred pairs and having that may ORs kind of makes me nauseous. Thanks!!!

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

赢得她心 2022-06-07 3 楼

Great answers from @Quassnoi and @KM !!!

Also, you can get pairs duplicates and select them for post-processing:

SELECT *
FROM `foo`
WHERE (`id_obj` , `Foo_obj`)
IN (
  SELECT `id_obj` , `Foo_obj`
  FROM `foo`
  GROUP BY `id_obj` , `Foo_obj`
  HAVING count(*) > 1
)
街角卖回忆 2022-06-07 2 楼

If you can get your values into a temp table (you only need the two columns) easily and quickly, you can just INNER JOIN your way there. If not, you'll have to use @Quassnoi version.

青朷 2022-06-07 1 楼
SELECT  *
FROM    foo
WHERE   (column1, column2) IN (('foo', 1), ('bar', 2))

This syntax may be confusing, and it may be more readable to replace it with:

SELECT  *
FROM    foo
WHERE   ROW(column1, column2) IN (ROW('foo', 1), ROW('bar', 2))

I'm used to the former one, though :)