Cara menggunakan boolean in mysql workbench

Question

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

Table of Contents Show

Introduction to MySQL BOOLEAN data type
MySQL BOOLEAN example
MySQL BOOLEAN operators

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)