MySQL 8.4 直接处理二进制字符串参数（无需转换）并生成二进制字符串结果。非整数或二进制字符串的参数会被转换为整数。MySQL 8.4 handles binary string arguments directly (without conversion) and produces binary string results. Arguments that are not integers or binary strings are converted to integers.

作为二进制字符串的参数包括列值、例程参数、本地变量以及具有二进制字符串类型的用户定义变量：BINARY、VARBINARY，或 BLOB 类型之一。Arguments that count as binary strings include column values, routine parameters, local variables, and user-defined variables that have a binary string type: BINARY, VARBINARY, or one of the BLOB types.

您可以使用十六进制字面量或意图表示数字的位字面量来指定位运算的参数；当所有位参数都是十六进制或位字面量时，MySQL 会在数值上下文中评估位运算。若想以二进制字符串而非数值进行评估，请至少对其中一个字面量使用 _binary 引导符。You can specify arguments to bit operations using hexadecimal literals or bit literals with the intent that they represent numbers; MySQL evaluates bit operations in numeric context when all bit arguments are hexadecimal or bit literals. For evaluation as binary strings instead, use the _binary introducer for at least one of the literal values.

尽管两条语句中的位运算产生的结果数值都是 65，但第二条语句在二进制字符串上下文中操作，对于该上下文，65 对应的是 ASCII 字符 A。Although the bit operations in both statements produce a result with a numeric value of 65, the second statement operates in binary-string context, for which 65 is ASCII A.

在数值求值环境中，十六进制字面量和位字面量参数的允许值最多为 64 位，结果也是如此。相比之下，在二进制字符串求值环境中，允许的参数（和结果）可以超过 64 位：In numeric evaluation context, permitted values of hexadecimal literal and bit literal arguments have a maximum of 64 bits, as do results. By contrast, in binary-string evaluation context, permitted arguments (and results) can exceed 64 bits:

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

有几种方式可以在位操作中引用十六进制字面量或位字面量以引起二进制字符串求值：There are several ways to refer to a hexadecimal literal or bit literal in a bit operation to cause binary-string evaluation:

_binary literal
BINARY literal
CAST(literal AS BINARY)

另一种产生十六进制字面量或位字面量二进制字符串求值的方法是将它们赋值给用户定义的变量，这会导致变量具有二进制字符串类型：Another way to produce binary-string evaluation of hexadecimal literals or bit literals is to assign them to user-defined variables, which results in variables that have a binary string type:

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错误：In binary-string context, bitwise operation arguments must have the same length or an ER_INVALID_BITWISE_OPERANDS_SIZE error occurs:

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

为了满足等长的要求，可以用前导零填充较短的值，或者如果较长的值以前导零开始且接受较短的结果值，则去掉它们：To satisfy the equal-length requirement, pad the shorter value with leading zero digits or, if the longer value begins with leading zero digits and a shorter result value is acceptable, strip them:

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 也不再必要。示例：Padding or stripping can also be accomplished using functions such as LPAD(), RPAD(), SUBSTR(), or CAST(). In such cases, the expression arguments are no longer all literals and _binary becomes unnecessary. Examples:

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                             |
+-------------------------------+

另请参见 十六进制字面值、位字面值和 NULL 字面值的特殊处理。See also Special Handling of Hexadecimal Literals, Bit Literals, and NULL Literals.

下面的示例说明了如何使用位操作提取 UUID 值的部分内容，在此例中为时间戳和 IEEE 802 节点编号。该技术需要为每个提取的部分准备位掩码。The following example illustrates use of bit operations to extract parts of a UUID value, in this case, the timestamp and IEEE 802 node number. This technique requires bitmasks for each extracted part.

将文本 UUID 转换为相应的 16 字节二进制值，以便可以在二进制字符串上下文中使用位操作进行处理：Convert the text UUID to the corresponding 16-byte binary value so that it can be manipulated using bit operations in binary-string context:

mysql> SET @uuid = UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db');
mysql> SELECT HEX(@uuid);
+----------------------------------+
| HEX(@uuid)                       |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+

