在SQL中使用位字符串（而不是整数）进行匹配位掩码。

Question

我在这里找到了一个非常好的资源（比较SQL中的两个位掩码以查看是否匹配任何位），用于在SQL数据库中进行搜索，其中使用位掩码存储具有多个属性的数据。然而，在此示例中，所有数据都存储为整数，并且where子句似乎仅适用于整数。

是否有一种简单的方法将非常相似的测试用例转换为完整的位字符串？因此，例如：

with test (id, username, roles)
AS
(
    SELECT 1,'Dave',1
    UNION SELECT 2,'Charlie',3
    UNION SELECT 3,'Susan',5
    UNION SELECT 4,'Nick',2
)
select * from test where (roles & 7) != 0 

而不是像这样：

with test (id, username, roles)
AS
(
    SELECT 1,'Dave',B'001'
    UNION SELECT 2,'Charlie',B'011'
    UNION SELECT 3,'Susan',B'101'
    UNION SELECT 4,'Nick',B'110'
)
select * from test where (roles & B'001') != 0 

我可以进行转换，但用实际位串更容易可视化。对于我的简单转换（如上），我得到一个错误，即运算符不能用于位串。是否有另一种设置方式可以使其正常工作？

- ten

---

"SQL" 是结构化查询语言的通用术语。你使用的是哪种数据库管理系统？字符串和位运算符因数据库管理系统而异。 - N West

Answer 1

一种方法是在表达式的右侧也使用bit字符串:

WITH test (id, username, roles) AS (
   VALUES
     (1,'Dave',B'001')
    ,(2,'Charlie',B'011')
    ,(3,'Susan',B'101')
    ,(4,'Nick',B'110')
   )
SELECT *, (roles & B'001') AS intersection
FROM   test
WHERE  (roles & B'001') <> B'000';

或者您可以将 整数0 转换成 bit(3)

...
WHERE  (roles & B'001') <> 0::bit(3);

您可能对以下相关答案感兴趣，该答案展示了在boolean、bit string和integer之间进行转换的多种方法：
我可以将一组布尔列转换为PostgreSQL中的单个位图吗？ 请注意，将数据存储为integer可以节省一些空间。 integer需要4字节来保存最多32位的信息，而 - 我引用该位置的手册所说：

- Erwin Brandstetter

---

太棒了，我知道我漏掉了一些小东西。还有谢谢你的提示，虽然在这种情况下空间不是问题，但提高可读性可能更重要。 - ten