自分用メモ : mysqlで完全なbooleanを定義する

# はじめに

- よくある`tinyint(1)`でbooleanを表現できるとあるんだけど、実際には`-1`を入れられるのがなんだかなぁ、と思っていた。
- unsignedなtinyint(1)なら入らないよね？と思って実験
- [素晴らしい実験をしてくれてる人がいた](https://qiita.com/ka215/items/379c4d46d0c04b7fdb46)

# 実験結果

1より大きい値も入れられてしまうので、うーん・・という結果。しかも、`0以外true`という訳でもなさそう。

enum型で定義すると、TRUE(1), FALSE(2)のようなものになってしまうし。~~桁だけでなく入れられる値をもっと制限出来る方法ないかなぁ、と思うけれど、データベースという特質上、「ロジカルな操作は他でやってよ」と思われても仕方がないかも、とも。~~ 

```
mysql> create table piyo (
    -> flag tinyint(1) unsigned not null default false
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc piyo;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| flag  | tinyint(1) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into piyo values ();
Query OK, 1 row affected (0.00 sec)

mysql> select * from piyo;
+------+
| flag |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> insert into piyo values (-1);
ERROR 1264 (22003): Out of range value for column 'flag' at row 1

mysql> insert into piyo values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from piyo;
+------+
| flag |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> insert into piyo values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from piyo;
+------+
| flag |
+------+
|    0 |
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

mysql> select * from piyo where flag = true;
+------+
| flag |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from piyo where flag = false;
+------+
| flag |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
```

# 真似して実験

素晴らしい実験をしてくれてる人がいた
https://qiita.com/ka215/items/379c4d46d0c04b7fdb46

素晴らしい！

`0`か`1`しか入れられないようになった！

カラムの内容を表示するには、`カラム名+0`でクエリを書く必要があるけど、`true`,`false`で検索した結果は空白の数が異なるので、区別できてるらしい。O/Rマッパーとかは多分、Boolean型変数のfalseは0,trueは1として翻訳してくれるはずだから、これで完全なbooleanの定義が出来る！

```
mysql> CREATE TABLE `table_name` (
    ->   bool_bit bit(1) NOT NULL DEFAULT b'0'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table_name values ();
Query OK, 1 row affected (0.01 sec)

mysql> select * from table_name;
+----------+
| bool_bit |
+----------+
|          |
+----------+
1 row in set (0.00 sec)

mysql> select bool_bit+0 from table_name;
+------------+
| bool_bit+0 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)


mysql> insert into table_name values (-1);
ERROR 1406 (22001): Data too long for column 'bool_bit' at row 1
mysql> insert into table_name values (2);
ERROR 1406 (22001): Data too long for column 'bool_bit' at row 1


mysql> insert into table_name values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select bool_bit+0 from table_name;
+------------+
| bool_bit+0 |
+------------+
|          0 |
|          1 |
+------------+
2 rows in set (0.00 sec)


mysql> select bool_bit+0 from table_name where bool_bit = false;
+------------+
| bool_bit+0 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select bool_bit+0 from table_name where bool_bit = true;
+------------+
| bool_bit+0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)


mysql> select bool_bit from table_name where bool_bit = true;
+----------+
| bool_bit |
+----------+
|         |
+----------+
1 row in set (0.00 sec)

mysql> select bool_bit from table_name where bool_bit = false;
+----------+
| bool_bit |
+----------+
|          |
+----------+
1 row in set (0.00 sec)



mysql> select bool_bit+0 from table_name where bool_bit = "true";
+------------+
| bool_bit+0 |
+------------+
|          0 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> select bool_bit+0 from table_name where bool_bit = "false";
+------------+
| bool_bit+0 |
+------------+
|          0 |
+------------+
1 row in set, 1 warning (0.00 sec)

mysql> select bool_bit+0 from table_name where bool_bit = 0;
+------------+
| bool_bit+0 |
+------------+
|          0 |
+------------+
1 row in set (0.01 sec)

mysql> select bool_bit+0 from table_name where bool_bit = 1;
+------------+
| bool_bit+0 |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
```