BIT is a data type used in MySQL that allows us to store bit values. The bit value comes in a range of 1-64. It will store values only in 0 and 1. If we store a bit value like 2, it will return an error message. Generally, we can define the bit value with the create table or defining statements.
The following is a syntax to define bit data type in MySQL:
Here, the keyword BIT represents the storage of binary values, and its value is mentioned in variable M. The value of M can be in a range of 1-64. If we have not specified any value of M, it contains the default value is 1. Hence, the below statements are equivalent:
If we want to specify bit values literal, we can use the b'value' or 0bvalue notation. In this notation, the value represents a binary value that can be written only in zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively.
The default character set of bit-value literal is the binary string. See the below statement:
MySQL BIT Example
Let us understand the concept of BIT in MySQL through an example. First, we will create a new table named my_calendars that contains the 'days' column as BIT(7). See the below statement:
The "days" column values indicate whether a working day or day off. Here 1 represents the working day, and 0 value represents the day off.
Suppose the Saturday and Sunday of the week in the year 2020 are not the working days. In that case, we can insert a record into the my_calendars table as follows:
Next, execute the below query to retrieve the data from the my_calendar table:
We should get the output as follows:
We can see that the retrieved result is not showing data in our desired format. Thus, we will use the BIN() function to retrieve the data as binary. For that, we need to specifically call the column 'days' as BIN().
After execution, we will get the output in our desired manner.
If we insert a value into a bit column less than M bits long, MySQL automatically added zeros on the left of the specified bit value. Suppose the 1st day of the third week is off; we can insert 01111100 into the 'days' column. See the below statement:
After verifying the data, you can see that it works perfectly.
We can see that the retrieved output removed the leading zeros before returning the result. Thus, we will use the LPAD() function to retrieve the data correctly:
See the below output:
Difference between BIT and TINYINT
BIT and TINYINT both have different usage in MySQL.
A BIT data type is used to store the value of 1 bit that can be 0 or 1. It stores the value in the range of 1 to 64. If we try to insert other values (for example, 2) inside the BIT column, MySQL issues an error. In contrast, the TINYINT data type is used to store the integer value of 8 bits. It stores the value in the range of -128 to +127 or 0 to 256 and occupies 1 byte. If we try to insert other values (for example, 987) inside the TINYINT column, MySQL issued an error.
In this article, we have learned how to use and store a BIT data type in a MySQL table.