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并不是实际占用空间。

 

参考 http://www.gbase8.cn/1357