MySQL的函数和运算符 - 位函数和运算符(1)

林员外聊编程 2021-07-20

1368

位函数和运算符

名称	介绍
&	位 AND
>>	右移
<<	左移
^	位 XOR
BIT_COUNT()	返回二进制表示中已设置为1的位数
\|	位 OR
~	按位取反

位函数和操作符包括 BIT_COUNT()、BIT_AND()、BIT_OR()、BIT_XOR()、&、|、^、~、<< 和 >>。在 MySQL 8.0 之前，位函数和操作符需要 BIGINT(64位整数)参数并返回 BIGINT 值，所以它们的最大范围是64位。非 BIGINT 参数在执行操作之前被转换为 BIGINT，可能会发生截断。

在 MySQL 8.0 中，位函数和操作符允许二进制字符串类型参数(BINARY, VARBINARY 和 BLOB 类型)并返回类似类型的值，这使它们能够接受的参数和产生的返回值能大于64位。非二进制字符串参数被转换为 BIGINT 并像前面介绍的一样进行处理。

这种行为变化的一个含义是，二进制字符串参数的位操作在 MySQL 8.0 中产生的结果可能与 5.7 中不同。

MySQL 8.0 之前的位运算

MySQL 8.0 之前的位运算只处理无符号64位整型参数和结果值(即无符号 BIGINT 值)。必要时将其他类型的参数转换为 BIGINT。示例：

● 该语句处理的是作为无符号64位整数处理的数字字面量：

mysql> SELECT 127 | 128, 128 << 2, BIT_COUNT(15);
+-----------+----------+---------------+
| 127 | 128 | 128 << 2 | BIT_COUNT(15) |
+-----------+----------+---------------+
|       255 |      512 |             4 |
+-----------+----------+---------------+

● 在执行与第一个语句相同的操作并产生相同的结果之前，该语句对字符串参数('127' 到 127，等等)执行到数字的转换：

mysql> SELECT '127' | '128', '128' << 2, BIT_COUNT('15');
+---------------+------------+-----------------+
| '127' | '128' | '128' << 2 | BIT_COUNT('15') |
+---------------+------------+-----------------+
|           255 |        512 |               4 |
+---------------+------------+-----------------+

● 该语句使用十六进制字面量作为位运算参数。MySQL 默认将十六进制文字视为二进制字符串，但在数值上下文中将其计算为数字。在 MySQL 8.0 之前，数字上下文包含位运算。示例：

mysql> SELECT X'7F' | X'80', X'80' << 2, BIT_COUNT(X'0F');
+---------------+------------+------------------+
| X'7F' | X'80' | X'80' << 2 | BIT_COUNT(X'0F') |
+---------------+------------+------------------+
|           255 |        512 |                4 |
+---------------+------------+------------------+

位运算中的位值字面量的处理类似于十六进制字面量(即数字)。

MySQL 8.0 中的位运算

MySQL 8.0 扩展了位运算，直接处理二进制字符串参数(不进行转换)并产生二进制字符串结果。(与前面一样，非整数或二进制字符串的参数仍然会被转换为整数。)这个扩展增强了以下方式的位运算:

● 对大于64位的值进行位运算成为可能。

● 对更自然地表示为二进制字符串而不是整数的值执行位运算更容易。

例如，考虑 UUID 值和 IPv6 地址，它们具有如下人类可读的文本格式：

UUID: 6ccd780c-baba-1026-9564-5b8c656024db
IPv6: fe80::219:d1ff:fe91:1a72

在这些格式中操作文本字符串是很麻烦的。一种方法是将它们转换为不带分隔符的固定长度的二进制字符串。UUID_TO_BIN() 和 INET6_ATON() 各自产生一个数据类型 BINARY(16) 的值，一个16字节(128位)长的二进制字符串。下面的语句说明了这一点(HEX() 被用来产生可显示的值)：

