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

admin

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

sql

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

我无法使用WHERE MyRoles

查询应该是什么样？

这类似于我的想法：

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

没有答案很容易实现，会使我走得更糟，这一事实表明，归一化数据库始终是最好的选择。

阅读 163

2021-06-07

共1个答案

admin

将其转换为数组：

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])

2021-06-07