"云数据库OceanBase入门教程"的目录
云数据库OceanBase入门教程 OceanBase ALTER TABLE
描述
该语句用来修改已存在的表的结构，比如：修改表及表属性、新增列、修改列及属性、删除列等。
格式
alter_table_stmt:
ALTER TABLE table_name
alter_table_action_list;
| RENAME TABLE rename_table_action_list;
alter_table_action_list:
alter_table_action [, alter_table_action ...]
alter_table_action:
ADD [COLUMN] {column_definition | (column_definition_list)}
| CHANGE [COLUMN] column_name column_definition
| MODIFY [COLUMN] column_definition
| ALTER [COLUMN] column_name {SET DEFAULT const_value | DROP DEFAULT}
| DROP [COLUMN] column_name
| ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
| ADD {INDEX | KEY} [index_name] index_desc
| ADD FULLTEXT [INDEX | KEY] [index_name] fulltext_index_desc
| ALTER INDEX index_name [VISIBLE | INVISIBLE]
| DROP {INDEX | KEY} index_name
| ADD PARTITION (range_partition_list)
| DROP PARTITION (partition_name_list)
| REORGANIZE PARTITION name_list INTO partition_range_or_list
| TRUNCATE PARTITION name_list
| [SET] table_option_list
| RENAME [TO] table_name
| DROP TABLEGROUP
| DROP FOREIGN KEY fk_name
rename_table_action_list:
rename_table_action [, rename_table_action ...]
rename_table_action:
table_name TO table_name
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
index_desc:
(column_desc_list) [index_type] [index_option_list]
fulltext_index_desc:
(column_desc_list) CTXCAT(column_desc_list) [index_option_list]
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [(length)] [ASC | DESC]
index_type:
USING BTREE
index_option_list:
index_option [ index_option ...]
index_option:
[GLOBAL | LOCAL]
| block_size
| compression
| STORING(column_name_list)
| comment
table_option_list:
table_option [ table_option ...]
table_option:
| primary_zone
| replica_num
| table_tablegroup
| block_size
| compression
| AUTO_INCREMENT [=] INT_VALUE
| comment
| DUPLICATE_SCOPE [=] "none|zone|region|cluster"
partition_option:
PARTITION BY HASH(expression)
[subpartition_option] PARTITIONS partition_count
| PARTITION BY KEY([column_name_list])
[subpartition_option] PARTITIONS partition_count
| PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
[subpartition_option] (range_partition_list)
subpartition_option:
SUBPARTITION BY HASH(expression)
SUBPARTITIONS subpartition_count
| SUBPARTITION BY KEY(column_name_list)
SUBPARTITIONS subpartition_count
| SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
(range_subpartition_list)
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION partition_name
VALUES LESS THAN {(expression_list) | MAXVALUE}
range_subpartition_list:
range_subpartition [, range_subpartition ...]
range_subpartition:
SUBPARTITION subpartition_name
VALUES LESS THAN {(expression_list) | MAXVALUE}
expression_list:
expression [, expression ...]
column_name_list:
column_name [, column_name ...]
partition_name_list:
partition_name [, partition_name ...]
partition_count | subpartition_count:
INT_VALUE
参数解释
参数 | 描述 |
|---|---|
ADD [COLUMN] | 增加列，目前不支持增加主键列。 |
CHANGE [COLUMN] | 修改列名和列属性。 |
MODIFY [COLUMN] | 修改列属性。 |
ALTER [COLUMN] | 修改列的默认值。 |
DROP [COLUMN] | 删除列，不允许删除主键列或者包含索引的列。 |
ADD [UNIQUE INDEX] | 增加唯一索引。 |
ADD [INDEX] | 增加普通索引 |
ALTER [INDEX] | 修改索引属性。 |
ADD [PARTITION] | 增加分区。 |
DROP [PARTITION] | 删除分区。 |
REORGANIZE [PARTITION] | 分区重组。 |
TRUNCATE [PARTITION] | 删除分区数据。 |
RENAME [TO] table_name | 表重命名。 |
DROP [TABLEGROUP] | 删除表组。 |
DROP [FOREIGN KEY] | 删除外键。 |
SET BLOCK_SIZE | 设置 Partition 表 BLOCK 大小。 |
SET REPLICA_NUM | 设置表的副本数（指表的副本总数）。 |
SET COMPRESSION | 设置表的压缩方式。 |
SET USE_BLOOM_FILTER | 设置是否使用 BloomFilter。 |
SET COMMENT | 设置注释信息。 |
SET PROGRESSIVE_MERGE_NUM | 设置渐进合并步数，取值范围是 1~64。 |
示例
- 把表 t2 的字段 d 改名为 c，并同时修改字段类型
ALTER TABLE t2 CHANGE COLUMN d c INT;增加、删除列
- 增加列前，执行 DESCRIBE test; 命令查看表信息，如下图所示：
ALTER TABLE test ADD c3 int;增加列后，执行 DESCRIBE test; 命令查看表信息，如下图所示：
ALTER TABLE test DROP c3;删除列后，执行 DESCRIBE test; 命令查看表信息，如下图所示：
ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;