Summary: in this tutorial, you will learn about MySQL BIT
data type and how to store BIT
data in a column of a table.
Introduction to MySQL BIT data type
The BIT
data type that allows you to store bit values, which are 0 and 1.
Here’s the syntax for defining BIT
type of a column:
column_name BIT(n)
Code language: SQL (Structured Query Language) (sql)
The BIT(n)
can store up to n-bit values. The n
can range from 1 to 64. The default value of n is 1 if you skip it.
So the following syntaxes are equivalent:
column_name BIT(1);
Code language: SQL (Structured Query Language) (sql)
and
column_name BIT;
Code language: SQL (Structured Query Language) (sql)
BIT literals
To specify a bit value literal, you use b'val'
or 0bval
notation, which val
is a binary value that contains only 0 and 1.
The leading b
can be written as B
, for example, the following are valid bit literals:
b01
B11
Code language: SQL (Structured Query Language) (sql)
However, the leading 0b
is case-sensitive. Therefore, you cannot use 0B
. For example, the following is an invalid bit literal value:
0B'1000'
Code language: SQL (Structured Query Language) (sql)
By default the character set of a bit-value literal is the binary string as follows:
SELECT CHARSET(B'); -- binary
Code language: SQL (Structured Query Language) (sql)
MySQL BIT data type examples
The following statement creates a new table named working_calendars
that has the days column is BIT(7)
:
CREATE TABLE working_calendars(
year INT,
week INT,
days BIT(7),
PRIMARY KEY(year,week)
);
Code language: SQL (Structured Query Language) (sql)
The values in the column days
indicate whether the day is a working day or day off i.e., 1: working day and 0: day off.
Suppose that Saturday and Friday of the first week of 2017 are not working days, you can insert a row into the working_calendars
table:
INSERT INTO working_calendars(year,week,days)
VALUES(2017,1,B'1111100');
Code language: SQL (Structured Query Language) (sql)
The following query retrieves data from the working_calendar
table:
SELECT
year, week, days
FROM
working_calendars;
Code language: SQL (Structured Query Language) (sql)
Output:
+------+------+------------+
| year | week | days |
+------+------+------------+
| 2017 | 1 | 0x7C |
+------+------+------------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
The output indicates that the bit value in the days
column is converted into an integer. To represent it as bit values, you use the BIN
function:
SELECT
year, week, bin(days)
FROM
working_calendars;
Code language: SQL (Structured Query Language) (sql)
Output:
+------+------+-----------+
| year | week | bin(days) |
+------+------+-----------+
| 2017 | 1 | 1111100 |
+------+------+-----------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
If you insert a value into a BIT(n)
column that is less than n
bits long, MySQL will pad zeros on the left of the bit value.
Suppose the first day of the second week is off, you can insert 01111100
into the days
column. However, the 111100
value will also work because MySQL will pad one zero to the left.
INSERT INTO working_calendars(year,week,days)
VALUES(2017,2,B'111100');
Code language: SQL (Structured Query Language) (sql)
To view the data you use the same query as above:
SELECT
year, week , bin(days)
FROM
working_calendars;
Code language: SQL (Structured Query Language) (sql)
Output:
+------+------+-----------+
| year | week | bin(days) |
+------+------+-----------+
| 2017 | 1 | 1111100 |
| 2017 | 2 | 111100 |
+------+------+-----------+
2 rows in set (0.00 sec)
Code language: JavaScript (javascript)
The output shows that MySQL removed the leading zeros before returning the result. To display it correctly, you can use the LPAD()
function:
SELECT
year, week, lpad(bin(days),7,'0')
FROM
working_calendars;
Code language: SQL (Structured Query Language) (sql)
Output:
+------+------+-----------------------+
| year | week | lpad(bin(days),7,'0') |
+------+------+-----------------------+
| 2017 | 1 | 1111100 |
| 2017 | 2 | 0111100 |
+------+------+-----------------------+
2 rows in set (0.00 sec)
Code language: JavaScript (javascript)
The output shows the expected format.
Summary
- Use MySQL
BIT
data type to storeBIT
data in a table.