Cara menggunakan boolean in mysql workbench

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

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)0 instead. To make it more convenient, MySQL provides

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)1 or

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)2 as the synonym of

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)0.

In MySQL, zero is considered as false, and non-zero value is considered as true. To use Boolean literals, you use the constants

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)4 and

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)5 that evaluate to 1 and 0 respectively. See the following example:

SELECT true, false, TRUE, FALSE, True, False; -- 1 0 1 0 1 0

Code language: SQL (Structured Query Language) (sql)

MySQL BOOLEAN example

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

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)6 table:

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)

Even though we specified the completed column as

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)1, when we show the table definition, it is

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)0 as follows:

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)

The following statement inserts 2 rows into the

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)6 table:

INSERT INTO tasks(title,completed) VALUES('Master MySQL Boolean type',true), ('Design database table',false);

Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)6 table:

SELECT id, title, completed FROM tasks;

Code language: SQL (Structured Query Language) (sql)

As you see, the

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)1 and

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)2 were converted to 1 and 0.

Because Boolean is

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)0, you can insert value other than 1 and 0 into the Boolean column. Consider the following example:

INSERT INTO tasks(title,completed) VALUES('Test Boolean with a number',2);

Code language: SQL (Structured Query Language) (sql)

It is working fine.

If you want to output the result as

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)1 and

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)2, you can use the

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)6 function as follows:

SELECT id, title, IF(completed, 'true', 'false') completed FROM tasks;

Code language: SQL (Structured Query Language) (sql)

MySQL BOOLEAN operators

To get all completed tasks in the

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)6 table, you might come up with the following query:

SELECT id, title, completed FROM tasks WHERE completed = TRUE;

Code language: SQL (Structured Query Language) (sql)

As you see, it only returned the task with

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)8 value 1. To fix it, you must use

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)9 operator:

SELECT id, title, completed FROM tasks WHERE completed IS TRUE;

Code language: SQL (Structured Query Language) (sql)

In this example, we used the

DESCRIBE tasks;

Code language: SQL (Structured Query Language) (sql)9 operator to test a value against a Boolean value.

To get the pending tasks, you use

INSERT INTO tasks(title,completed) VALUES('Master MySQL Boolean type',true), ('Design database table',false);

Code language: SQL (Structured Query Language) (sql)1 or

INSERT INTO tasks(title,completed) VALUES('Master MySQL Boolean type',true), ('Design database table',false);

Code language: SQL (Structured Query Language) (sql)2 as follows:

SELECT id, title, completed FROM tasks WHERE completed IS NOT TRUE

Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the

INSERT INTO tasks(title,completed) VALUES('Master MySQL Boolean type',true), ('Design database table',false);

Code language: SQL (Structured Query Language) (sql)3 data type, which is the synonym of

CREATE TABLE tasks ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, completed BOOLEAN );

Code language: SQL (Structured Query Language) (sql)0, and how to manipulate Boolean values.