DDL - ALTER TABLE - 《OceanBase v3.1.1 官方教程》

描述
格式
参数解释
示例

描述

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

格式

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_definition | (column_definition_list)}
    | MODIFY [COLUMN] column_definition
    | MODIFY CONSTRAINT constraint_name { ENABLE | DISABLE }
    | DROP [COLUMN] column_name
    | ADD [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | 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 | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
    | [SET] table_option_list
    | RENAME [TO] table_name
    | DROP TABLEGROUP
    | DROP CONSTRAINT constraint_name
    | {ENABLE | DISABLE} CONSTRAINT constraint_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]
references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]] 
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(column_name_list)
      [subpartition_option] hash_partition_define
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)
/*模板化二级分区*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE 
      (list_subpartition_list)
/*非模板化二级分区*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list)
    | SUBPARTITION BY RANGE (column_name_list) 
    | SUBPARTITION BY LIST (column_name_list) 
subpartition_list:
      (hash_subpartition_list)
    | (range_subpartition_list)
    | (list_subpartition_list)
hash_partition_define:
      PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | (hash_partition_list)
hash_partition_list:
    hash_partition [, hash_partition, ...]
hash_partition:
    partition [partition_name] [subpartition_list/*仅非模板化二级分区可定义*/]
hash_subpartition_define:
      SUBPARTITIONS subpartition_count
    | SUBPARTITION TEMPLATE (hash_subpartition_list)
hash_subpartition_list:
    hash_subpartition [, hash_subpartition, ...]
hash_subpartition:
    subpartition [subpartition_name]
range_partition_list:
    range_partition [, range_partition ...]
range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | (MAXVALUE)} 
    [subpartition_list/*仅非模板化二级分区可定义*/] 
    [ID = num] [physical_attribute_list] [compression]
range_subpartition_list:
    range_subpartition [, range_subpartition ...]
range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]
list_partition_list:
    list_partition [, list_partition] ...
list_partition:
    PARTITION [partition_name] 
    VALUES (DEFAULT|expression_list) 
    [subpartition_list/*仅非模板化二级分区可定义*/]
    [ID num] [physical_attribute_list] [compression]
list_subpartition_list:
    list_subpartition [, list_subpartition] ...
list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]
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	增加列，目前不支持增加主键列。
MODIFY [COLUMN]	修改列属性。
MODIFY CONSTRAINT	修改约束的状态为开启或关闭，只支持外键约束和 CHECK 约束。
DROP [COLUMN]	删除列，不允许删除主键列或者包含索引的列。
ADD [UNIQUE INDEX]	增加唯一索引。
ADD [INDEX]	增加普通索引
ALTER [INDEX]	修改索引属性。
ADD [PARTITION]	增加分区。
DROP {PARTITION \| SUBPARTITION}	删除分区： PARTITION：针对 RANGE、LIST 类型的一级分区，删除指定的分区（如果指定分区下存在二级分区，会同时删除该一级分区下的所有二级分区），包括分区定义和其中的数据，同时对分区上存在的本地索引进行维护。 SUBPARTITION：针对 -RANGE、-LIST 类型的二级分区，删除指定的二级分区，包括分区定义和其中的数据。同时对分区上存在的本地索引进行维护。如果指定 UPDATE GLOBAL INDEXES，则删除分区时会同步更新全局索引；如果不指定，分区表上的全局索引需要处于不可用状态。多个分区名称之间用逗号分隔。
REORGANIZE [PARTITION]	分区重组。
TRUNCATE {PARTITION \| SUBPARTITION}	删除分区数据： PARTITION：针对 RANGE、LIST 类型的一级分区，清除指定分区中的全部数据（如果指定分区下存在二级分区，会同时清除该一级分区下的所有二级分区中的数据），同时对分区上存在的本地索引进行维护。 SUBPARTITION：针对 -RANGE、-LIST 类型的二级分区，清除指定二级分区中的全部数据。同时对分区上存在的本地索引进行维护。如果指定 UPDATE GLOBAL INDEXES，则清除分区数据时会同步更新全局索引；如果不指定，分区表上的全局索引需要处于不可用状态。多个分区名称之间用逗号分隔。
RENAME [TO] table_name	表重命名。
DROP [TABLEGROUP]	删除表组。
DROP [CONSTRAINT]	删除约束。
SET BLOCK_SIZE	设置 Partition 表 BLOCK 大小。
SET REPLICA_NUM	设置表的副本数（指表的副本总数）。
SET COMPRESSION	设置表的压缩方式。
SET USE_BLOOM_FILTER	设置是否使用 BloomFilter。
SET COMMENT	设置注释信息。
SET PROGRESSIVE_MERGE_NUM	设置渐进合并步数，取值范围是 1~64。
parallel_clause	指定表级别的并行度： NOPARALLEL：并行度为 1，默认配置 PARALLEL integer：指定并行度，integer 取值大于等于 1。注意当指定并行度时，优先级关系如下：通过 hint 指定的并行度 > 通过 `ALTER SESSION` 指定的并行度 > 表级别的并行度
{ENABLE \| DISABLE} CONSTRAINT constraint_name	修改约束的状态，支持外键约束或 CHECK 约束。

