MySQL Boolean

Introduction to MySQL Boolean

There is none of the built-in datatype present in MySQL for boolean values. However, MySQL provides us with TINYINT data type which can store values of integers with small values. We can declare the data type of the column whose behavior is like boolean with TINYINT(1) datatype. That will function in the same way as boolean. The 0(zero) is considered as the FALSE value while all other non-zero values are considered as 1 in MySQL. There are keywords present in MySQL like BOOLEAN or BOOL that are internally treated in the same manner as TINYINT(1). In this article, we will learn about the available data types in MySQL that can be used to store boolean values and further learn how we can use boolean values in tables with the help of examples.

How to Declare & Store Boolean Values in MySQL?

We can specify the datatype of the column that might store the boolean value as BOOLEAN, BOOL, or TINYINT(1). All of these behave in the same manner and are synonyms of each other. The FALSE value is evaluated as 0 and stored as 0 in the column declared with the data type of any of the above three mentioned databases. All other values such as TRUE or any other non-zero value will be considered and stored as 1 in that column for that record. Let us fire one simple command in MySQL.

Command:

SELECT TRUE, FALSE;

Output:

the output of the above query statement is as follows

Hence, we can conclude that MySQL considers true as 1 and false as 0. Note that true and false are treated the same irrespective of the case in which they are used.

Example to Implement of MySQL Boolean

1. Let us create one table named marathon_players that will store the details of the persons participated in the marathon and have columns that will store boolean values in it such as healthChecked and runCompleted. We will declare the datatype of healthChecked column as BOOLEAN and runCompleted as BOOL and check the results of the created table. For the table creation, we will make the use of the following CREATE TABLE query.

Query:

CREATE TABLE marathon_players(
player_id INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(100),
Age INT,
healthChecked VARCHAR(100),
runCompleted VARCHAR(100),
completionTime TIME,
PRIMARY KEY (player_id)
);

Now, let us simply describe the created table by using the following query statement:

DESC marathon_players;

Output:

Executing the above query gives the following output

2. We can conclude that even when we specified the datatype of healthChecked column as BOOLEAN and runCompleted as BOOL they got converted and internally treated as TINYINT(1) datatype automatically by Mysql.

Let us now insert some values in the marathon_players table using the following insert queries:

Query:

INSERT INTO marathon_players (player_id, Name, Age, healthChecked, runCompleted, completionTime) VALUES('1','Ramesh','25','true','false','02:50:56');
INSERT INTO marathon_players (player_id, Name, Age, healthChecked, runCompleted, completionTime) VALUES('2','Suresh','27','-12','25','01:30:21');

3. Now, let us fire the following select query to see what results are retrieved –

Query:

SELECT * FROM marathon_players;

Output:

4. We can see that the first record that we inserted with Ramesh name, the healthChecked, and runCompleted columns were specified as true and false while insertion which is stored as 1 and 0 respectively. While in the second record that we inserted -12 and 25 values in healthChecked and runCompleted columns were stored in the same format that we declared and with same values even when we declared those columns as BOOLEAN and BOOL as they were considered as TINYINT datatypes internally and expanded its column length to store the value.

5. IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE Clauses.

To know whether a particular column contains the value that evaluates to true or false, MySQL provides us with four clauses namely IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE clauses. Out of them, IS TRUE and IS NOT FALSE behave in the same fashion and IS NOT TRUE and similarly IS FALSE function and result in the same results. Let us try to find out the records in our table marathon_players whose healthChecked columns value is true or is equivalent to true. For this, I will first use the clause IS TRUE and my query statement will be as follows –

Code:

SELECT * FROM marathon_players WHERE healthChecked IS TRUE;

Output:

6. We can see from the results that 1 and 12 values both are valuated to true. Any non-zero value whether it be positive or negative will be considered as true itself. Now, let us use IS NOT FALSE clause and see whether we retrieve the same results. Our query statement will be as follows –

Query:

SELECT * FROM marathon_players WHERE healthChecked IS NOT FALSE;

Output:

Hence, we can conclude that both clauses work in the same manner.

7. Now, we will check the working of the remaining two clauses by checking the results for all those persons whose run was not completed by checking the value of the runCompleted column. Let us prepare the query that will make the use of the IS FALSE clause in it. The query will be as follows:

Query:

SELECT * FROM marathon_players WHERE runCompleted IS FALSE;

Output:

8. Now, let us use the IS NOT TRUE clause in our query and retrieve the results. Our query statement is as follows –

Query:

SELECT * FROM marathon_players WHERE runCompleted IS NOT TRUE;

Output:

We can conclude that IS NOT TRUE and IS FALSE give the same output.

Conclusion

MySQL does not provide any specific datatype that will store the boolean values. However, BOOLEAN and BOOL are the keywords that can be used to declare the data type of the column that is internally treated and considered as TINYINT(1) datatype. Hence, we can say that BOOLEAN and BOOL are synonyms of TINYINT(1) datatype. Any true irrespective of the case in which it is specified is considered and stored as 1. Any non-zero value and 1 are treated as TRUE in MYSQL when any of the IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE clauses are used. The opposite is applicable for 0 and FALSE in MySQL.

Recommended Articles

This is a guide to MySQL Boolean. Here we discuss the Introduction to MySQL Boolean and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. MySQL Operators
2. Jira vs Github
3. MySQL String functions
4. MySQL vs SQLite