A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

时间:2022-04-10 00:16:39

转载自:http://www.mysqltutorial.org/mysql-boolean/

An Introduction to MySQL BOOLEAN Data Type

 

Summary: this tutorial shows you how to use MySQL BOOLEAN data type to store Boolean values, true and false.

Introduction to MySQL BOOLEAN data type

MySQL does not have built-in Boolean type. However, it uses TINYINT(1) instead. To make it more convenient, MySQL provides BOOLEAN or BOOL as the synonym of TINYINT(1).

In MySQL, zero is considered as false, and non-zero value is considered as true. To use Boolean literals, you use the constants TRUE and FALSE that evaluate to 1 and 0 respectively. See the following example:

 

MySQL BOOLEAN example

MySQL stores Boolean value in the table as an integer. To demonstrate this, let’s look at the following tasks table:

Even though we specified the completed column as BOOLEAN, when we show the table definition, it is TINYINT(1) as follows:

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

The following statement inserts 2 rows into the tasks table:

Before saving data into the Boolean column, MySQL converts it into 1 or 0. The following query retrieves data from tasks table:

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

As you see, the true and false were converted to 1 and 0.

Because Boolean is TINYINT(1), you can insert value other than 1 and 0 into the Boolean column. Consider the following example:

It is working fine.

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

If you want to output the result as true and false, you can use the IF function as follows:

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

MySQL BOOLEAN operators

To get all completed tasks in the tasks table, you might come up with the following query:

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

As you see, it only returned the task with completed value 1. To fix it, you must use IS operator:

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

In this example, we used the IS operator to test a value against a Boolean value.

To get the pending tasks, you use IS FALSE or IS NOT TRUE as follows:

A2-04-02.MySQL DATA TYPES-An Introduction to MySQL BOOLEAN Data Type

In this tutorial, you have learned how to use the MySQL BOOLEAN data type, which is the synonym of TINYINT(1), and how to manipulate Boolean values.