南大通用GBase 8a增加字段的长度或更改类型定义

GBase 8a数据库集群,目前不支持字段的定义修改,除了varchar类型可以增加长度,其它的类型或属性均不可以,需要重建一个字段过渡一下。

varchar类型增加长度

请一定保留原有的附加属性,包括not null, default 等。否则change时会报错。单独修改注释等,请用modify功能。

gbase> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> alter table t2 change name  name varchar(30);
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.98)
Records: 0  Duplicates: 0  Warnings: 0

gbase> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> 

其它类型变动

其它类型只有先建一个新的字段,然后把数据update过去,然后把老的删除,把新的change成老的字段名。

如下例子,把value bigint, 改成 value int.

gbase> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  | MUL | NULL    |       |
| value | bigint(20) | YES  |     | NULL    |       |
| birth | datetime   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> alter table t1 add column value2 int after value;
Query OK, 4 rows affected (Elapsed: 00:00:00.65)
Records: 4  Duplicates: 4  Warnings: 0

gbase> desc t1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | int(11)    | YES  | MUL | NULL    |       |
| value  | bigint(20) | YES  |     | NULL    |       |
| value2 | int(11)    | YES  |     | NULL    |       |
| birth  | datetime   | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
4 rows in set (Elapsed: 00:00:00.00)

gbase> update t1 set value2=value;
Query OK, 4 rows affected (Elapsed: 00:00:00.28)
Rows matched: 4  Changed: 4  Warnings: 0

gbase> alter table t1 drop value;
Query OK, 4 rows affected (Elapsed: 00:00:00.42)
Records: 4  Duplicates: 4  Warnings: 0

gbase> desc t1;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | YES  | MUL | NULL    |       |
| value2 | int(11)  | YES  |     | NULL    |       |
| birth  | datetime | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> alter table t1 change value2 value int;
Query OK, 0 rows affected (Elapsed: 00:00:00.21)
Records: 0  Duplicates: 0  Warnings: 0

gbase> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  | MUL | NULL    |       |
| value | int(11)  | YES  |     | NULL    |       |
| birth | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.01)

本文的功能经常用到,虽然系统可以修改字段类型,但还是建议在设计阶段控制好,特别是避免从高精度,高长度更改为低精度,低长度的情况。 特别是varchar并不是实际占用空间。