Bit operations prior to MySQL 8.0 handle only unsigned 64-bit integer argument and result values (that is, unsigned BIGINT values). MySQL 8.0之前的位操作只处理无符号64位整数参数和结果值（即无符号BIGINT值）。Conversion of arguments of other types to BIGINT occurs as necessary. 根据需要将其他类型的参数转换为BIGINT。Examples:例如：

MySQL 8.0 extends bit operations to handle binary string arguments directly (without conversion) and produce binary string results. MySQL 8.0扩展了位操作，直接处理二进制字符串参数（无需转换）并生成二进制字符串结果。(Arguments that are not integers or binary strings are still converted to integers, as before.) （与以前一样，非整数或二进制字符串的参数仍转换为整数。）This extension enhances bit operations in the following ways:此扩展通过以下方式增强位操作：

For example, consider UUID values and IPv6 addresses, which have human-readable text formats like this:例如，考虑UUID值和IPv6地址，它们具有如下人类可读的文本格式：

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

It is cumbersome to operate on text strings in those formats. 对这些格式的文本字符串进行操作很麻烦。An alternative is convert them to fixed-length binary strings without delimiters. 另一种方法是将它们转换为不带分隔符的固定长度二进制字符串。UUID_TO_BIN() and INET6_ATON() each produce a value of data type BINARY(16), a binary string 16 bytes (128 bits) long. The following statements illustrate this (HEX() is used to produce displayable values):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            |
+---------------------------------------------+

Those binary values are easily manipulable with bit operations to perform actions such as extracting the timestamp from UUID values, or extracting the network and host parts of IPv6 addresses. 这些二进制值很容易通过位操作进行操作，以执行诸如从UUID值提取时间戳，或者提取IPv6地址的网络和主机部分等操作。(For examples, see later in this discussion.)（有关示例，请参阅本讨论的后面部分。）

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.计为二进制字符串的参数包括列值、例程参数、局部变量和具有二进制字符串类型（BINARY、VARBINARY或BLOB类型之一）的用户定义变量。

What about hexadecimal literals and bit literals? 十六进制文字和位文字呢？Recall that those are binary strings by default in MySQL, but numbers in numeric context. 回想一下，在MySQL中，默认情况下这些是二进制字符串，但在数字上下文中是数字。How are they handled for bit operations in MySQL 8.0? 在MySQL 8.0中如何处理位操作？Does MySQL continue to evaluate them in numeric context, as is done prior to MySQL 8.0? Or do bit operations evaluate them as binary strings, now that binary strings can be handled “natively” without conversion?MySQL是否继续在数字环境中评估它们，就像MySQL8.0之前那样？或者位操作将它们作为二进制字符串进行评估，现在二进制字符串可以“本机”处理而无需转换？

Answer: It has been common to specify arguments to bit operations using hexadecimal literals or bit literals with the intent that they represent numbers, so MySQL continues to evaluate bit operations in numeric context when all bit arguments are hexadecimal or bit literals, for backward compatility. 答：通常使用十六进制文字或位文字来指定位操作的参数，目的是它们表示数字，因此MySQL继续在所有位参数都是十六进制或位文字的情况下评估数字上下文中的位操作，以实现向后兼容性。If you require evaluation as binary strings instead, that is easily accomplished: Use the _binary introducer for at least one literal.如果您要求将求值作为二进制字符串，那么这很容易实现：请至少对一个文本使用_binary引入器。

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.尽管这两个语句中的位操作都产生一个数值为65的结果，但第二个语句在二进制字符串上下文中操作，其中65是ASCII A。

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

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

In binary-string context, bitwise operation arguments must have the same length or an ER_INVALID_BITWISE_OPERANDS_SIZE error occurs:在二进制字符串上下文中，按位操作参数必须具有相同的长度，否则会出现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

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

