描述

该语句用来修改已存在的表的结构，比如：修改表及表属性、新增列、修改列及属性、删除列等。

格式

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

参数解释

示例

• 把表t2的字段d改名为c，并同时修改字段类型

ALTER TABLE t2 CHANGE COLUMN d c CHAR(10);

• 增加、删除列

• 增加列前，执行DESCRIBE test; 命令查看表信息，如下图所示：

• 执行以下命令增加c3列

ALTER TABLE test ADD c3 int;

• 增加列后，执行DESCRIBE test; 命令查看表信息，如下图所示：

• 执行以下命令删除c3列

ALTER TABLE test DROP c3;

• 删除列后，执行DESCRIBE test; 命令查看表信息，如下图所示：

• 设置表格test的副本数，并且增加列c5

ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;