Updated May 15, 2023
Introduction to MySQL Boolean
There is none of the built-in datatype present in MySQL for boolean values. However, MySQL provides us with the TINYINT data type, which can store values of integers with small values. We can declare the column’s data type whose behavior is like boolean with TINYINT(1) data type. That will function in the same way as a boolean. The 0(zero) is the FALSE value, while all other non-zero values are 1 in MySQL. MySQL provides keywords such as BOOLEAN or BOOL, which are internally treated in the same manner as TINYINT(1). In this article, we will explore the available data types in MySQL that can be utilized to store boolean values. We will also demonstrate the usage of boolean values in tables through examples.
How to Declare & Store Boolean Values in MySQL?
We can specify the column’s datatype that might store the boolean value as BOOLEAN, BOOL, or TINYINT(1). All of these behave similarly and are synonyms of each other. The column declared with the data type of any of the mentioned databases evaluates the FALSE value as 0 and stores it as 0. On the other hand, the column considers and stores all other values, including TRUE or any non-zero value, as 1 for the respective record. Let us fire a straightforward 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 one 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 participants’ details in the marathon and have columns that will store boolean values in it, such as healthChecked and runCompleted. We will declare the data type of the healthChecked column as BOOLEAN and runCompleted as BOOL and check the results of the created table. For the table creation, we will use 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 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 MySQL automatically converts and treats the data type of the “healthChecked” column as TINYINT(1) and the data type of the “runCompleted” column as BOOL, even though we specified them as BOOLEAN.
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’s execute the following select query to observe the retrieved results
Query:
SELECT * FROM marathon_players;Output:
4. In the first record, Ramesh’s name was inserted and we specified the healthChecked and runCompleted columns as true and false, respectively. The insertion stored them as 1 and 0, respectively. In the second record, we stored the values -12 and 25 in the healthChecked and runCompleted columns, respectively, using the same format that we declared. Even though we declared those columns as BOOLEAN and BOOL, the database internally treated them as TINYINT datatypes, expanding the column length to store the values.
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: IS TRUE, IS FALSE, IS NOT TRUE, and 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. From the results, it is evident that both the values 1 and 12 are considered true. Any non-zero value, whether positive or negative, will be considered true. 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, let’s review the results for all individuals whose run was not completed by checking the value of the “runCompleted” column. This will allow us to verify the functionality of the remaining two clauses. Let us prepare the query using 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, we can use the keywords “BOOLEAN” and “BOOL” to declare the data type of the column, which will be internally treated and considered as TINYINT(1) data type. Hence, we can say that BOOLEAN and BOOL are synonyms of the TINYINT(1) data type. In MySQL, any truth value, regardless of its case, is considered and stored as 1. Similarly, any non-zero value and the value “1” are treated as TRUE when using clauses such as IS TRUE, IS FALSE, IS NOT TRUE, or IS NOT FALSE. The opposite is applicable for 0 and FALSE in MySQL.
Recommended Articles
We hope that this EDUCBA information on “MySQL Boolean” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
