Boolean (true or false) in SQL

This is a note on using boolean (true or false) in SQL.

To implement boolean in SQL, use BIT.

BIT is a type that can store only 0 or 1.

Store 1 for true and 0 for false.

Defining the BIT Type

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `flag` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Inserting Data

INSERT INTO `test` (`id`, `flag`) VALUES
(1, b'1'),
(2, b'0');

Retrieving Data

SELECT * FROM `test`;

+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  2 |    0 |
+----+------+

Updating Data

UPDATE `test` SET `flag` = b'1' WHERE `id` = 2;

Deleting Data

DELETE FROM `test` WHERE `id` = 2;