SQLのboolean型は、データベースにより実装が異なる 真偽NULLの判定方法サンプル

SQLのboolean型についてまとめています。

以下のサンプルSQLは、MySQL8.0.18を前提としています。

データ型booleanを使ったサンプルSQL

Oracle、MySQL、PostgreSQLではTrue/Falseを扱うデータ型Booleanが使用可能です。SQL Serverでは、同等のbit型を使うことができます。bit型は1と0を取り扱い可能です。

まずは、テスト用のテーブルboolean_testを作成しましょう。

mysql> create table boolean_test ( id varchar(4), value boolean );
Query OK, 0 rows affected (0.06 sec)

mysql> desc boolean_test;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | varchar(4) | YES  |     | NULL    |       |
| value | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

mysql> show create table boolean_test;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                         |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| boolean_test | CREATE TABLE `boolean_test` (
  `id` varchar(4) DEFAULT NULL,
  `value` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=sjis |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------+mysql> insert boolean_test (id, value) values ('0001',(true));

テーブルboolean_testは、varchar(4)のidと、boolean型のvalueの2カラムで構成しています。MySQLでは、booleanはtinyint型に置き換えられます。tinyintは0と1以外の値を格納することが可能なので、厳密なboolean型を扱うには挿入・更新時に値チェックが必要になります。Oracle、PostgreSQLではboolean型が提供されています。

テーブルboolean_testに以下のデータを挿入します。

• 0001 , 1
• 0002, 0
• 0003, NULL

mysql> insert boolean_test (id, value) values ('0001',1); 
Query OK, 1 row affected (0.00 sec)

mysql> insert boolean_test (id, value) values ('0002',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert boolean_test (id, value) values ('0003',null);
Query OK, 1 row affected (0.01 sec)

MySQLでは、(true）は1、(false)は0として扱われます。テーブルに格納される値は以下のようになります。

mysql> select * from boolean_test;
+------+-------+
| id   | value |
+------+-------+
| 0001 |     1 |
| 0002 |     0 |
| 0003 |  NULL |
+------+-------+

where条件で、is true、is false、is null、is not trueを指定したときの結果は以下のようになります。

mysql> select * from boolean_test where value is true;
+------+-------+
| id   | value |
+------+-------+
| 0001 |     1 |
+------+-------+

mysql> select * from boolean_test where value is false;
+------+-------+
| id   | value |
+------+-------+
| 0002 |     0 |
+------+-------+

mysql> select * from boolean_test where value is null;
+------+-------+
| id   | value |
+------+-------+
| 0003 |  NULL |
+------+-------+

mysql> select * from boolean_test where value is not true;
+------+-------+
| id   | value |
+------+-------+
| 0002 |     0 |
| 0003 |  NULL |
+------+-------+

insert時の値には（true）と（false）が使えます。以下のSQLは、次の２つのデータを挿入したときの例です。

• 0004 , (true)
• 0005, (false)

mysql> insert boolean_test (id, value) values ('0004',(true));
Query OK, 1 row affected (0.01 sec)

mysql> insert boolean_test (id, value) values ('0005',(false));
Query OK, 1 row affected (0.03 sec)

mysql> select * from boolean_test;
+------+-------+
| id   | value |
+------+-------+
| 0001 |     1 |
| 0002 |     0 |
| 0003 |  NULL |
| 0004 |     1 |
| 0005 |     0 |
+------+-------+

データベースによっては、on、off、t、fなどが使えます。

まとめ

ポテパンダの一言メモ

• boolean型は真（１）、偽（0）、NULLの3値を取ることができる
• データーベースによってboolean型の実装には違いがある。MySQLでは実体はtinyintとなる。
• where条件で真以外を指定するには、is not trueなどを指定し、偽とNULLを含める必要あり
• データベースによって、真偽値はtrue、falseなどが使える