使用多列的主键选择行

Question

如何根据所提供的配对列表选择所有相关记录？

表：

CREATE TABLE "users_groups" (
    "user_id" INTEGER NOT NULL,
    "group_id" BIGINT NOT NULL,
    PRIMARY KEY (user_id, group_id),
    "permissions" VARCHAR(255)
);

例如，如果我有下面的对的JavaScript array，那么我应该从DB获得

[
  {user_id: 1, group_id: 19},
  {user_id: 1, group_id: 11},
  {user_id: 5, group_id: 19}
]

在这里，我们看到相同的user_id可以在多个组中。

我可以通过每个数组元素传递for-loop，并创建以下查询

SELECT * FROM users_groups
    WHERE (user_id = 1 AND group_id = 19) 
    OR (user_id = 1 AND group_id = 11) 
    OR (user_id = 5 AND group_id = 19);

但这是最好的解决办法吗？假设数组很长。如我所知，查询长度可以得到~1GB。

做这件事的最好和快速的解决方案是什么？

Answer 1

比尔·卡温的回答也同样适用于波斯特格斯。

但是，我的经验是，与VALUES子句的连接通常比大型IN list更快(包含数百个元素(如果不是数千个元素)：

select ug.*
from user_groups ug
  join ( 
     values (1,19), (1,11), (5,19), ... 
  ) as l(uid, guid) on l.uid = ug.user_id and l.guid = ug.group_id;

这假设在所提供的值中没有重复项，否则联接将导致重复行，而in解决方案则不会这样做。

Answer 2

您标记了mysql和postgresql，所以我不知道您真正使用的是哪个SQL数据库。

MySQL至少支持元组比较：

SELECT * FROM users_groups WHERE (user_id, group_id) IN ((1,19), (1,11), (5,19), ...)

这类谓词可以在MySQL 5.7及更高版本中进行优化。请参阅https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-optimization

我不知道PostgreSQL是否支持这种类型的谓词，或者它是否对其进行了优化。