ALTER TABLE

描述

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

语法

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
    | [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 [ON UPDATE CURRENT_TIMESTAMP]

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
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | parallel_clause
    | READ {ONLY | WRITE}

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]	修改列属性
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 语句报错，或者一些异常情况。
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。 `TABLE_GROUP`：设置表所属的表组。 `BLOCK_SIZE`：设置表的微块大小，默认为 `16384`，即 16 KB，取值范围为 [1024,1048576]。 `COMPRESSION`：设置表的压缩方式，默认为 `None`，表示不压缩。 `AUTO_INCREMENT`：设置表中自增列的下一个取值。 `comment`：设置表的注释信息。 `DUPLICATE_SCOPE`：设置表的复制方式。 `PROGRESSIVE_MERGE_NUM`：设置渐进合并步数，取值范围为 [0,100]。 `parallel_clause`：指定表级别的并行度。 `NOPARALLEL`：并行度为 `1`，默认配置。 `PARALLEL integer`：指定并行度，`integer` 取值大于等于 `1`。
READ {ONLY \| WRITE}	指定表的读写权限。具体如下： `READ ONLY`：表示表只能被读取，不能被修改或删除。注意表级别的 `READ ONLY` 参数对具有 `SUPER` 权限的用户不生效，只对普通用户生效。 `READ WRITE`：默认值，表示表可以被读取、修改和删除。

示例

增加和删除列的示例

创建示例表 tbl1，并执行 DESCRIBE 命令查看表信息。

obclient> CREATE TABLE tbl1 (c1 INT(11) PRIMARY KEY,c2 VARCHAR(50)); 
Query OK, 0 rows affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |    
| c2    | varchar(50)| YES      |        | NULL    |       |   
+-------+------------+----------+--------+---------+-------+

增加 c3 列，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 ADD c3 INTEGER;
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |    
| c2    | varchar(50)| YES      |        | NULL    |       |  
| c3    | int(11)    | YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
3 row affected

删除 c3 列，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 DROP c3;
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |    
| c2    | varchar(50)| YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
2 row affected

将表 tbl1 的字段 c2 改名为 c3，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 CHANGE COLUMN c2 c3 VARCHAR(50);
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |    
| c3    | varchar(50)| YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
2 rows affected

为表 tbl1 添加 c4 列，并将该列设置为表的第一列，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST;
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c4    | int(11)    | YES      |        | NULL    |       |  
| c1    | int(11)    | NO       | PRI    | NULL    |       |    
| c3    | varchar(50)| YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
3 row affected

在 c1 列后添加 c2 列，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1;
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c4    | int(11)    | YES      |        | NULL    |       |  
| c1    | int(11)    | NO       | PRI    | NULL    |       | 
| c2    | int(11)    | YES      |        | NULL    |       |    
| c3    | varchar(50)| YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
4 row affected

在 c4 列前添加 c5 列，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 ADD COLUMN c5 INTEGER BEFORE c4;
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c5    | int(11)    | YES      |        | NULL    |       |  
| c4    | int(11)    | YES      |        | NULL    |       |  
| c1    | int(11)    | NO       | PRI    | NULL    |       | 
| c2    | int(11)    | YES      |        | NULL    |       |    
| c3    | varchar(50)| YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
5 row affected

在 c4 列前添加 c5 列，并执行 DESCRIBE 命令查看表信息。

obclient> ALTER TABLE tbl1 ADD COLUMN c5 INTEGER BEFORE c4;
Query OK, 1 row affected

obclient> DESCRIBE tbl1; 
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra | 
+-------+------------+----------+--------+---------+-------+
| c5    | int(11)    | YES      |        | NULL    |       |  
| c4    | int(11)    | YES      |        | NULL    |       |  
| c1    | int(11)    | NO       | PRI    | NULL    |       | 
| c2    | int(11)    | YES      |        | NULL    |       |    
| c3    | varchar(50)| YES      |        | NULL    |       |  
+-------+------------+----------+--------+---------+-------+
5 row affected

为表 tbl2 添加外键约束 fk1。

obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50)); 
Query OK, 0 row affected
obclient> CREATE TABLE tbl3(c1 INT(11) PRIMARY KEY,c2 INT(50));
Query OK, 0 row affected
obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1);
Query OK, 0 row affected

删除 tbl3 表的外键约束 fk1。

obclient> ALTER TABLE tbl2 DROP FOREIGN KEY fk1;
Query OK, 0 row affected

索引操作

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

obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50));
Query OK, 0 row affected

obclient> CREATE INDEX ind1 ON tbl2 (c2 ASC);
Query OK, 0 row affected
obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
Query OK, 0 row affected

在 tbl2 表上创建索引 ind3，引用 c1、c2 列。

obclient> ALTER TABLE tbl2 ADD INDEX ind3 (c1,c2) USING BTREE;
Query OK, 0 row affected

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

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

删除 tbl2 表上的索引 ind2。

obclient> ALTER TABLE tbl2 DROP INDEX ind2;
Query OK, 0 row affected

说明

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

分区示例

清除分区表 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'))
         );
Query OK, 0 rows affected

并行度示例

修改表 tbl1 的并行度为 2。

obclient> ALTER TABLE tbl3 PARALLEL 2;
Query OK, 0 rows affected

描述

语法

参数解释

注意

示例

增加和删除列的示例

索引操作

分区示例

并行度示例