南大通用GBase 8a boolean类型的insert update操作

GBase 8a 数据库支持boolean类型，但实质内部是用tinyint 的0和1实现的。0表示false, 1表示true。对boolean类型的操作，既可以用true,false，也可以直接用0和1。

如下是代码演示。

建表

可以看到内部就是用tinyint(1)代替的。

gbase> create table testboolean(id int, bool boolean);
Query OK, 0 rows affected (Elapsed: 00:00:00.16)

gbase> desc testboolean;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| bool  | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> show create table testboolean;
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testboolean | CREATE TABLE "testboolean" (
  "id" int(11) DEFAULT NULL,
  "bool" tinyint(1) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

插入Insert

insert是既可以用true,也可以用1，结果都是一样的。

gbase> insert into testboolean values(1,true);
Query OK, 1 row affected (Elapsed: 00:00:00.08)

gbase> insert into testboolean values(2,false);
Query OK, 1 row affected (Elapsed: 00:00:00.08)

gbase> insert into testboolean values(3,1);
\Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into testboolean values(4,0);
Query OK, 1 row affected (Elapsed: 00:00:00.08)

gbase> select * from testboolean;
+------+------+
| id   | bool |
+------+------+
|    1 |    1 |
|    2 |    0 |
|    3 |    1 |
|    4 |    0 |
+------+------+
4 rows in set (Elapsed: 00:00:00.01)

条件查询Select

查询条件也同样可以用true和1， false和0.

gbase> select * from testboolean where bool=true;
+------+------+
| id   | bool |
+------+------+
|    1 |    1 |
|    3 |    1 |
+------+------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from testboolean where bool=1;
+------+------+
| id   | bool |
+------+------+
|    1 |    1 |
|    3 |    1 |
+------+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> select * from testboolean where bool=false;
+------+------+
| id   | bool |
+------+------+
|    2 |    0 |
|    4 |    0 |
+------+------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> select * from testboolean where bool=0;
+------+------+
| id   | bool |
+------+------+
|    2 |    0 |
|    4 |    0 |
+------+------+
2 rows in set (Elapsed: 00:00:00.00)

更新update

一样的可以使用0和1代替false,true。

gbase> update testboolean set bool=true where bool=false and id=2;
Query OK, 1 row affected (Elapsed: 00:00:00.07)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from testboolean;
+------+------+
| id   | bool |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    0 |
+------+------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> update testboolean set bool=0 where bool=1 and id=2;
Query OK, 1 row affected (Elapsed: 00:00:00.10)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from testboolean;
+------+------+
| id   | bool |
+------+------+
|    1 |    1 |
|    2 |    0 |
|    3 |    1 |
|    4 |    0 |
+------+------+
4 rows in set (Elapsed: 00:00:00.01)