后浪云OceanBase教程：OceanBase ALTER TABLE

描述

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

格式

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)}
    | 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 (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 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"

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 [COLUMN]	增加列，目前不支持增加主键列。
MODIFY [COLUMN]	修改列属性。
MODIFY CONSTRAINT	修改约束的状态为开启或关闭，只支持外键约束和 CHECK 约束。
DROP [COLUMN]	删除列，不允许删除主键列或者包含索引的列。
ADD [UNIQUE INDEX]	增加唯一索引。
ADD [INDEX]	增加普通索引
ALTER [INDEX]	修改索引属性。
ADD [PARTITION]	增加分区。
DROP [PARTITION]	删除分区。
REORGANIZE [PARTITION]	分区重组。
TRUNCATE [PARTITION]	删除分区数据。
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。

示例

示例：修改表 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)

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/279202.html<