示例

示例：修改表 t2 中字段 d 的字段类型。

obclient>CREATE TABLE t2(d VARCHAR(3));
Query OK, 0 rows affected (0.04 sec)
obclient>ALTER TABLE t2 MODIFY d CHAR(10);
Query OK, 0 rows affected (0.04 sec)

示例：增加、删除列。

obclient> CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected (0.07 sec)

增加列前，执行DESCRIBE test; 命令查看表信息：

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+

执行以下命令增加 c3 列：

obclient> ALTER TABLE test ADD c3 NUMBER(30);
Query OK, 0 rows affected (0.02 sec)

增加列后，执行DESCRIBE test; 命令查看表信息：

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

执行以下命令删除 c3 列：

obclient> ALTER TABLE test DROP column c3;
Query OK, 0 rows affected (0.02 sec)

删除列后，执行 DESCRIBE test; 命令查看表信息：

obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

示例：设置表格 test 的副本数，并且增加列 c5。

obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD c5 INT;
Query OK, 0 rows affected (0.02 sec)

为非模板化二级分区表 t_range_range1 添加二级分区 p1_r4。

obclient>ALTER TABLE t_range_range1 MODIFY partition p1 ADD subpartition p1_r4 values less than (400);
Query OK, 0 rows affected (0.08 sec)

删除非模板化二级分区表 t_range_range1 的二级分区 p2_r1。

obclient>ALTER TABLE t_range_range1 DROP subpartition p2_r1;
Query OK, 0 rows affected (0.08 sec)

为非模板化二级分区表 t_range_range1 添加一级分区 p4，需要同时指定一级分区的定义和该分区下的二级分区定义。

obclient>ALTER TABLE t_range_range1 ADD partition p4 values less than (500) (
  subpartition p4_r1 values less than (100),
  subpartition p4_r2 values less than (200),
  subpartition p5_r3 values less than (300)
);
Query OK, 0 rows affected (0.08 sec)

为模板化二级分区表 t_range_range 添加一级分区 p3，只需要指定一级分区的定义，二级分区的定义会自动按照模板填充。

obclient>CREATE TABLE t_range_range(c1 INT, c2 INT, PRIMARY KEY(c1,c2))
     PARTITION BY RANGE(c1) SUBPARTITION BY RANGE(c2) SUBPARTITION TEMPLATE 
     (SUBPARTITION p0 VALUES LESS THAN (50),SUBPARTITION p1 VALUES LESS THAN (100))
     (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), 
     PARTITION p2 VALUES LESS THAN (300));
Query OK, 0 rows affected (0.07 sec)
obclient>ALTER TABLE t_range_range ADD PARTITION p3 VALUES LESS THAN (400);
Query OK, 0 rows affected (0.07 sec)

修改表 t1 的并行度为 3。

obclient> ALTER TABLE t1 PARALLEL 3;
Query OK, 0 rows affected (0.06 sec)

修改外键约束的状态

obclient> CREATE TABLE MMS_GROUPUSER (
  "ID" VARCHAR2(254 BYTE) NOT NULL,
  "GROUPID" VARCHAR2(254 BYTE),
  "USERID" VARCHAR2(254 BYTE),
  CONSTRAINT "PK_MMS_GROUPUSER" PRIMARY KEY ("ID"),
  CONSTRAINT "FK_MMS_GROUPUSER_02" FOREIGN KEY ("GROUPID") REFERENCES MMS_GROUPUSER ("ID") ON DELETE CASCADE DISABLE
);
Query OK, 0 rows affected (0.09 sec)
obclient> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints where CONSTRAINT_NAME like 'FK_MMS_GROUPUSE%';
+---------------------+-----------------+---------------+----------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME    | STATUS   |
+---------------------+-----------------+---------------+----------+
| FK_MMS_GROUPUSER_02 | R               | MMS_GROUPUSER | DISABLED |
+---------------------+-----------------+---------------+----------+
1 row in set (0.00 sec)
obclient> ALTER TABLE MMS_GROUPUSER ENABLE CONSTRAINT FK_MMS_GROUPUSER_02;
Query OK, 0 rows affected (0.21 sec)
obclient> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from user_constraints where CONSTRAINT_NAME like 'FK_MMS_GROUPUSE%';
+---------------------+-----------------+---------------+---------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME    | STATUS  |
+---------------------+-----------------+---------------+---------+
| FK_MMS_GROUPUSER_02 | R               | MMS_GROUPUSER | ENABLED |
+---------------------+-----------------+---------------+---------+
1 row in set (0.15 sec)

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

obclient> CREATE TABLE t_log_part_by_range (
    log_id      number NOT NULL 
    , log_value varchar2(50)
    , log_date  date NOT NULL DEFAULT sysdate
) PARTITION BY RANGE(log_date) 
(
    PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
    , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
    , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
    , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
    , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD')) 
    , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
);
Query OK, 0 rows affected (0.08 sec)
obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected (0.05 sec)