How to Use Boolean Data Type in MySQL

In this tutorial, we aim to understand how to use the Boolean data type in SQL.

Database developers occasionally use the Boolean data type, allowing only two possible values, True or False. Having only two possible values makes things easier.

While Boolean is not a supported data type in SQL Server, there is a method to store Boolean values in SQL. This method involves using the BIT data type in SQL.

In this article, we’ll introduce the bit data type in SQL Server. For SQL Server, bit data can only take one of these values: 0, 1, NULL.

Regarding storage, for tables of less than nine columns, bit data is stored as one byte. For tables of 9 to 16 such columns, bit data takes up 2 bytes.

Moreover, string values in a SQL table can be converted to BIT values. Let us try to understand how this statement works.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with stu_firstName, stu_lastName, stu_passed, and stu_id columns in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  stu_passed BIT,
  primary key(stu_id)
);

Now let us insert student details in the table with stu_passed acting as the boolean value.

-- insert bulk rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName,stu_passed) 
 VALUES(1,"Preet","Sanghavi",0),
 (2,"Rich","John",1),
 (3,"Veron","Brow",0),
 (4,"Geo","Jos",1),
 (5,"Hash","Shah",1),
 (6,"Sachin","Parker",1),
 (7,"David","Miller",0);

The code above would enter the student data in the table student_details. We can visualize this table with the following command:

SELECT * from student_details;

The above stated code block would generate the following output:

stu_id	stu_firstName   stu_lastName	stu_passed   
1		Preet			Sanghavi		0
2		Rich			John			1
3		Veron			Brow			0
4		Geo				Jos				1
5		Hash			Shah			1
6		Sachin			Parker			1
7		David			Miller			0

As we can see above, students with the stu_id’s 1, 3, and 7 have the value 0 in the stu_passed column. It indicates that these students have not passed the exam.

On the other hand, students with the stu_id’s 2, 4, 5, and 6 have the value 1 in the stu_passed column. It would indicate that these students have passed the exam.

Using this idea of BIT helps us store values similar to the boolean data type in SQL. Thus, we have learned how to use the boolean values in SQL.