MySQL-Boolean型

MySQL-Boolean型

• MySQL内部保存Boolean型是按照 「tinyint」1字节型「-128～127」的数字来保存。
• 插入true 保存为 1
• 插入false保存为 0
• = true 检索出 true，1
• = false 检索出 false，0
• is true 检索出 非 0
• is false 检索出 0

创建测试表

mysql> create table `test` (
    ->   `boolColumn` boolean
    -> );
Query OK, 0 rows affected (0.00 sec)

确认表定义

mysql> show create table test;
+-------+----------------------------------------------------------------+
| Table | Create Table                                                   |
+-------+----------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `boolColumn` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------+
1 row in set (0.00 sec)

插入数据测试

mysql> insert into test values (true), (false), (0), (1), (100), (-100), (-127), (-128)，(127)，(128)
    -> ;
ERROR 1264 (22003): Out of range value for column 'boolColumn' at row 10

插入结果确认

mysql> select * from test;
+------------+
| boolColumn |
+------------+
|          1 |   ← true
|          0 |   ← false
|          0 |   ← 0
|          1 |   ← 1
|        100 |
|       -100 |
|       -127 |
|       -128 |
|        127 |
+------------+
9 rows in set (0.00 sec)

可以看到，插入 true 是当 1 保存， 插入 false 是当 0 保存，-128～127 范围内可插入。

检索测试

测试 = true/false

mysql> select * from test where boolColumn = true;
+------------+
| boolColumn |
+------------+
|          1 |
|          1 |
+------------+
2 rows in set (0.00 sec)

mysql> select * from test where boolColumn = false;
+------------+
| boolColumn |
+------------+
|          0 |
|          0 |
+------------+
2 rows in set (0.00 sec)

以 true，1 插入的行 和 以 false，0 插入的行被检索出。

测试 is true/false

mysql> select * from test where boolColumn is true;
+------------+
| boolColumn |
+------------+
|          1 |
|          1 |
|        100 |
|       -100 |
|       -127 |
|       -128 |
|        127 |
+------------+
7 rows in set (0.00 sec)

mysql> select * from test where boolColumn is false;
+------------+
| boolColumn |
+------------+
|          0 |
|          0 |
+------------+
2 rows in set (0.00 sec)