Description
You can use this statement to modify the structure of an existing table. For example, you can use this statement to modify the table and its attributes, add columns to it, modify its columns and attributes, and delete its columns.
Syntax
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
| ALTER INDEX index_name [VISIBLE | INVISIBLE]
| DROP {INDEX | KEY} index_name
| ADD PARTITION (range_partition_list)
| DROP {PARTITION | SUBPARTITION} partition_name_list
| REORGANIZE PARTITION name_list INTO partition_range_or_list
| TRUNCATE {PARTITION | SUBPARTITION} 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]
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
Parameters
| Parameter | Description |
|---|---|
| ADD [COLUMN] | Adds a column. You cannot add a primary key column. |
| CHANGE [COLUMN] | Modifies the column name and column attribute. |
| MODIFY [COLUMN] | Modifies the column attribute. |
| ALTER [COLUMN] | Modifies the default value of a column. |
| DROP [COLUMN] | Drops a column. You cannot drop a primary key column or columns that contain indexes. |
| ADD [UNIQUE INDEX] | Adds a unique index. |
| ADD [INDEX] | Adds a normal index. |
| ALTER [INDEX] | Modifies index attributes. |
| ADD [PARTITION] | Adds a partition. |
| DROP {PARTITION | SUBPARTITION} | Drops partitions: * PARTITION: drops the specified RANGE or LIST partitions (all subpartitions that exist under these partitions will be dropped as well), including deleting the partition definitions and partition data, and maintains the indexes defined on the partitioned table. * SUBPARTITION: drops the specified *-RANGE or *-LIST subpartitions, including deleting the subpartition definitions and subpartition data, and maintains the indexes defined on the partitioned table. Separate multiple partition names with commas (,). |
| REORGANIZE [PARTITION] | Reorganizes a partition. |
| TRUNCATE {PARTITION | SUBPARTITION} | Truncates partitions: * PARTITION: deletes all data in the specified RANGE or LIST partitions (data in all subpartitions that exist under these partitions will be deleted as well), and maintains the indexes defined on the partitioned table. * SUBPARTITION: deletes all data in the specified *-RANGE or *-LIST subpartitions, and maintains the indexes defined on the partitioned table. Separate multiple partition names with commas (,). |
| RENAME [TO] table_name | Renames a table. |
| DROP [TABLEGROUP] | Drops a table group. |
| DROP [FOREIGN KEY] | Drops a foreign key. |
| SET BLOCK_SIZE | Sets the block size of a table partition. |
| SET REPLICA_NUM | Specifies the number of replicas of the table. |
| SET COMPRESSION | Sets the compression mode of the table. |
| SET USE_BLOOM_FILTER | Specifies whether to use BloomFilter. |
| SET COMMENT | Sets comment information. |
| SET PROGRESSIVE_MERGE_NUM | Sets the number of progressive compaction steps. Value range: [1,64]. |
Example
- Rename Field d of Table t2 to c and modify the field type.
ALTER TABLE t2 CHANGE COLUMN d c INT;
- Add and drop columns
ALTER TABLE test ADD c3 int;
ALTER TABLE test DROP c3;
- Set the number of replicas of Table test and add Column c5.
ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;
- Truncate Partitions M202001 and M202002 of Table t_log_part_by_range.
obclient> CREATE TABLE t_log_part_by_range (
log_id bigint NOT NULL
, log_value varchar(50)
, log_date timestamp NOT NULL
) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
(
PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
, PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
, PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
, PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
, PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
);
Query OK, 0 rows affected (0.09 sec)
obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
Query OK, 0 rows affected (0.10 sec)