MySQL获取两个不同列中的键值对 [英] MySQL fetching a key value pair which are in two different columns

问题描述

表1:具有3列，分别是ID，键&值.

Table1 : Has 3 columns say id, key & value.

+----+-----+-------+
| ID | KEY | VALUE |
+----+-----+-------+
| 1  | 1   | 1     |
| 2  | 1   | 2     |
| 3  | 2   | 1     |
| 4  | 2   | 2     |
| 5  | 3   | 1     |
| 6  | 3   | 2     |
| 7  | 4   | 1     |
| 8  | 4   | 2     |
| 9  | 5   | 1     |
| 10 | 5   | 2     |
+----+-----+-------+

此表的键和值都可以重复，但是两者的组合是唯一的.

This table key and value both can be repeated but the combination of both is unique.

如果我想基于键值对和id上的否查询多行.我该怎么办?

If I want to query for multiple rows based on the key value pair and NOT on id. How can I do that ?

注意:我知道我可以做到

SELECT * from Table1 
WHERE (key=1 AND value=2) OR 
      (key=1 AND value=1) OR 
      (key=5 AND value=1);

我希望可以做得更好，更简洁，因为我需要查询大约40K的键值对.

I was hope to something much better and concise as I have approximately 40K key value pairs which I need to query for.

推荐答案

除了已经在做的事情之外，没有其他合适的方法.您应该比较唯一列(在您的情况下为ID).是的，您可以使用IN运算符来比较行构造函数，将其缩短

There is no other proper way than what you are already doing. You should rather compare on the unique column, which in you case is ID. yes you can shorten it using a IN operator to compare row constructors saying

SELECT * from Table1 
WHERE (`key`, `value`) IN ((1,2),(1,1),(5,1));

有关更多信息，请参见 MySQL文档一样.

See MySQL Documentation for more information on the same.

这篇关于MySQL获取两个不同列中的键值对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！