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