閱讀(1.7k) 書(shū)簽贊(0) 我要糾錯(cuò)

OceanBase ALTER TABLE

2021-06-28 13:40 更新

描述

該語(yǔ)句用來(lái)修改已存在的表的結(jié)構(gòu)，比如：修改表及表屬性、新增列、修改列及屬性、刪除列等。

格式

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)

/*模板化二級(jí)分區(qū)*/
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)

/*非模板化二級(jí)分區(qū)*/
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/*僅非模板化二級(jí)分區(qū)可定義*/]
    
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/*僅非模板化二級(jí)分區(qū)可定義*/] 
    [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/*僅非模板化二級(jí)分區(qū)可定義*/]
    [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

參數(shù)解釋

參數(shù)	描述
ADD [COLUMN]	增加列，目前不支持增加主鍵列。
MODIFY [COLUMN]	修改列屬性。
MODIFY CONSTRAINT	修改約束的狀態(tài)為開(kāi)啟或關(guān)閉，只支持外鍵約束和 CHECK 約束。
DROP [COLUMN]	刪除列，不允許刪除主鍵列或者包含索引的列。
ADD [UNIQUE INDEX]	增加唯一索引。
ADD [INDEX]	增加普通索引
ALTER [INDEX]	修改索引屬性。
ADD [PARTITION]	增加分區(qū)。
DROP [PARTITION]	刪除分區(qū)。
REORGANIZE [PARTITION]	分區(qū)重組。
TRUNCATE [PARTITION]	刪除分區(qū)數(shù)據(jù)。
RENAME [TO] table_name	表重命名。
DROP [TABLEGROUP]	刪除表組。
DROP [CONSTRAINT]	刪除約束。
SET BLOCK_SIZE	設(shè)置 Partition 表 BLOCK 大小。
SET REPLICA_NUM	設(shè)置表的副本數(shù)（指表的副本總數(shù)）。
SET COMPRESSION	設(shè)置表的壓縮方式。
SET USE_BLOOM_FILTER	設(shè)置是否使用 BloomFilter。
SET COMMENT	設(shè)置注釋信息。
SET PROGRESSIVE_MERGE_NUM	設(shè)置漸進(jìn)合并步數(shù)，取值范圍是 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)

增加列前，執(zhí)行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  |
+-------+--------------+------+-----+---------+-------+

執(zhí)行以下命令增加 c3 列：

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

增加列后，執(zhí)行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)

執(zhí)行以下命令刪除 c3 列：

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

刪除列后，執(zhí)行 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)

示例：設(shè)置表格 test 的副本數(shù)，并且增加列 c5。

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

為非模板化二級(jí)分區(qū)表 t_range_range1 添加二級(jí)分區(qū) 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)

刪除非模板化二級(jí)分區(qū)表 t_range_range1 的二級(jí)分區(qū) p2_r1。

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

為非模板化二級(jí)分區(qū)表 t_range_range1 添加一級(jí)分區(qū) p4，需要同時(shí)指定一級(jí)分區(qū)的定義和該分區(qū)下的二級(jí)分區(qū)定義。

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)

為模板化二級(jí)分區(qū)表 t_range_range 添加一級(jí)分區(qū) p3，只需要指定一級(jí)分區(qū)的定義，二級(jí)分區(qū)的定義會(huì)自動(dòng)按照模板填充。

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)

以上內(nèi)容是否對(duì)您有幫助：

← OceanBase ALTER SESSION

OceanBase ALTER TABLEGROUP →

寫(xiě)筆記

我要補(bǔ)充