SQL - 如何检查项目是否在记录的列表中

Question

我有一个名为 MyRoles 的列，其中包含存储在名为 UserRoles 的字段中的项目(整数)列表。我想编写一个查询来检查特定项目是否在列表中。该列表将如下所示:“1,2,3”

我不能使用 WHERE MyRoles

查询应该是什么样的？

这与我的想法相似:

SELECT *
FROM MyTable
WHERE MyRoles CONTAINS ('1')

没有答案是容易实现的，而且会让我在丑陋的道路上走得更远这一事实清楚地表明，规范化数据库始终是最好的选择。

Answer 1

将其转换为数组:

SELECT *
FROM MyTable
WHERE ('{' || MyRoles || '}')::int[] && array[1]

更好的是，您可以在上述困惑中使用索引。在构建数组时，将文本直接转换为数组类型将被拒绝，但您可以解决它:

create function role_scan(text) returns int[] as $$
  select ('{' || $1 || '}')::int[];
$$ language sql immutable strict;

create index on MyTable using gin (role_scan(MyRoles));

-- actually use the index
SELECT *
FROM MyTable
WHERE role_scan(MyRoles) && array[1]

添加索引时有一个警告，您应该注意。统计信息收集器不会查看(无论如何都高达 9.1)到实际的数组值中。重叠运算符的选择性(1/200，即非常有选择性)是针对所有意图和目的进行硬编码的。因此，如果您查询非常常见的值，您可能会在不合适的地方进行索引扫描。一种解决方法是当您知道很多角色适用时直接调用底层重叠方法(它提供 1/3 的选择性并且没有潜在的索引扫描):

SELECT *
FROM MyTable
WHERE arrayoverlap(role_scan(MyRoles), array[1])