MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Bit-value literals are written using
b'
or
val
'0b
notation.
val
val
is a binary value written using
zeros and ones. Lettercase of any leading b
does not matter. A leading 0b
is
case-sensitive and cannot be written as 0B
.
Legal bit-value literals:
b'01' B'01' 0b01
Illegal bit-value literals:
b'2' (2 is not a binary digit) 0B01 (0B must be written as 0b)
By default, a 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 | +-----------+--------------------+
A bit-value literal may have an optional character set
introducer and COLLATE
clause, to designate
it as a string that uses a particular character set and
collation:
[_charset_name
] b'val
' [COLLATEcollation_name
]
Examples:
SELECT _latin1 b'1000001'; SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;
The examples use
b'
notation,
but val
'0b
notation
permits introducers as well. For information about introducers,
see Section 10.3.8, “Character Set Introducers”.
val
In numeric contexts, MySQL treats a bit literal like an integer.
To ensure numeric treatment of a bit literal, use it in numeric
context. Ways to do this include adding 0 or using
CAST(... AS UNSIGNED)
. For
example, a bit literal assigned to a user-defined variable is a
binary string by default. To assign the value as a number, use
it in 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 | +------+------+------+
An empty bit value (b''
) evaluates to a
zero-length binary string. Converted to a number, it produces 0:
mysql>SELECT CHARSET(b''), LENGTH(b'');
+--------------+-------------+ | CHARSET(b'') | LENGTH(b'') | +--------------+-------------+ | binary | 0 | +--------------+-------------+ mysql>SELECT b''+0;
+-------+ | b''+0 | +-------+ | 0 | +-------+
Bit-value notation is convenient for specifying values to be
assigned 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';
Bit values in result sets are returned as binary values, which
may not display well. To convert a bit value to printable form,
use it in numeric context or use a conversion function such as
BIN()
or
HEX()
. High-order 0 digits are
not displayed in the converted value.
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 |
+------+----------+--------+--------+