SQL-如何检查项目是否在记录列表中 [英] SQL - How to check if item is in a list in a record

问题描述

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

I have have a column name MyRoles that contains a list of items (integers) stored in a field named UserRoles. I want to write a query that checks to see if a specific item is in the list. The list will look like this: "1,2,3"

我不能使用WHERE MyRoles

I can't use WHERE MyRoles

查询应该是什么样?

这类似于我的想法:

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

没有答案很容易实现，这会让我走得更糟，这一事实表明，标准化数据库永远是最好的选择.

The fact that no answer was easy to implement and would take me farther down an ugly road really makes clear that a normalized database is always the best bet.

推荐答案

将其转换为数组:

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

更好的是，您可以在上述混乱中使用索引.构建数组时，将文本直接转换为数组类型将被拒绝，但是您可以解决该问题:

Better yet, you can use an index on the above mess. Casting text to an array type outright will get rejected when building an array, but you can work around it:

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，并且没有潜在的索引扫描):

There is a caveat in adding the index, that you should be aware of. The stats collector doesn't look (up to 9.1 anyway) into the actual array values. The selectivity of the overlap operator (1/200, i.e. very selective) is hard-coded for all intents and purposes. So if you're querying against very common values, you might get an index scan where inappropriate. One workaround is to call the underlying overlap method directly (which gives a selectivity of 1/3 and no potential index scan) when you know plenty of roles apply:

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

这篇关于SQL-如何检查项目是否在记录列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！