目录
名称 | 值 |
CPU | Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz |
操作系统 | CentOS Linux release 7.9.2009 (Core) |
内存 | 3G |
逻辑核数 | 2 |
Gbase8a版本 | 8.6.2-R43.34.27468a27 |
- ALTER TABLE [VCNAME.][database_name.]table_name
- alter_specification [, alter_specification] ...
- alter_specification:
- ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
- | ADD [COLUMN] (column_definition,...)
- | CHANGE [COLUMN] old_col_name new_col_name column_definition
- | MODIFY [COLUMN] col_name column_definition
- [FIRST | AFTER col_name]
- | RENAME [TO] new_table_name
- | DROP [COLUMN] col_name
名称 | 描述 |
ADD [COLUMN] | 添加字段,关键字FIRST、AFTER分别表示添加到指定字段的前和后,如果不指定,就是添加到表尾。 |
CHANGE [COLUMN] | 修改字段名,但不支持修改类型、非空、默认值等,但支持列注释。 |
MODIFY [COLUMN] | 修改字段在表中的位置,其他同上。 |
RENAME [TO] | 修改表名。 |
DROP [COLUMN] | 删除字段。 |
8a修改字段数据类型目前只支持varchar类型到varchar类型。
建测试表
- gbase> CREATE TABLE TEST_09_19 (A VARCHAR(10), B VARCHAR(100) NOT NULL DEFAULT 'TEST_DEFAULT_VAL' COMMENT'TEST', C INT);
- Query OK, 0 rows affected (Elapsed: 00:00:00.02)
-
- gbase> DESC TEST_09_19;
- +-------+--------------+------+-----+------------------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+--------------+------+-----+------------------+-------+
- | A | varchar(10) | YES | | NULL | |
- | B | varchar(100) | NO | | TEST_DEFAULT_VAL | |
- | C | int(11) | YES | | NULL | |
- +-------+--------------+------+-----+------------------+-------+
- 3 rows in set (Elapsed: 00:00:00.00)
增加字段A的长度正常
- gbase> ALTER TABLE TEST_09_19 CHANGE A A VARCHAR(101);
- Query OK, 0 rows affected (Elapsed: 00:00:00.17)
- Records: 0 Duplicates: 0 Warnings: 0
增加字段B的长度报错,注意需要加上默认值和非空、注释等
- gbase> ALTER TABLE TEST_09_19 CHANGE B B VARCHAR(101);
- ERROR 1702 (HY000): gcluster table error: This version of GBase doesn't yet support 'such options, check default value and comment'.
我们加上再试试,执行正常。
- gbase> ALTER TABLE TEST_09_19 CHANGE B B VARCHAR(101) NOT NULL DEFAULT 'TEST_DEFAULT_VAL' COMMENT'TEST';
- Query OK, 0 rows affected (Elapsed: 00:00:00.10)
- Records: 0 Duplicates: 0 Warnings: 0
如果我们只是变化默认值,其他不变看看,提示不支持修改默认值。
- gbase> ALTER TABLE TEST_09_19 CHANGE B B VARCHAR(101) NOT NULL DEFAULT 'TEST_DEFAULT_VAL1' COMMENT'TEST';
- ERROR 1702 (HY000): gcluster table error: This version of GBase doesn't yet support 'change default value'.
如果我们只是变化注释,其他不变看看,执行正常。
- gbase> ALTER TABLE TEST_09_19 CHANGE B B VARCHAR(101) NOT NULL DEFAULT 'TEST_DEFAULT_VAL' COMMENT'TEST1';
- Query OK, 0 rows affected (Elapsed: 00:00:00.12)
- Records: 0 Duplicates: 0 Warnings: 0
测试数据生成
- gbase> DESC TEST_09_19;
- +-------+--------------+------+-----+------------------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+--------------+------+-----+------------------+-------+
- | A | varchar(101) | YES | | NULL | |
- | B | varchar(101) | NO | | TEST_DEFAULT_VAL | |
- | C | int(11) | YES | | NULL | |
- +-------+--------------+------+-----+------------------+-------+
- 3 rows in set (Elapsed: 00:00:00.00)
-
- gbase> INSERT INTO TEST_09_19 VALUES('SUN','MOON',1);
- Query OK, 1 row affected (Elapsed: 00:00:00.28)
-
- gbase> INSERT INTO TEST_09_19 VALUES('SUN','MOON',2);
- Query OK, 1 row affected (Elapsed: 00:00:00.04)
-
- gbase> SELECT * FROM TEST_09_19;
- +------+------+------+
- | A | B | C |
- +------+------+------+
- | SUN | MOON | 1 |
- | SUN | MOON | 2 |
- +------+------+------+
- 2 rows in set (Elapsed: 00:00:00.41)
测试int->varchar,提示:修改列定义,除了增加varchar长度都不支持。
- gbase> ALTER TABLE TEST_09_19 CHANGE C C VARCHAR(101);
- ERROR 1702 (HY000): gcluster table error: This version of GBase doesn't yet support 'ALTER column definition except increasing length of varchar'.
那我们曲线救援,通过新建复制字段->更新数据到复制字段->删除老字段->修改复制字段名为老字段名的方式,来实现字段类型修改。
- gbase> ALTER TABLE TEST_09_19 ADD COLUMN C_CopyColumn VARCHAR(10) AFTER C;
- Query OK, 2 rows affected (Elapsed: 00:00:00.08)
- Records: 2 Duplicates: 2 Warnings: 0
-
- gbase> UPDATE TEST_09_19 SET C_CopyColumn = C;
- Query OK, 2 rows affected (Elapsed: 00:00:00.10)
- Rows matched: 2 Changed: 2 Warnings: 0
-
- gbase> ALTER TABLE TEST_09_19 DROP C;
- Query OK, 2 rows affected (Elapsed: 00:00:00.24)
- Records: 2 Duplicates: 2 Warnings: 0
-
- gbase> ALTER TABLE TEST_09_19 CHANGE C_CopyColumn C VARCHAR(10) COMMENT'';
- Query OK, 0 rows affected (Elapsed: 00:00:00.22)
- Records: 0 Duplicates: 0 Warnings: 0
验证正确性
- gbase> DESC TEST_09_19;
- +-------+--------------+------+-----+------------------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+--------------+------+-----+------------------+-------+
- | A | varchar(101) | YES | | NULL | |
- | B | varchar(101) | NO | | TEST_DEFAULT_VAL | |
- | C | varchar(10) | YES | | NULL | |
- +-------+--------------+------+-----+------------------+-------+
- 3 rows in set (Elapsed: 00:00:00.01)
-
- gbase> SELECT * FROM TEST_09_19;
- +------+------+------+
- | A | B | C |
- +------+------+------+
- | SUN | MOON | 1 |
- | SUN | MOON | 2 |
- +------+------+------+
- 2 rows in set (Elapsed: 00:00:00.09)
通过上面的实验和语法树了解,8a暂时不支持通过ALTER语句修改字段默认值、非空属性,只有通过重建表来曲线救援了,如果有其他大佬知道的,可以分享大家一起学习一下。
1、ALTER TABLE CHANGE语句只支持VARCHAR到VARCHAR的类型转换,且是小范围到大范围的。
2、ALTER TABLE CHANGE语句支持注释修改。
3、其他类型修改需要通过:新建复制字段->更新数据到复制字段->删除老字段->修改复制字段名为老字段名的方式实现。
4、字段默认值、非空属性等需要通过重建表来实现。