If you are interested to learn about the MySQL JSON
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. BIT is a data type used in MySQL. This type stores bit values within range of 1-64. It is generally defined in the create table or defining statements and denoted as ‘BIT(n)’, where ‘n’ is the number of bit values that can be stored. This ‘n’ value keeps the range from 1-64 and if not defined in the statement, it is defaulted to 1 bit value. This bit value will be storing binary values. We know in MySQL, we need to call a binary value as ‘BIN()’ to be displayed as a binary value.
Syntax:
The following is a syntax to define bit data type in MySQL:
BIT (M);
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:
column_name BIT(1); OR column_name BIT;
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. Nested Structure in Keep Watching. The default character set of bit-value literal is the binary string. See the below statement:
mysql> SELECT CHARSET(B'); -- binary
How Does BIT Data Type work in MySQL?
BIT (n) is defined with a defining statement like ‘create table’ etc. Please note that, if the value ‘n’ is not specified, the BIT value is defaulted to ‘1’ bit. We can create a table to understand the BIT data type. Our table is to store the attendance of students in a class for 5 working days. The table holds data for student name, attendance as BIT value in binary with ‘1’ denoting presence & ‘0’ denoting absence and the class to which student belongs. The create table statement is as below:
Query:
CREATE TABLE attendance ( STUDENT_NAME CHAR(50), ATTENDANCE BIT(5), CLASS INT );
So the table name is attendance with three columns. Name of the student is stored as char data type, class in the INT data type and the attendance is stored as a BIT data type. The attendance filed will have 5 values representing the 5 working days. Let’s insert values into these fields.
Query:
INSERT INTO attendance (STUDENT_NAME, ATTENDANCE, CLASS)
VALUES ('ALAN',b'11111',5),
('EMIL',b'11000',5),
('ANNA',b'00111',5),
('JOHN',b'11101',5);A BIT value is inserted as a bit literal. Bit value literals are defined using b’val’ or ‘0bval’ notations. The table has now four rows with data for 4 different student attendance. We will retrieve the data with the SELECT query.
Query:
<code>select * from attendance;</code>
The simple SELECT query to retrieve complete data from table attendance.
Output:
The output retrieved is not showing correct data or data in our desired manner. We had input the attendance for 5 days from Monday to Friday for each student. But while retrieving, in the SELECT query, we have not specified how the bit value is to be displayed. Thus the value is displayed as a decimal value corresponding to the binary input shared
Query:
<code>SELECT STUDENT_NAME, BIN(ATTENDANCE), CLASS FROM attendance;</code>
The BIN() function will display the binary values input as bit value literals in the binary format itself.
Output:
We now get the attendance of each student as ‘1’ and ‘0 ‘, thus making it easier to identify the days when each of them were present or absent. But Anna’s attendance is having a slight issue here. She was absent on Monday and Tuesday. So that was marked as ‘0’ for both days. Since the bit value literal started with zeros, it omitted the preceding zeros while displaying the output. This can cause confusion as the data is available only for 3 days out of a total 5 days. To avoid this situation, we can use the LPAD function along with the BIN function.
Query:
<code>SELECT STUDENT_NAME, LPAD(BIN(ATTENDANCE),5,'0') as ATTENDANCE, CLASS FROM attendance;</code>
Output:
Now all attendance look similar as the count of days is constantly 5 days. These were several methods of retrieving a BIT data type in MySQL. In our variable definition statement, we had specified the number of bits in the variable attendance as 5, by the part of the query – attendance BIT(5). As discussed, this count can range from 1-64 and the default count is 1 when no digit is specified within (). We can see some examples of different lengths in the BIT value.
Query:
<code>CREATE TABLE sampleBITs (<br>sample1 BIT(10),<br>sample2 BIT,<br>sample3 BIT(50));</code>
A table with 3 variables sample1, sample2 and sample3 are created. Sample2 has no length specified, which means the length is 1. We can insert the values into this table.
Query:
<code>INSERT INTO sampleBITs (sample1, sample2, sample3)<br>VALUES (b'1010101010', 0b1, B'10001100011000110001111111000110001100011100110001');<br>select * from sampleBITs;</code>
Output:
Note that, here we have inserted values using all 3 valid bit value laterals. Now, the table can be retrieved as below.
Query:
<code>SELECT<br>LPAD(BIN(sample1),10,'0') as sample1,<br>LPAD(BIN(sample2),1,'0') as sample2,<br>LPAD(BIN(sample3),50,'0') as sample3<br>FROM sampleBITs;</code>
Output:
We have used LPAD and BIN functions to retrieve the data in a proper format.NOTE: Bit literals can be specified as b’val’ or 0bval. The b’val’ can also be denoted as B’val’, as the literal is not case sensitive in this format. But the leading 0b is case sensitive and cannot be replaced with 0B. A bit-value literal is always binary value by default, in both notations.
Let us understand the concept of BIT 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:
CREATE TABLE my_calendars(
years INT,
weeks INT,
days BIT(7),
PRIMARY KEY(years, weeks)
); 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:
mysql> INSERT INTO my_calendars (years, weeks, days) VALUES(2020, 2, B'1111100');
Next, execute the below query to retrieve the data from the my_calendar table:
mysql> SELECT * FROM my_calendars;
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().
mysql> SELECT years, weeks, BIN(days) FROM my_calendars;
After execution, we will get the output in our desired manner.l
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:
mysql> INSERT INTO my_calendars (years, weeks, days) VALUES(2020, 1, B'111100');
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:
mysql> SELECT years, weeks, LPAD (BIN(days), 7, '0') FROM my_calendars;
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.