为值的时间戳和节点编号部分构建位掩码。时间戳包含前三个部分（64 位，位 0 到 63），节点编号是最后一部分（48 位，位 80 到 127）：Construct bitmasks for the timestamp and node number parts of the value. The timestamp comprises the first three parts (64 bits, bits 0 to 63) and the node number is the last part (48 bits, bits 80 to 127):

mysql> SET @ts_mask = CAST(X'FFFFFFFFFFFFFFFF' AS BINARY(16));
mysql> SET @node_mask = CAST(X'FFFFFFFFFFFF' AS BINARY(16)) >> 80;
mysql> SELECT HEX(@ts_mask);
+----------------------------------+
| HEX(@ts_mask)                    |
+----------------------------------+
| FFFFFFFFFFFFFFFF0000000000000000 |
+----------------------------------+
mysql> SELECT HEX(@node_mask);
+----------------------------------+
| HEX(@node_mask)                  |
+----------------------------------+
| 00000000000000000000FFFFFFFFFFFF |
+----------------------------------+

这里使用 CAST(... AS BINARY(16)) 函数是因为掩码必须与其应用的 UUID 值长度相同。可以使用其他函数将掩码填充到所需长度以获得相同的结果：The CAST(... AS BINARY(16)) function is used here because the masks must be the same length as the UUID value against which they are applied. The same result can be produced using other functions to pad the masks to the required length:

SET @ts_mask= RPAD(X'FFFFFFFFFFFFFFFF' , 16, X'00');
SET @node_mask = LPAD(X'FFFFFFFFFFFF', 16, X'00') ;

使用掩码来提取时间戳和节点号部分：Use the masks to extract the timestamp and node number parts:

mysql> SELECT HEX(@uuid & @ts_mask) AS 'timestamp part';
+----------------------------------+
| timestamp part                   |
+----------------------------------+
| 6CCD780CBABA10260000000000000000 |
+----------------------------------+
mysql> SELECT HEX(@uuid & @node_mask) AS 'node part';
+----------------------------------+
| node part                        |
+----------------------------------+
| 000000000000000000005B8C656024DB |
+----------------------------------+

前面的示例使用了这些位操作：右移 (>>) 和按位与 (&)。The preceding example uses these bit operations: right shift (>>) and bitwise AND (&).

下一个示例使用位操作来提取 IPv6 地址的网络部分和主机部分。假设网络部分的长度为 80 位。那么主机部分的长度为 128 − 80 = 48 位。要提取地址的网络和主机部分，请将其转换为二进制字符串，然后在二进制字符串上下文中使用位操作。The next example uses bit operations to extract the network and host parts of an IPv6 address. Suppose that the network part has a length of 80 bits. Then the host part has a length of 128 − 80 = 48 bits. To extract the network and host parts of the address, convert it to a binary string, then use bit operations in binary-string context.

将文本 IPv6 地址转换为相应的二进制字符串：Convert the text IPv6 address to the corresponding binary string:

mysql> SET @ip = INET6_ATON('fe80::219:d1ff:fe91:1a72');

以位为单位定义网络长度：Define the network length in bits:

mysql> SET @net_len = 80;

通过将全 1 地址左移或右移来构建网络掩码和主机掩码。为此，请从地址 :: 开始，它是全零的简写，可以通过将其转换为二进制字符串来看到这一点，如下所示：Construct network and host masks by shifting the all-ones address left or right. To do this, begin with the address ::, which is shorthand for all zeros, as you can see by converting it to a binary string like this:

mysql> SELECT HEX(INET6_ATON('::')) AS 'all zeros';
+----------------------------------+
| all zeros                        |
+----------------------------------+
| 00000000000000000000000000000000 |
+----------------------------------+

要生成补数值（全 1），请使用 ~ 运算符来反转位：To produce the complementary value (all ones), use the ~ operator to invert the bits:

mysql> SELECT HEX(~INET6_ATON('::')) AS 'all ones';
+----------------------------------+
| all ones                         |
+----------------------------------+
| FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF |
+----------------------------------+

将全为 1 的值向左或向右移动以生成网络掩码和主机掩码：Shift the all-ones value left or right to produce the network and host masks:

mysql> SET @net_mask = ~INET6_ATON('::') << (128 - @net_len);
mysql> SET @host_mask = ~INET6_ATON('::') >> @net_len;

显示掩码以验证它们覆盖了地址的正确部分：Display the masks to verify that they cover the correct parts of the address:

mysql> SELECT INET6_NTOA(@net_mask) AS 'network mask';
+----------------------------+
| network mask               |
+----------------------------+
| ffff:ffff:ffff:ffff:ffff:: |
+----------------------------+
mysql> SELECT INET6_NTOA(@host_mask) AS 'host mask';
+------------------------+
| host mask              |
+------------------------+
| ::ffff:255.255.255.255 |
+------------------------+

提取并显示地址的网络部分和主机部分：Extract and display the network and host parts of the address:

mysql> SET @net_part = @ip & @net_mask;
mysql> SET @host_part = @ip & @host_mask;
mysql> SELECT INET6_NTOA(@net_part) AS 'network part';
+-----------------+
| network part    |
+-----------------+
| fe80::219:0:0:0 |
+-----------------+
mysql> SELECT INET6_NTOA(@host_part) AS 'host part';
+------------------+
| host part        |
+------------------+
| ::d1ff:fe91:1a72 |
+------------------+

前面的示例使用了这些位运算：补码（~）、左移（<<）和按位与（&）。The preceding example uses these bit operations: Complement (~), left shift (<<), and bitwise AND (&).

接下来的讨论提供了每组位操作的参数处理的详细信息，以及有关位操作中字面值处理的更多信息。The remaining discussion provides details on argument handling for each group of bit operations, and more information about literal-value handling in bit operations.

对于 &、| 和 ^ 按位操作，结果类型取决于参数是作为二进制字符串还是数字进行计算：For &, |, and ^ bit operations, the result type depends on whether the arguments are evaluated as binary strings or numbers:

数字评估的示例：Examples of numeric evaluation:

mysql> SELECT 64 | 1, X'40' | X'01';
+--------+---------------+
| 64 | 1 | X'40' | X'01' |
+--------+---------------+
|     65 |            65 |
+--------+---------------+

二进制字符串评估的示例：Examples of binary-string evaluation:

mysql> SELECT _binary X'40' | X'01';
+-----------------------+
| _binary X'40' | X'01' |
+-----------------------+
| A                     |
+-----------------------+
mysql> SET @var1 = X'40', @var2 = X'01';
mysql> SELECT @var1 | @var2;
+---------------+
| @var1 | @var2 |
+---------------+
| A             |
+---------------+

对于 ~、<< 和 >> 位操作，结果类型取决于位参数是作为二进制字符串还是数字进行求值：For ~, <<, and >> bit operations, the result type depends on whether the bit argument is evaluated as a binary string or number:

对于移位操作，被移出值末端的位会丢失而不会有任何警告，无论参数类型如何。特别是，如果移位计数大于或等于位参数中的位数，则结果中的所有位都是 0。For shift operations, bits shifted off the end of the value are lost without warning, regardless of the argument type. In particular, if the shift count is greater or equal to the number of bits in the bit argument, all bits in the result are 0.

数字评估的示例：Examples of numeric evaluation:

mysql> SELECT ~0, 64 << 2, X'40' << 2;
+----------------------+---------+------------+
| ~0                   | 64 << 2 | X'40' << 2 |
+----------------------+---------+------------+
| 18446744073709551615 |     256 |        256 |
+----------------------+---------+------------+

二进制字符串评估的示例：Examples of binary-string evaluation:

mysql> SELECT HEX(_binary X'1111000022220000' >> 16);
+----------------------------------------+
| HEX(_binary X'1111000022220000' >> 16) |
+----------------------------------------+
| 0000111100002222                       |
+----------------------------------------+
mysql> SELECT HEX(_binary X'1111000022220000' << 16);
+----------------------------------------+
| HEX(_binary X'1111000022220000' << 16) |
+----------------------------------------+
| 0000222200000000                       |
+----------------------------------------+
mysql> SET @var1 = X'F0F0F0F0';
mysql> SELECT HEX(~@var1);
+-------------+
| HEX(~@var1) |
+-------------+
| 0F0F0F0F    |
+-------------+

BIT_COUNT() 函数总是返回一个无符号 64 位整数，如果参数为 NULL，则返回 NULL。The BIT_COUNT() function always returns an unsigned 64-bit integer, or NULL if the argument is NULL.

mysql> SELECT BIT_COUNT(127);
+----------------+
| BIT_COUNT(127) |
+----------------+
|              7 |
+----------------+
mysql> SELECT BIT_COUNT(b'010101'), BIT_COUNT(_binary b'010101');
+----------------------+------------------------------+
| BIT_COUNT(b'010101') | BIT_COUNT(_binary b'010101') |
+----------------------+------------------------------+
|                    3 |                            3 |
+----------------------+------------------------------+

对于 BIT_AND()、BIT_OR() 和 BIT_XOR() 位函数，结果类型取决于函数参数值是作为二进制字符串还是数字进行计算：For the BIT_AND(), BIT_OR(), and BIT_XOR() bit functions, the result type depends on whether the function argument values are evaluated as binary strings or numbers:

NULL 值不会影响结果，除非所有值都是 NULL。在这种情况下，结果是一个中性值，其长度与参数值的长度相同（对于 BIT_AND()，所有位都是 1，对于 BIT_OR()，所有位都是 0，对于 BIT_XOR()）。NULL values do not affect the result unless all values are NULL. In that case, the result is a neutral value having the same length as the length of the argument values (all bits 1 for BIT_AND(), all bits 0 for BIT_OR(), and BIT_XOR()).

示例：Example:

mysql> CREATE TABLE t (group_id INT, a VARBINARY(6));
mysql> INSERT INTO t VALUES (1, NULL);
mysql> INSERT INTO t VALUES (1, NULL);
mysql> INSERT INTO t VALUES (2, NULL);
mysql> INSERT INTO t VALUES (2, X'1234');
mysql> INSERT INTO t VALUES (2, X'FF34');
mysql> SELECT HEX(BIT_AND(a)), HEX(BIT_OR(a)), HEX(BIT_XOR(a))
       FROM t GROUP BY group_id;
+-----------------+----------------+-----------------+
| HEX(BIT_AND(a)) | HEX(BIT_OR(a)) | HEX(BIT_XOR(a)) |
+-----------------+----------------+-----------------+
| FFFFFFFFFFFF    | 000000000000   | 000000000000    |
| 1234            | FF34           | ED00            |
+-----------------+----------------+-----------------+

当所有位参数为十六进制字面值、位字面值或NULL字面值时，MySQL 8.4 会在数值上下文中评估位操作。也就是说，如果所有位参数都是普通的十六进制字面值、位字面值或NULL字面值，则针对二进制字符串位参数的位操作不会使用二进制字符串评估。（如果这些字面值使用_binary 引导符、BINARY 运算符或其他显式指定为二进制字符串的方式书写，则不适用此规则。）MySQL 8.4 evaluates bit operations in numeric context when all bit arguments are hexadecimal literals, bit literals, or NULL literals. That is, bit operations on binary-string bit arguments do not use binary-string evaluation if all bit arguments are unadorned hexadecimal literals, bit literals, or NULL literals. (This does not apply to such literals if they are written with a _binary introducer, BINARY operator, or other way of specifying them explicitly as binary strings.)

示例：Examples:

你可以通过明确表明至少有一个参数是二进制字符串来让这些操作在二进制字符串上下文中评估参数：You can cause those operations to evaluate the arguments in binary-string context by indicating explicitly that at least one argument is a binary string:

_binary b'0001' | b'0010'
_binary X'0008' << 8
BINARY NULL & NULL
BINARY NULL >> 4

最后两个表达式的结果是 NULL，就像没有 BINARY 运算符时一样，但结果的数据类型是二进制字符串类型，而不是整数类型。The result of the last two expressions is NULL, just as without the BINARY operator, but the data type of the result is a binary string type rather than an integer type.