BIT
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
Bit data.
USAGE
BIT[(<number_of_bits_per_value)]
DETAILS
Data Type | Minimum Number of Bits | Maximum Number of Bits |
|---|---|---|
|
|
|
EXAMPLES
BIT
To create a BIT column, specify the number of bits the column should store:
CREATE TABLE bit_example (
description VARCHAR(20),
b1 BIT,
b4 BIT(4),
b16 BIT(16)
);
When BIT is specified without a length, the column is equivalent to BIT(1).
When BIT(0) is specified, an error is raised. This behavior differs from MySQL, which creates a BIT(1) column when BIT(0) is specified.
Insert Integer Literals
To insert integer literal values:
INSERT INTO bit_example VALUES
('Zero', 0, 0, 0),
('One', 1, 1, 1),
('Two', 2, 2, 2);
If the full value can't fit in the column, it will be truncated without raising a warning.
Insert Binary Literals
To insert binary literal values, use the b'value' syntax:
INSERT INTO bit_example VALUES
('Eight', b'1000', b'1000', b'1000'),
('Fifteen', b'1111', b'1111', b'1111'),
('2^16-1', b'1111111111111111', b'1111111111111111', b'1111111111111111');
If the full value can't fit in the column, it will be truncated without raising a warning.
Select Integer Values
To select the value of a BIT column as its integer value, cast the column to INT:
SELECT description,
CAST(b1 AS INT) AS 1_bit,
CAST(b4 AS INT) AS 4_bits,
CAST(b16 AS INT) AS 16_bits
FROM bit_example;
+-------------+-------+--------+---------+
| description | 1_bit | 4_bits | 16_bits |
+-------------+-------+--------+---------+
| Zero | 0 | 0 | 0 |
| One | 1 | 1 | 1 |
| Two | 1 | 2 | 2 |
| Eight | 1 | 8 | 8 |
| Fifteen | 1 | 15 | 15 |
| 2^16-1 | 1 | 15 | 65535 |
+-------------+-------+--------+---------+
Select Binary Values
To select the value of a BIT column in binary format, use the BIN() function:
SELECT description,
CONCAT('0b', BIN(b1)) AS 1_bit,
CONCAT('0b', LPAD(BIN(b4), 4, '0')) AS 4_bits,
CONCAT('0b', LPAD(BIN(b16), 16, '0')) AS 16_bits
FROM bit_example;
+-------------+-------+--------+--------------------+
| description | 1_bit | 4_bits | 16_bits |
+-------------+-------+--------+--------------------+
| Zero | 0b0 | 0b0000 | 0b0000000000000000 |
| One | 0b1 | 0b0001 | 0b0000000000000001 |
| Two | 0b1 | 0b0010 | 0b0000000000000010 |
| Eight | 0b1 | 0b1000 | 0b0000000000001000 |
| Fifteen | 0b1 | 0b1111 | 0b0000000000001111 |
| 2^16-1 | 0b1 | 0b1111 | 0b1111111111111111 |
+-------------+-------+--------+--------------------+
Select Octal Values
To select the value of a BIT column in octal format, use the OCT() function:
SELECT description,
CONCAT('0o', OCT(b1)) AS 1_bit,
CONCAT('0o', LPAD(OCT(b4), 2, '0')) AS 4_bits,
CONCAT('0o', LPAD(OCT(b16), 6, '0')) AS 16_bits
FROM bit_example;
+-------------+-------+--------+----------+
| description | 1_bit | 4_bits | 16_bits |
+-------------+-------+--------+----------+
| Zero | 0o0 | 0o00 | 0o000000 |
| One | 0o1 | 0o01 | 0o000001 |
| Two | 0o1 | 0o02 | 0o000002 |
| Eight | 0o1 | 0o10 | 0o000010 |
| Fifteen | 0o1 | 0o17 | 0o000017 |
| 2^16-1 | 0o1 | 0o17 | 0o177777 |
+-------------+-------+--------+----------+
Select Hexadecimal Values
To select the value of a BIT column in hexadecimal format, use the HEX() function:
SELECT description,
CONCAT('0x', HEX(b1)) AS 1_bit,
CONCAT('0x', HEX(b4)) AS 4_bits,
CONCAT('0x', LPAD(HEX(b16), 4, '0')) AS 16_bits
FROM bit_example;
+-------------+-------+--------+---------+
| description | 1_bit | 4_bits | 16_bits |
+-------------+-------+--------+---------+
| Zero | 0x0 | 0x0 | 0x0000 |
| One | 0x1 | 0x1 | 0x0001 |
| Two | 0x1 | 0x2 | 0x0002 |
| Eight | 0x1 | 0x8 | 0x0008 |
| Fifteen | 0x1 | 0xF | 0x000F |
| 2^16-1 | 0x1 | 0xF | 0xFFFF |
+-------------+-------+--------+---------+