github-fill

ALTER TABLE

更新时间：2023-08-03 17:20:32

描述

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

语法

alter_table_stmt:
      ALTER TABLE table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action: 
      ADD [COLUMN] column_definition  
          [FIRST | AFTER column_name]
    | ADD [COLUMN] (column_definition_list)
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} 
          [index_name] index_desc
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY
          [index_name] index_desc
          REFERENCES reference_definition
          [match_action][opt_reference_option_list]
    | ADD {INDEX | KEY} 
          [index_name] index_desc
    | ADD PARTITION (range_partition_list)
    | ALTER [COLUMN] column_name {
          SET DEFAULT const_value 
        | DROP DEFAULT
      }
    | ALTER INDEX index_name 
          [VISIBLE | INVISIBLE]
    | CHANGE [COLUMN] column_name column_definition
    | DROP [COLUMN] column_name
    | DROP {INDEX | KEY} index_name
    | DROP {PARTITION | SUBPARTITION} partition_name_list
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | MODIFY [COLUMN] column_definition
    | RENAME [TO] table_name
    | RENAME {INDEX | KEY} old_index_name TO new_index_name
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list

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]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULLX | NO ACTION | SET DEFAULT}

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"
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

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]	增加列，支持增加生成列。说明暂不支持增加主键列。
[FIRST \| AFTER column_name]	将新增的列作为表的第一列或在 `column_name` 列之后。目前，OceanBase 数据库仅支持在 `ADD COLUMN` 语法中设置列的位置。
CHANGE [COLUMN]	修改列名和列定义，仅支持增加特定字符数据类型（`VARCHAR`、`VARBINARY`、`CHAR` 等）的长度。
MODIFY [COLUMN]	修改列定义，仅支持增加特定字符数据类型（`VARCHAR`、`VARBINARY`、`CHAR` 等）的长度。
ALTER [COLUMN] {SET DEFAULT const_value \| DROP DEFAULT}	修改列的默认值。
DROP [COLUMN]	删除列，不允许删除主键列或者包含索引的列。
ADD UNIQUE {INDEX \| KEY}	增加唯一索引。创建唯一索引的同时也会为表增加与索引同名的约束。如果不指定键名，则会使用索引指定的名称，如果没有指定索引名，则按索引命名规则命名。如果不指定索引名，则会使用键指定的名称，如果没有指定键名，则会使用下划线（_）+ 序号的方式命名。（例如，使用 `c1` 列创建的索引如果命名重复，则会将索引命名为 `c1_2`。）如果同时指定了键名与索引名，则会使用索引名作为键与索引的名称。您可以通过 `SHOW INDEX` 语句查看表上的索引。
ADD FOREIGN KEY	增加外键。如果不指定外键名，则会使用表名 + `OBFK` + 创建时间命名。（例如，在 2021 年 8 月 1 日 00:00:00 为 `t1` 表创建的外键名称为 `t1_OBFK_1627747200000000`）。
ADD {INDEX \| KEY}	增加普通索引。 `INDEX` 与 `KEY` 同义。如果不指定索引名，则会使用索引引用的第一列作为索引名，如果命名存在重复，则会使用下划线（_）+ 序号的方式命名。（例如，使用 `c1` 列创建的索引如果命名重复，则会将索引命名为 `c1_2`。）您可以通过 `SHOW INDEX` 语句查看表上的索引。
ALTER INDEX	修改索引是否可见，当索引状态为 `INVISIBLE` 时，SQL 优化器将不会选择该索引。
ADD [PARTITION]	为分区表增加分区。 OceanBase 数据库不支持将非分区表修改为分区表。
DROP {PARTITION \| SUBPARTITION}	删除分区： `PARTITION`：针对 Range、List 类型的一级分区，删除指定分区（如果指定分区下存在二级分区，会同时删除该分区下所有二级分区），包括分区定义和其中的数据，同时对分区上存在的索引进行维护。 `SUBPARTITION`：针对 `-RANGE`、`-LIST` 类型的二级分区，删除指定二级分区，包括分区定义和其中的数据。同时对分区上存在的索引进行维护。多个分区名称之间用逗号分隔。注意删除分区时，请尽量避免该分区上存在活动的事务或查询，否则可能会导致 SQL 语句报错，或者一些异常情况。
REORGANIZE [PARTITION]	分区重组。说明当前版本暂不支持此参数。
TRUNCATE {PARTITION \| SUBPARTITION}	删除分区数据： `PARTITION`：针对 Range、List 类型的一级分区，清除指定分区中的全部数据（如果指定分区下存在二级分区，会同时清除该分区下所有二级分区中的数据），同时对分区上存在的索引进行维护。 `SUBPARTITION`：针对 `-RANGE`、`-LIST` 类型的二级分区，清除指定二级分区中的全部数据。同时对分区上存在的索引进行维护。多个分区名称之间用逗号分隔。注意删除分区数据时，请尽量避免该分区上存在活动的事务或查询，否则可能会导致 SQL 语句报错，或者一些异常情况。
RENAME [TO] table_name	表重命名。
RENAME {INDEX \| KEY}	重命名索引或键。
DROP [TABLEGROUP]	删除表组。
DROP [FOREIGN KEY]	删除外键。
[SET] table_option	设置表级属性，可选以下参数： `PRIMARY_ZONE`：设置表的 Primary Zone。 `REPLICA_NUM`：设置表的副本数（暂不支持）。 `TABLE_GROUP`：设置表所属的表组。 `BLOCK_SIZE`：设置表的微块大小，默认为 `16384`，即 16 KB，取值范围为 [1024,1048576]。 `COMPRESSION`：设置表的压缩方式，默认为 `None`，表示不压缩。 `AUTO_INCREMENT`：设置表中自增列的下一个取值。 `comment`：设置表的注释信息。 `DUPLICATE_SCOPE`：设置表的复制方式。 `PROGRESSIVE_MERGE_NUM`：设置渐进合并步数，取值范围为 [1,64]。 `parallel_clause`：指定表级别的并行度。 `NOPARALLEL`：并行度为 `1`，默认配置。 `PARALLEL integer`：指定并行度，`integer` 取值大于等于 `1`。

