The MySQL BIT data type explained

The MySQL BIT type is a new data type introduced in MySQL version 5.0.3 that can be used for storing binary values.

A BIT type can hold only one of two binary values: 0 or 1.

The type can be used when you create or alter your table structure as follows:

CREATE TABLE bit_columns (
  columnOne BIT,
  columnTwo BIT(5)
);

The above statement will generate a MySQL table that has two columns of BIT data type.

You can define the storage length of the BIT type by adding a number inside parentheses as follows:

columnTwo BIT(5)

A BIT column can store between 1 to 64 bit values, with the default value of 1.

This means the example columnOne above can store only one binary value while columnTwo can store up to 5 binary values.

To insert a BIT type value, you need to use the binary literal notations of b'xxx' as shown below:

INSERT INTO bit_columns VALUES
(b'1', b'1011');

When you retrieve the stored values using the SELECT statement, MySQL will convert the binary values into hexadecimal values as shown below:

SELECT * FROM bit_columns;

--   +----------------------+----------------------+
--   | columnOne            | columnTwo            |
--   +----------------------+----------------------+
--   | 0x01                 | 0x0B                 |
--   +----------------------+----------------------+

To show the binary values instead of the hexadecimal values, you need to convert the result using BIN() function.

Here’s an example query using the BIN() function:

SELECT BIN(columnOne), BIN(columnTwo) FROM bit_columns;

The returned result set will be as follows:

+----------------+----------------+
| BIN(columnOne) | BIN(columnTwo) |
+----------------+----------------+
| 1              | 1011           |
+----------------+----------------+

Alternatively, you can also convert the binary values into their decimal equivalent using the CONV() function as follows:

SELECT CONV(columnOne, 2, 10), CONV(columnTwo, 2, 10) FROM bit_columns;

Binary values are base-2 numeral systems, so you need to convert them to base-10 numeral systems with the CONV() function.

Now the returned values will be decimal (base-10) numbers:

+------------------------+------------------------+
| CONV(columnOne, 2, 10) | CONV(columnTwo, 2, 10) |
+------------------------+------------------------+
| 1                      | 11                     |
+------------------------+------------------------+

And that’s how the BIT type works in MySQL.

Using BIT type for storing boolean values

MySQL BIT type can also be used for storing boolean values. To do so, you need to create a column that stores a maximum of one binary value.

Let’s create a new table named banned that store usernames that can’t be used for a web application as follows:

CREATE TABLE banned (
  username VARCHAR(20),
  is_banned BIT
);

Now insert some data to the table with the following statement:

INSERT INTO banned VALUES 
('joe_black', b'0'),
('jane_ph', b'1');

The table banned will now have the following data:

+-----------+----------------------+
| username  | is_banned            |
+-----------+----------------------+
| joe_black | 0x00                 |
| jane_ph   | 0x01                 |
+-----------+----------------------+

MySQL implicitly converts the binary values into their decimal equivalent when you use the binary column in the WHERE clause.

Take a look at the following example queries:

SELECT * FROM banned WHERE is_banned IS NOT TRUE;

--   +-----------+----------------------+
--   | username  | is_banned            |
--   +-----------+----------------------+
--   | joe_black | 0x00                 |
--   +-----------+----------------------+

SELECT * FROM banned WHERE is_banned IS TRUE;

--   +----------+----------------------+
--   | username | is_banned            |
--   +----------+----------------------+
--   | jane_ph  | 0x01                 |
--   +----------+----------------------+

Finally, you can use the IF function to display the is_banned column value as true or false like this:

SELECT username, IF(is_banned, 'true', 'false') AS is_banned
  FROM banned WHERE is_banned IS NOT TRUE;

The query above will return the following output:

+-----------+-----------+
| username  | is_banned |
+-----------+-----------+
| joe_black | false     |
+-----------+-----------+

And that’s how you can use the BIT type for storing boolean values.

Please keep in mind that until MySQL version 5.0.3, the BIT type is just an alias for the TINYINT type.

It’s better to use the TINYINT type for storing boolean values if you have backward compatibility concerns.