更新时间:2023-01-27 22:52:16
要排除按位AND(&
)返回由零组成的位串,但长度可能会改变( B'000 ...'
),则可以使用强制转换为整数
(最多 bit(32)
)或 bigint
(最多 bit(64)
):
To exclude cases where the bitwise AND (&
) returns a bitstring consisting of nothing but zeros, but the length might change (B'000...'
), you can use a cast to integer
(up to bit(32)
) or bigint
(up to bit(64)
):
SELECT u.name
FROM users u
JOIN features f ON (u.mask & f.mask)::int <> 0;
当转换为整数时,它们全部导致 0
。
这也排除其中任一列为 NULL
的情况。换句话说,结果必须包含至少一个 1
。
When cast to integer, all of them result in 0
.
This also excludes cases where either of the columns is NULL
. In other words, the result has to include at least one 1
.
如果您的值可以大于64位,则可以强制转换为 text
并使用正则表达式进行检查:
If your values can be longer than 64 bit, you could cast to text
and check with a regular expression:
ON (u.mask & f.mask)::text !~ '^0+$'
模式解释:
^
..字符串的开头 0 +
..一个或多个'0' $
..字符串结尾
^
.. beginning of string0+
.. one or more '0'$
.. end of string
或者,作为手册会通知:
以下SQL标准函数有效位字符串以及
字符串:length
,bit_length
,octet_length
,位置
,子字符串
,重叠
。
Ergo:
ON position('1' IN (u.mask & f.mask)) > 0