GBase 8a里,null常量数据,注意不是某个字段的数值为null,而是一个null常量,如果不指定类型,默认未varbinary。可以通过cast强制指定类型,也可以根据上下文,从其它对应字段取得类型,比如union的其它表的。
目录导航
验证环境
tt1表是一个id int类型的表,就这一个字段。
gbase> desc tt1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
tt2表是2个字段的表,多了一个varchar类型的name字段。
gbase> desc tt2;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
tt3也是2个字段,但name字段故意设置成bigint类型
gbase> create table tt3(id int, name bigint);
Query OK, 0 rows affected (Elapsed: 00:00:00.25)
gbase> insert into tt3 values(1,111);
Query OK, 1 row affected (Elapsed: 00:00:00.19)
不指定null常量类型,则默认为varbinary
如下的tt2表,用 null 生成name字段,因为没有指定类型,则默认是varbinary。
gbase> create table tt6 as select id,null name from tt1;
Query OK, 9 rows affected (Elapsed: 00:00:01.18)
gbase> desc tt6;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varbinary(1) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
通过cast强制指定null的类型
gbase> create table tt8 select id,cast(null as int) name from tt1;
Query OK, 9 rows affected (Elapsed: 00:00:00.56)
gbase> desc tt8;
+-------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | bigint(1) | YES | | NULL | |
+-------+-----------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
2表union时其中一个包含了明确类型时可以正确识别
虽然带null常量的tt1在union的前面,但结果依然正确识别了类型为tt2的varchar。
gbase> create table tt4 as select id,null name from tt1 union all select * from tt2;
Query OK, 18 rows affected (Elapsed: 00:00:00.63)
gbase> desc tt4;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
多表union时对应列的类型必须一致
如果不一致,则会报错,比如如下第3个表tt3是bigint类型,第2个表tt2是varchar类型,语句报错。
gbase> create table tt7 as select id,null name from tt1 union select * from tt2 union all select * from tt3;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: UNION/INTERSECT/MINUS of non-matching columns: VARCHAR UNION/INTERSECT/MINUS LONGLONG
必须通过cast强制转化指定类型
gbase> create table tt7 as select id,null name from tt1 union select * from tt2 union all select id,cast(name as char) from tt3;
Query OK, 17 rows affected (Elapsed: 00:00:00.59)
gbase> desc tt7;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)