mysql> SELECT HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db'));
+----------------------------------------------------------+
| HEX(UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db')) |
+----------------------------------------------------------+
| 6CCD780CBABA102695645B8C656024DB                         |
+----------------------------------------------------------+
mysql> SELECT HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72'));
+---------------------------------------------+
| HEX(INET6_ATON('fe80::219:d1ff:fe91:1a72')) |
+---------------------------------------------+
| FE800000000000000219D1FFFE911A72            |
+---------------------------------------------+

这些二进制值很容易通过位运算进行操作，例如从 UUID 值中提取时间戳，或提取 IPv6 地址的网络和主机部分。

作为二进制字符串的参数包括列值、例程参数、局部变量和用户定义的具有二进制字符串类型的变量：BINARY、VARBINARY 或 BLOB 类型之一。

十六进制字面量和位字面量呢？回想一下，他们在 MySQL 中默认的是二进制字符串，在数字上下文中是数字。在 MySQL 8.0 中，它们是如何处理位操作的？MySQL 是否继续在数值上下文中评估它们，就像在 MySQL 8.0 之前所做的那样？或者是位运算以二进制字符串的形式计算它们，现在二进制字符串可以进行“原生”处理，而不需要转换?

答：使用十六进制字面量或位字面量来指定参数来表示数字已经很常见，所以当所有的位参数都是十六进制或位字面量时，为了向后兼容性，MySQL 继续在数字上下文中计算位操作。如果要求以二进制字符串的形式求值，这很容易完成：对至少一个字面量使用 _binary 导入符。

● 位运算将十六进制字面值和位字面值计算为整数：

mysql> SELECT X'40' | X'01', b'11110001' & b'01001111';
+---------------+---------------------------+
| X'40' | X'01' | b'11110001' & b'01001111' |
+---------------+---------------------------+
|            65 |                        65 |
+---------------+---------------------------+

● 位运算以二进制字符串的形式计算十六进制字面值和位字面值，因为引入了 _binary：

mysql> SELECT _binary X'40' | X'01', b'11110001' & _binary b'01001111';
+-----------------------+-----------------------------------+
| _binary X'40' | X'01' | b'11110001' & _binary b'01001111' |
+-----------------------+-----------------------------------+
| A                     | A                                 |
+-----------------------+-----------------------------------+

尽管两个语句中的位操作产生的结果都是数值 65，但第二个语句在二进制字符串上下文中操作，其中65是ASCII A。

在数值计算上下文中，十六进制字面值和位字面值参数允许的值最多为64位，结果也是如此。相反，在二进制字符串求值上下文中，允许的参数(和结果)可以超过64位：

mysql> SELECT _binary X'4040404040404040' | X'0102030405060708';
+---------------------------------------------------+
| _binary X'4040404040404040' | X'0102030405060708' |
+---------------------------------------------------+
| ABCDEFGH                                          |
+---------------------------------------------------+

有几种方法可以在位操作中引用十六进制字面值或位字面值来进行二进制字符串计算：

_binary literal
BINARY literal
CAST(literal AS BINARY)

十六进制字面值或位字面值进行二进制字符串计算的另一种方法是将它们赋值给用户定义的变量，这将导致变量具有二进制字符串类型：

mysql> SET @v1 = X'40', @v2 = X'01', @v3 = b'11110001', @v4 = b'01001111';
mysql> SELECT @v1 | @v2, @v3 & @v4;
+-----------+-----------+
| @v1 | @v2 | @v3 & @v4 |
+-----------+-----------+
| A         | A         |
+-----------+-----------+

在二进制字符串上下文中，按位操作参数必须具有相同的长度，否则会出现 ER_INVALID_BITWISE_OPERANDS_SIZE 错误：

mysql> SELECT _binary X'40' | X'0001';
ERROR 3513 (HY000): Binary operands of bitwise
operators must be of equal length

为了满足等长要求，用前导零填充较短的值，或者，如果较长的值以前导零开头，并且可以接受较短的结果值，则去掉前导零：

mysql> SELECT _binary X'0040' | X'0001';
+---------------------------+
| _binary X'0040' | X'0001' |
+---------------------------+
|  A                        |
+---------------------------+
mysql> SELECT _binary X'40' | X'01';
+-----------------------+
| _binary X'40' | X'01' |
+-----------------------+
| A                     |
+-----------------------+

填充或剥离也可以使用 LPAD()、RPAD()、SUBSTR() 或 CAST() 等函数来完成。在这种情况下，表达式参数不再都是字面量，_binary 变得不必要了。示例：

mysql> SELECT LPAD(X'40', 2, X'00') | X'0001';
+---------------------------------+
| LPAD(X'40', 2, X'00') | X'0001' |
+---------------------------------+
|  A                              |
+---------------------------------+
mysql> SELECT X'40' | SUBSTR(X'0001', 2, 1);
+-------------------------------+
| X'40' | SUBSTR(X'0001', 2, 1) |
+-------------------------------+
| A                             |
+-------------------------------+

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 位函数和运算符(1)

评论