位函数与运算符
TiDB 支持所有在 MySQL 8.0 中可用的 bit functions and operators。
位函数与运算符:
名称 | 描述 |
---|---|
BIT_COUNT() | 返回在 expr 中设置为 1 的位的数量 |
& | 位与(Bitwise AND) |
~ | 位取反(Bitwise inversion) |
| | 位或(Bitwise OR) |
^ | 位异或(Bitwise XOR) |
<< | 左移(Left shift) |
>> | 右移(Right shift) |
BIT_COUNT()
BIT_COUNT(expr)
函数返回 expr
中设置为 1 的位的数量。
SELECT BIT_COUNT(b'00101001');
+------------------------+
| BIT_COUNT(b'00101001') |
+------------------------+
| 3 |
+------------------------+
1 行结果,耗时 0.00 秒
以下示例与前一个类似,但使用十六进制字面量代替位字面量作为参数。CONV()
函数将 0x29
从十六进制(基数 16)转换为二进制(基数 2),显示其二进制等价为 00101001
。
SELECT BIT_COUNT(0x29), CONV(0x29,16,2);
+-----------------+-----------------+
| BIT_COUNT(0x29) | CONV(0x29,16,2) |
+-----------------+-----------------+
| 3 | 101001 |
+-----------------+-----------------+
1 行结果,耗时 0.01 秒
BIT_COUNT(expr)
函数的一个实际应用是将子网掩码转换为 CIDR 表示法。在以下示例中,子网掩码 255.255.255.0
被转换为其 CIDR 表示 24
。
SELECT BIT_COUNT(INET_ATON('255.255.255.0'));
+---------------------------------------+
| BIT_COUNT(INET_ATON('255.255.255.0')) |
+---------------------------------------+
| 24 |
+---------------------------------------+
1 行结果,耗时 0.00 秒
&
(位与)
&
运算符执行位与操作。它比较两个数字的对应位:如果两个对应位都是 1,则结果的对应位为 1;否则为 0。
例如,1010
和 1100
进行位与操作后返回 1000
,因为只有最左边的位在两个数字中都为 1。
1010
& 1100
----
1000
在 SQL 中,可以如下使用 &
运算符:
SELECT CONV(b'1010' & b'1000',10,2);
+------------------------------+
| CONV(b'1010' & b'1000',10,2) |
+------------------------------+
| 1000 |
+------------------------------+
1 行结果,耗时 0.00 秒
你可以结合 INET_NTOA()
和 INET_ATON()
函数,将 IP 地址与网络掩码进行位与操作,以获取网络地址。这在判断多个 IP 是否属于同一网络时非常有用。
在以下两个示例中,IP 地址 192.168.1.1
和 192.168.1.2
在掩码 255.255.255.0
下属于同一网络 192.168.1.0/24
。
SELECT INET_NTOA(INET_ATON('192.168.1.1') & INET_ATON('255.255.255.0'));
+------------------------------------------------------------------+
| INET_NTOA(INET_ATON('192.168.1.1') & INET_ATON('255.255.255.0')) |
+------------------------------------------------------------------+
| 192.168.1.0 |
+------------------------------------------------------------------+
1 行结果,耗时 0.00 秒
SELECT INET_NTOA(INET_ATON('192.168.1.2') & INET_ATON('255.255.255.0'));
+------------------------------------------------------------------+
| INET_NTOA(INET_ATON('192.168.1.2') & INET_ATON('255.255.255.0')) |
+------------------------------------------------------------------+
| 192.168.1.0 |
+------------------------------------------------------------------+
1 行结果,耗时 0.00 秒
~
(位取反)
~
运算符对给定值执行位取反(或位非)操作。它反转每一位:0 变为 1,1 变为 0。
在操作前,值会被扩展到 64 位。
以二进制数 1111000011110000
为例。当扩展到 64 位并取反后,表现如下:
原始(16 位): 1111000011110000
扩展并取反(64 位): 1111111111111111111111111111111111111111111111110000111100001111
在 SQL 中,可以如下使用 ~
运算符:
SELECT CONV(~ b'1111000011110000',10,2);
+------------------------------------------------------------------+
| CONV(~ b'1111000011110000',10,2) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111110000111100001111 |
+------------------------------------------------------------------+
1 行结果,耗时 0.00 秒
你也可以通过再次应用 ~
运算符,将取反的结果还原:
SELECT CONV(~ b'1111111111111111111111111111111111111111111111110000111100001111',10,2);
+----------------------------------------------------------------------------------+
| CONV(~ b'1111111111111111111111111111111111111111111111110000111100001111',10,2) |
+----------------------------------------------------------------------------------+
| 1111000011110000 |
+----------------------------------------------------------------------------------+
1 行结果,耗时 0.00 秒
|
(位或)
|
运算符执行位或操作。它比较两个数字的对应位:如果至少有一位为 1,则结果的对应位为 1。
例如,1010
和 1100
进行位或后返回 1110
,因为前面三位中至少有一位为 1。
1010
| 1100
----
1110
在 SQL 中,可以如下使用 |
运算符:
SELECT CONV(b'1010' | b'1100',10,2);
+------------------------------+
| CONV(b'1010' | b'1100',10,2) |
+------------------------------+
| 1110 |
+------------------------------+
1 行结果,耗时 0.00 秒
^
(位异或)
^
运算符执行位异或操作。它比较两个数字的对应位:如果对应位不同,则结果的对应位为 1。
例如,1010
和 1100
进行位异或后返回 0110
,因为第二和第三位不同。
1010
^ 1100
----
0110
在 SQL 中,可以如下使用 ^
运算符:
SELECT CONV(b'1010' ^ b'1100',10,2);
+------------------------------+
| CONV(b'1010' ^ b'1100',10,2) |
+------------------------------+
| 110 |
+------------------------------+
1 行结果,耗时 0.00 秒
注意,结果显示为 110
而非 0110
,因为前导零被省略。
<<
(左移)
<<
运算符执行左移操作,即将数字的位向左移动指定的位数,空出的位用零填充。
例如,以下语句使用 1<<n
将二进制值 1
向左移动 n
位:
WITH RECURSIVE cte(n) AS (
SELECT 0 AS n
UNION ALL
SELECT 1+n FROM cte WHERE n<10
)
SELECT n,1<<n,LPAD(CONV(1<<n,10,2),11,0) FROM cte;
+------+------+----------------------------+
| n | 1<<n | LPAD(CONV(1<<n,10,2),11,0) |
+------+------+----------------------------+
| 0 | 1 | 00000000001 |
| 1 | 2 | 00000000010 |
| 2 | 4 | 00000000100 |
| 3 | 8 | 00000001000 |
| 4 | 16 | 00000010000 |
| 5 | 32 | 00000100000 |
| 6 | 64 | 00001000000 |
| 7 | 128 | 00010000000 |
| 8 | 256 | 00100000000 |
| 9 | 512 | 01000000000 |
| 10 | 1024 | 10000000000 |
+------+------+----------------------------+
11 行结果,耗时 0.00 秒
>>
(右移)
>>
运算符执行右移操作,即将数字的位向右移动指定的位数,空出的位用零填充。
例如,以下语句使用 1024>>n
将值 1024
(二进制为 10000000000
)向右移动 n
位:
WITH RECURSIVE cte(n) AS (
SELECT 0 AS n
UNION ALL
SELECT n+1 FROM cte WHERE n<11
)
SELECT n,1024>>n,LPAD(CONV(1024>>n,10,2),11,0) FROM cte;
+------+---------+-------------------------------+
| n | 1024>>n | LPAD(CONV(1024>>n,10,2),11,0) |
+------+---------+-------------------------------+
| 0 | 1024 | 10000000000 |
| 1 | 512 | 01000000000 |
| 2 | 256 | 00100000000 |
| 3 | 128 | 00010000000 |
| 4 | 64 | 00001000000 |
| 5 | 32 | 00000100000 |
| 6 | 16 | 00000010000 |
| 7 | 8 | 00000001000 |
| 8 | 4 | 00000000100 |
| 9 | 2 | 00000000010 |
| 10 | 1 | 00000000001 |
| 11 | 0 | 00000000000 |
+------+---------+-------------------------------+
12 行结果,耗时 0.00 秒
>>
运算符也可以用来提取较大数字的特定部分,例如从 TiDB TSO 时间戳中提取 UNIX 时间戳。
MySQL 兼容性
MySQL 8.0 与早期版本在处理位函数和运算符方面存在一些差异。TiDB 旨在遵循 MySQL 8.0 的行为。
已知问题
在以下情况下,TiDB 的查询结果与 MySQL 5.7 相同,但与 MySQL 8.0 不同。