BIT

USAGE

BIT[(<number_of_bits_per_value)]

DETAILS

Data Type

Minimum Number of Bits

Maximum Number of Bits

BIT

1 (the default)

64

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES