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)