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

Question

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

我不能使用WHERE MyRoles

查询应该是什么样子？

这与我的想法类似：

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

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

Answer 1

您可以使用LIKE:

SELECT *
FROM MyTable
WHERE MyRoles LIKE ('%1%')

这可能会非常糟糕（因为索引对于这样的搜索来说是相当无用的）。当然，即使查询中不存在1，也会在10上匹配。您可以扩展方法：

SELECT *
FROM MyTable
WHERE MyRoles = '1'
  OR MyRoles LIKE '1,%'
  OR MyRoles LIKE '%,1,%'

一个更好的解决方案是规范化数据库，而不是多值字段。使用每行具有单个角色ID和项目ID的多对多表。这更容易查询。

有些数据库会为这样的查询提供更好的设施，但这些将是扩展，而不是标准的SQL——您没有提到特定的RDBMS。

Answer 2

如果使用LIKE：，请小心

如果MyRoles是2,11，那么它将匹配LIKE('%1%')，尽管您不希望它匹配。

一个痛苦的解决方法是使用

SELECT *
FROM MyTable
WHERE MyRoles LIKE ('%,1,%')

但是，您需要在每个MyRoles条目中添加前导和尾随逗号。

这些丑陋的事实是每个人都告诉你改变数据库设计并创建"角色"表的原因。

Answer 3

将其转换为数组：

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]

添加索引时有一个警告，您应该注意这一点。stats收集器不会查找（无论如何，最高可达9.1）实际的数组值。重叠算子的选择性（1/200，即非常选择性）对于所有意图和目的都是硬编码的。因此，如果您针对非常常见的值进行查询，您可能会在不合适的地方进行索引扫描。一种解决方法是，当你知道有很多角色适用时，直接调用底层的重叠方法（这提供了1/3的选择性，并且没有潜在的索引扫描）：

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

Answer 4

SELECT *
FROM MyTable
WHERE FIND_IN_SET(1, MyRoles)

编辑：它在mysql数据库服务器中工作。

编辑：

用于postgres:的find_in_set function

create function find_in_set(n int, s text) returns bigint as
$$
select z.row_number
from
(
    select row_number() over(), y.x
    from (select unnest(('{' || $2 || '}')::int[]) as x) as y
) as z
where z.x = $1
$$ language sql;

Answer 5

也许正则表达式可以在这里提供帮助：

SELECT *
FROM MyTable
WHERE MyRoles ~ ('^(.*,)*' || 1 || '(,.*)*$')