示例

将表 t2 的字段 d 改名为 c，并同时修改字段类型为 INTEGER。
```
obclient> ALTER TABLE t2 CHANGE COLUMN d c INT;
```
增加、删除列。
- 增加列前，执行 DESCRIBE test; 命令查看表信息，如下图所示：
- 执行以下命令增加 c3 列。
```
obclient> ALTER TABLE test ADD c3 INTEGER ;
```
- 增加列后，执行 DESCRIBE test; 命令查看表信息，如下图所示：
- 执行以下命令删除 c3 列。
```
obclient> ALTER TABLE test DROP c3;
```
- 删除列后，执行 DESCRIBE test; 命令查看表信息，如下图所示：
为表添加 c4 列，并将该列设置为 test 表的第一列。
```
obclient> ALTER TABLE test ADD COLUMN c4 INTEGER FIRST;
```

设置表 test 的副本数。

obclient> ALTER TABLE test SET REPLICA_NUM=2;

为表 t1 添加外键约束 fk1。

obclient> ALTER TABLE t1 ADD CONSTRAINT fk1 FOREIGN KEY (c3) REFERENCES t2(c1);

删除 t1 表的外键约束 fk1。

obclient> ALTER TABLE t1 DROP FOREIGN KEY fk1;

索引操作。

将 test 表的索引 ind1 重命名为 ind2。

obclient> ALTER TABLE test RENAME INDEX ind1 TO ind2;

在 test 表上创建索引 ind1，引用 c1、c2 列。

obclient> ALTER TABLE test ADD INDEX ind1 (c1,c2) USING BTREE;

可以通过 SHOW INDEX 语句查看创建的索引。

obclient> SHOW INDEX FROM test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| test  |          0 | PRIMARY  |  1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |     | YES     |
| test  |          1 | ind1     |  1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |     | YES     |
| test  |          1 | ind1     |  2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |     | YES     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set

删除 test 表上的索引 ind2。

obclient> ALTER TABLE test DROP INDEX ind2;

说明

在实际运维场景中，您可以通过以上方式实现索引的原子性变更。

清除分区表 t_log_part_by_range 的分区 M202001 和 M202002 中的全部数据。

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

obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
Query OK, 0 rows affected

为分区表 t_log_part_by_range 添加分区 M202006。

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

obclient> ALTER TABLE t_log_part_by_range ADD PARTITION 
(PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
);

修改表 t1 的并行度为 2。
```
obclient> ALTER TABLE t1 PARALLEL 2;
```