Official document address: 9.1.5 Bit-Value Literals

Bit value literals are written in b'val 'or 0bval notation. Val is a binary value written in 0 and 1. Leading B is not case sensitive. The leading 0b is case sensitive and cannot be written as 0b.

Legal bit value literal:

b'01'
B'01'
0b01

Illegal bit value literal:

b'2'    (2 Not a binary number)
0B01    (0B Must be written as 0 b)

By default, the bit value literal is a binary string:

mysql> SELECT b'1000001', CHARSET(b'1000001');
+------------+---------------------+
| b'1000001' | CHARSET(b'1000001') |
+------------+---------------------+
| A          | binary              |
+------------+---------------------+
mysql> SELECT 0b1100001, CHARSET(0b1100001);
+-----------+--------------------+
| 0b1100001 | CHARSET(0b1100001) |
+-----------+--------------------+
| a         | binary             |
+-----------+--------------------+

Bit valued literals can have an optional character set importer and COLLATE clause, specifying it as a string using a specific character set and COLLATE:

[_charset_name] b'val' [COLLATE collation_name]

For example:

SELECT _latin1 b'1000001';
SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;

The example uses the b'val 'notation or the 0bval notation. For information about referrals, see 10.3. 8 character set introducer.

In a numeric context, MySQL treats bitliterals as integers. To ensure that bitliterals are processed numerically, use it in a numeric context. Methods include adding 0 or using CAST(... AS UNSIGNED). For example, bitliterals assigned to user-defined variables are binary strings by default. To assign a value as a number, use it in a numeric context:

mysql> SET @v1 = b'1100001';
mysql> SET @v2 = b'1100001'+0;
mysql> SET @v3 = CAST(b'1100001' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| a    |   97 |   97 |
+------+------+------+

The null value (b ") evaluates to a binary string of zero length. When converted to a number, 0:

mysql> SELECT CHARSET(b''), LENGTH(b'');
+--------------+-------------+
| CHARSET(b'') | LENGTH(b'') |
+--------------+-------------+
| binary       |           0 |
+--------------+-------------+
mysql> SELECT b''+0;
+-------+
| b''+0 |
+-------+
|     0 |
+-------+

BIT value representation is convenient for assigning values to BIT columns:

mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';

The bit values in the result set are returned as binary values, which may not be easy to display. To convert a bit value to a printable form, use it in a numeric context or use a conversion function, such as BIN() or HEX(). The converted value does not display the high-order number 0.

mysql> SELECT b+0, BIN(b), OCT(b), HEX(b) FROM t;
+------+----------+--------+--------+
| b+0  | BIN(b)   | OCT(b) | HEX(b) |
+------+----------+--------+--------+
|  255 | 11111111 | 377    | FF     |
|   10 | 1010     | 12     | A      |
|    5 | 101      | 5      | 5      |
+------+----------+--------+--------+

For bit literals, bit operations are considered numeric contexts, but bit operations allow numeric or binary string parameters in MySQL 8.0 and later. To explicitly specify a binary string context for bitliterals, use at least one of the parameters_ Binary importer:

mysql> SET @v1 = b'000010101' | b'000101010';
mysql> SET @v2 = _binary b'000010101' | _binary b'000101010';
mysql> SELECT HEX(@v1), HEX(@v2);
+----------+----------+
| HEX(@v1) | HEX(@v2) |
+----------+----------+
| 3F       | 003F     |
+----------+----------+

For these two bit operations, the displayed results look similar, but are not used_ The result of binary is a BIGINT value_ The result of binary is a binary string. Due to different result types, the displayed values are also different: the numerical results do not display the 0 of higher-order bits.