Padding or stripping can also be accomplished using functions such as LPAD(), RPAD(), SUBSTR(), or CAST(). 填充或剥离也可以使用LPAD()、RPAD()、SUBSTR()或CAST()等函数来完成。In such cases, the expression arguments are no longer all literals and _binary becomes unnecessary. 在这种情况下，表达式参数不再都是文本，因此不需要_binary。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                             |
+-------------------------------+

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. 下面的示例说明如何使用位操作来提取UUID值的一部分，在本例中是时间戳和IEEE 802节点号。This technique requires bitmasks for each extracted part.这种技术需要为每个提取的部分设置位掩码。

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

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

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):时间戳包括前三部分（64位，0到63位），节点号是最后一部分（48位，80到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 |
+----------------------------------+

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. 这里使用CAST(... AS BINARY(16))函数是因为掩码的长度必须与应用它们的UUID值的长度相同。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 (&).前面的示例使用以下位操作：右移位（>>）和按位与（&）。

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

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

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

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

mysql> SET @net_len = 80;

Construct network and host masks by shifting the all-ones address left or right. 通过向左或向右移动all-one地址来构造网络和主机掩码。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 |
+----------------------------------+

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

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

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, more information about literal-value handling in bit operations, and potential incompatibilities between MySQL 8.0 and older MySQL versions.剩下的讨论提供了关于每组位操作的参数处理的详细信息，关于位操作中的文本值处理的更多信息，以及MySQL 8.0和旧版本MySQL之间的潜在不兼容性。

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:对于~、<<和>>位操作，结果类型取决于位参数是作为二进制字符串还是作为数字计算的：

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.特别是，如果移位计数大于或等于位参数中的位数，则结果中的所有位都是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    |
+-------------+

The BIT_COUNT() function always returns an unsigned 64-bit integer, or NULL if the argument is NULL.BIT_COUNT()函数的作用是：返回一个无符号的64位整数，如果参数为NULL，则返回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 |
+----------------------+------------------------------+

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:对于BIT_AND()、BIT_OR()和BIT_XOR()位函数，结果类型取决于函数参数值是作为二进制字符串还是数字计算的：

NULL values do not affect the result unless all values are NULL. 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()).在这种情况下，结果是一个中性值，其长度与参数值的长度相同（所有位1表示BIT_AND()，所有位0表示BIT_OR()，以及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            |
+-----------------+----------------+-----------------+

For backward compatibility, MySQL 8.0 evaluates bit operations in numeric context when all bit arguments are hexadecimal literals, bit literals, or NULL literals. 为了向后兼容，当所有位参数都是十六进制文字、位文字或NULL文字时，MySQL 8.0会在数值上下文中评估位操作。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. 也就是说，如果所有位参数都是未加修饰的十六进制文字、位文字或NULL文字，则对二进制字符串位参数的位操作不使用二进制字符串求值。(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.)（如果这些文字是用_binary引导器、BINARY运算符或其他显式指定为二进制字符串的方式编写的，则这不适用于这些文字。）

The literal handling just described is the same as prior to MySQL 8.0. 刚才描述的文本处理与MySQL 8.0之前的处理相同。Examples:例如：

In MySQL 8.0, 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:在MySQL 8.0中，您可以通过显式指示至少一个参数是二进制字符串，使这些操作在二进制字符串上下文中计算参数：

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

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.最后两个表达式的结果是NULL，就像没有BINARY运算符一样，但是结果的数据类型是二进制字符串类型而不是整数类型。

Because bit operations can handle binary string arguments natively in MySQL 8.0, some expressions produce a different result in MySQL 8.0 than in 5.7. 因为位操作可以在MySQL8.0中本机处理二进制字符串参数，所以有些表达式在MySQL8.0中产生的结果与在5.7中不同。The five problematic expression types to watch out for are:要注意的五种有问题的表达式类型是：

nonliteral_binary { & | ^ } binary
binary  { & | ^ } nonliteral_binary
nonliteral_binary { << >> } anything
~ nonliteral_binary
AGGR_BIT_FUNC(nonliteral_binary)

Those expressions return BIGINT in MySQL 5.7, binary string in 8.0.这些表达式在MySQL5.7中返回BIGINT，在8.0中返回二进制字符串。

Explanation of notation:符号说明：

For information about how to prepare in MySQL 5.7 for potential incompatibilities between MySQL 5.7 and 8.0, see Bit Functions and Operators, in MySQL 5.7 Reference Manual.有关如何在MySQL5.7中准备MySQL5.7和8.0之间潜在不兼容的信息，请参阅MySQL5.7参考手册中的位函数和运算符。

The following list describes available bit functions and operators: