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
          [ opt_position_column ]
    | 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 PRIMARY KEY (column_name)
    | ADD CONSTRAINT [constraint_name] CHECK (expression)[[NOT] ENFORCED]
    | ADD PARTITION (range_partition_list)
    | ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option]
    | ADD FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...]) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)] [FTS_INDEX_TYPE [=] {MATCH | PHRASE_MATCH}] [LOCAL]
    | ADD SEARCH INDEX [index_name] (search_index_column [, search_index_column ...])
    | ADD COLUMN GROUP([all columns, ]each column) [DELAYED]
    | ALTER [COLUMN] column_name {
          SET DEFAULT const_value
        | DROP DEFAULT
      }
    | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
    | ALTER INDEX index_name [VISIBLE | INVISIBLE] [STORAGE_CACHE_POLICY = {"hot"|"auto"|"none"}]
    | ALTER INDEX index_name
          [VISIBLE | INVISIBLE]
    | CHANGE [COLUMN] column_name column_definition
    | DROP [COLUMN] column_name [, DROP COLUMN column_name ...]
    | DROP {PARTITION | SUBPARTITION} partition_name_list
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
    | DROP CHECK constraint_name
    | DROP COLUMN GROUP([all columns, ]each column)
    | DROP CLUSTERING KEY
    | MODIFY [COLUMN] column_definition
    | RENAME COLUMN old_col_name [TO|AS] new_col_name
    | RENAME [TO|AS] table_name
    | RENAME {INDEX | KEY} old_index_name [TO|AS] new_index_name
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list
    | EXCHANGE PARTITION partition_name WITH TABLE origin_table_name WITHOUT VALIDATION
    | REORGANIZE PARTITION partition_name INTO (split_range_partition_list)
    | PARTITION BY {RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')] [range_partition_list]
                   | partition_option}
    | MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)
    | ALTER {PARTITION | SUBPARTITION} partition_name STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}
    | FORCE
    | REMOVE TTL

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 string_value] [SKIP_INDEX(skip_index_option_list)] [ opt_position_column ]

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

opt_position_column:
    FIRST | BEFORE | AFTER column_name

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 NULL | NO ACTION | SET DEFAULT}

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

search_index_column:
    column_name [WITH (search_col_option_list)]

search_col_option_list:
    [INCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| EXCLUDE_PATHS = ('path1'[, 'path2']...)]
    [| INCLUDE_TYPES = (type1[, type2]...)]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
     [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | COMMENT string_value
    | STORAGE_CACHE_POLICY (storage_cache_policy_option)

index_column_group_option:
    WITH COLUMN GROUP([all columns, ]each column)

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

parser_properties:
    min_token_size = int_value
    | max_token_size = int_value
    | ngram_token_size = int_value
    | ik_mode = 'char_value'
    | min_ngram_size = int_value
    | max_ngram_size = int_value

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | COMMENT string_value
    | parallel_clause
    | TABLE_MODE [=] 'table_mode_value'
    | AUTO_INCREMENT_CACHE_SIZE [=] INT_VALUE
    | READ {ONLY | WRITE}
    | DUPLICATE_SCOPE= 'none | cluster'
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SEMISTRUCT_ENCODING_TYPE [=] 'encoding' # 从 V4.4.1 版本开始废弃，使用 SEMISTRUCT_PROPERTIES 替代。
    | SEMISTRUCT_PROPERTIES [=] (encoding_type=encoding|none[,freq_threshold=INT_VALUE])
    | STORAGE_CACHE_POLICY (storage_cache_policy_option)
    | CLUSTER BY (column_name_list)
    | HMS_CATALOG_NAME [=] string_value
    | COLUMN_NAME_CASE_SENSITIVE [=] {True | False}
    | DELTA_FORMAT [=] 'flat | encoding'
    | SKIP_INDEX_LEVEL [=] {1 | 0}
    | TTL col_name + INTERVAL interval_num ttl_unit BY COMPACTION

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}

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)

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "cold" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit
    | MIXED_RETENTION = intnum retention_time_unit
    | GRANULARITY = {"partition" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

split_range_partition_list:
    PARTITION split_partition_name VALUES LESS THAN (value)
    [, PARTITION split_partition_name VALUES LESS THAN (value) ...]
     , PARTITION split_partition_name VALUES LESS THAN (source_value)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}
    [STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}
    [STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]

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_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
        [, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
    | {SUBPARTITION subpartition_name VALUES IN list_partition_expr
        [, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}

参数说明

table_mode_value

• NORMAL：默认值，表示正常。在该模式下，数据转储后触发合并的概率极低。
• QUEUING：在该模式下，数据转储后触发合并的概率低。
• MODERATE：表示适度。在该模式下，数据转储后触发合并的概率为中等。
• SUPER：表示超级。在该模式下，数据转储后触发合并的概率高。
• EXTREME：表示极端。在该模式下，转储后触发合并的概率较高。

更多有关合并的信息，请参见 自适应合并。

tokenizer_option

• SPACE：默认值，表示按空格进行分词。可以指定以下属性：

  属性	取值范围
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM：表示基于 N-Gram（中文）的分词方式。可以指定以下属性：

  属性	取值范围
  ngram_token_size	[1, 10]

• NGRAM2：表示将文本拆成 min_ngram_size～max_ngram_size 范围的连续字符。可以指定以下属性：

  属性	取值范围
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

• BENG：基于 Beng （基础英文）的分词方式。可以指定以下属性：

  属性	取值范围
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK：表示基于 IK（中文）的分词方式。当前仅支持 utf-8 字符集。可以指定以下属性：

  属性	取值范围
  ik_mode	smart max_word

可以使用 TOKENIZE 函数查看文本按照指定的分词器及 Json 形式参数的分词结果。

parser_properties

• min_token_size：表示最小分词长度，默认值为 3，取值范围是 1 到 16。

• max_token_size：表示最大分词长度，默认值为 84，取值范围是 10 到 84。

• ngram_token_size：表示 NGRAM 的分词长度，只有 NGRAM 分词器有效，默认值为 2，取值范围是 1 到 10。

• ik_mode: 表示 IK 分词器的分词模式。取值如下：

  • smart：默认值，表示词典中的词汇会被用来提高分词的准确性，词典中的词汇边界会被优先考虑，从而可能减少不必要的扩展。
  • max_word：表示在词典中定义的词汇会被识别出来，但不会影响分词的最大化扩展。即使词典中有定义，max_word 模式仍然会尝试将文本切分成更多的词汇。

• min_ngram_size：表示最小分词长度，的取值范围为[1,16]。

• max_ngram_size: 表示最大分词长度，的取值范围为[1,16]。

示例如下：

1. 创建表 tbl1。

   CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);
   

2. 向表 tbl1 添加全文索引 fidx。

   ALTER TABLE tbl1 ADD FULLTEXT INDEX fidx(col2) WITH PARSER ik PARSER_PROPERTIES=(ik_mode='max_word');
   

dynamic_partition_policy_option

• ENABLE = {true | false}：表示是否启用动态分区管理。取值如下：

  • true：默认值，表示启用动态分区管理。
  • false：表示禁用动态分区管理。

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：表示预创建时间。调度一次动态分区管理，会预创建分区使得 最大分区上界 > now() + precreate_time。取值如下：

  • -1：默认值，表示不预创建分区。
  • 0：表示仅预创建当前分区。
  • n {hour | day | week | month | year}：表示预创建对应时间跨度的分区。例如，3 hour 表示预创建 3 小时内的分区。

  说明

  • 需要预创建多个分区时，分区边界的间隔为 TIME_UNIT。
  • 首个预创建的分区边界为现存最大分区边界按 TIME_UNIT 向上取整。

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：可选项，表示分区过期时间。调度一次动态分区管理，会删除所有 分区上界 < now() - expire_time 的过期分区。可修改，取值如下：

  • -1：默认值，表示分区永不过期。
  • 0：表示除当前分区外，之前的所有分区均过期。
  • n {hour | day | week | month | year}：表示分区过期时间。例如，1 day 表示分区过期时间为 1 天。

更多有关修改动态分区表的信息，参见 修改动态分区表。

示例如下：

ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
    ENABLE = true,
    PRECREATE_TIME = '1 day',
    EXPIRE_TIME = '-1'
);

storage_cache_policy

storage_cache_policy_option 中不同属性以 Key-Value 格式定义。各个属性的语意如下：

• GLOBAL = {"hot" | "auto" | "cold" | "none"}：用于指定全表数据的冷热存储策略，取值如下：

  • hot：表示指定表的所有数据都为热数据，在缓存盘空间足够的情况下，表下所有数据将会缓存到本地云盘。

  • auto：表示指定表的热数据为系统自动识别。

  • cold：表示指定表数据为冷数据，不会缓存到本地缓存盘，对于已经持久化到缓存的数据会主动触发缓存淘汰，后续访问的表数据可以存储在宏块内存缓存，不会持久化到宏块缓存和微块缓存。

  • none：表示该索引的策略跟随主表 STORAGE_CACHE_POLICY 取值。

    注意

    none 属性只能给索引使用。

• timeline_strategy_list：表示时间轴策略参数列表，各参数间使用英文逗号分隔。时间轴策略支持通过时间来判断分区/宏块数据冷热存储的机制，系统根据配置的策略，自动调整本地缓存盘上的数据。

  说明

  时间轴：按照 Range 分区定义的分区范围取值进行热数据缓存，当满足一定时间条件时，该分区的数据被判定为热数据。使用时间轴策略时，需要注意以下事项：

  • 仅支持 Range 分区表使用（Range 分区为一级或者二级都可以），因为需要时间来确定是否过期，但不能是双 Range 分区，会因为无法确定以哪个 Range 分区时间为准。
  • 使用时间轴时，分区表达中只能有列名，不支持表达式转换。例如 PARTITION BY RANGE COLUMNS(expr(col3))。
  • BOUNDARY_COLUMN 必须为分区键，如果分区键有多列，BOUNDARY_COLUMN 需为第一列，用来判断分区是否过期。

• BOUNDARY_COLUMN = column_name：用于判定热数据的列。支持整型（BIGINT 或者 INT 类型，格式为 Unix 时间戳）和时间类型（TIMESTAMP、DATE、DATETIME 或者 YEAR 类型）。

  • 如果 BOUNDARY_COLUMN 类型为整型时，表的分区方式支持 Range/Range Columns 分区类型。
  • 如果 BOUNDARY_COLUMN 类型为时间类型时，表的分区方式只能使用 Range Columns 分区类型。

• BOUNDARY_COLUMN_UNIT = {"s" | "ms"}：用于指定参数 BOUNDARY_COLUMN 的时间单位。仅支持 BOUNDARY_COLUMN 为整型时设置此参数，整型数值用作时间戳时，需要给定时间戳单位，否则可能会导致错解析出来错误的时间。取值如下：

  • 当分区列为 INT 类型时，BOUNDARY_COLUMN_UNIT 取值只能为 s。

  • 当分区列为 BIGINT 类型时，BOUNDARY_COLUMN_UNIT 取值可以是 s 或者 ms。

    注意

    如果格式非 Unix 时间戳，将不能正确识别 INT 类型表示的时间。

• HOT_RETENTION = intnum retention_time_unit：用于配置热数据的时间范围。

  • intnum：表示整数。
  • retention_time_unit：表示时间单位，取值如下：
    • YEAR：表示年。
    • MONTH：表示月。
    • WEEK：表示周。
    • DAY：表示天。
    • HOUR：表示小时。
    • MINUTE：表示分钟。

  注意

  参数 HOT_RETENTION 和 MIXED_RETENTION 在时间轴语法中至少要设置一个。

• MIXED_RETENTION = intnum retention_time_unit：

  • 如果设置 MIXED_RETENTION，则表示距离 HOT_RETENTION + MIXED_RETENTION 的时间范围后的数据都是 cold 数据。
  • 如果不设置 MIXED_RETENTION，则到达 HOT_RETENTION 之后，就一直为 auto 数据。
  • 如果没有设置 HOT_RETENTION, 则表示 [0, MIXED_RETENTION] 的时间范围为 auto 数据，MIXED_RETENTION 之后的为 cold 数据。

• GRANULARITY = {"partition" | "block"}：可选项，用于指定数据冷热存储的操作粒度，取值如下：

  • partition：表示缓存数据的策略按照分区粒度处理。

  • block：表示缓存数据的策略按照宏块粒度处理。

    说明

    在没有分区表的情况下，需要手动指定一段时间内数据为热点数据时，可以指定 block。详细使用宏块冷热策略的介绍，参见 创建宏块级冷热存储策略。

partition_storage_cache_policy

STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}：用于指定分区的数据的冷热存储策略，取值如下：

• hot：表示指定分区为热存储，在缓存盘空间足够的情况下，分区下所有数据将会缓存到本地云盘。
• auto：表示指定分区热数据为系统自动识别。
• cold：表示指定分区数据为冷数据，不会缓存到本地缓存盘，对于已经持久化到缓存的数据会主动触发缓存淘汰，后续访问的表数据可以存储在宏块内存缓存，不会持久化到宏块缓存和微块缓存。
• none：默认值，表示该分区的策略跟随主表 STORAGE_CACHE_POLICY 取值。

示例

增加和删除列的操作

创建示例表 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 INT;
  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 INT 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。当 UPDATE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

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

• 删除 tbl3 表的外键约束 fk1。

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

• 删除 tbl2 表的主键。

  obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
  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);
  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
  

  说明

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

• 在 t1_func 上添加 3 个函数索引，其中一个名字是 i2，另外两个由系统自动生成的名称，格式为 functional_index 前缀加编号。

  obclient> ALTER TABLE t1_func ADD INDEX ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX ((c1+1));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX i2 ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> SHOW CREATE TABLE t1_func;
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | t1_func | CREATE TABLE `t1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `i1` (((`c1` + `c2`) < 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index_2` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `i2` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• 为表创建列存索引。

  1. 使用下面 SQL 语句创建表 tbl3。

     CREATE TABLE tbl3 (col1 INT, col2 VARCHAR(50));
     

  2. 在 tbl3 表上创建列存索引 idx1_tbl3，引用 col1 列。

     ALTER TABLE tbl3 ADD INDEX idx1_tbl3 (col1) WITH COLUMN GROUP(each column);
     

• 修改索引不可见。

  说明

  当索引设置为不可见后，可以在表结构中进行看到 /*!80000 INVISIBLE */ 标记。

  1. 创建表 tbl4。

     CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT, col3 INT);
     

  2. 创建索引 idx1_tbl4。

     CREATE INDEX idx1_tbl4 ON tbl4(col2);
     

  3. 修改索引 idx1_tbl4 不可见。

     ALTER TABLE tbl4 ALTER INDEX idx1_tbl4 INVISIBLE;
     

  4. 再次查看表 tbl4 结构。

     SHOW CREATE TABLE tbl4;
     

     返回结果如下：

     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                              |
     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | tbl4  | CREATE TABLE `tbl4` (
       `col1` int(11) NOT NULL,
       `col2` int(11) DEFAULT NULL,
       `col3` int(11) DEFAULT NULL,
       PRIMARY KEY (`col1`),
       KEY `idx1_tbl4` (`col2`) BLOCK_SIZE 16384 LOCAL /*!80000 INVISIBLE */
     ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column) |
     +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

给已有表增加索引时，指定索引的表级冷热存储策略

语法格式如下：

ALTER TABLE table_name ADD INDEX index_name(column_name)
    STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

相关参数说明

支持对索引表配置冷热存储策略：

• 对于局部索引，可以指定表级 STORAGE_CACHE_POLICY。如果未指定表级 STORAGE_CACHE_POLICY，则默认为 none，表示使用数据表的 STORAGE_CACHE_POLICY 配置，局部索引分区的冷热存储策略则跟随数据表的分区规则。

• 对于全局索引，除了指定表级的 STORAGE_CACHE_POLICY，如果索引表有分区的话，还可以指定索引表分区级的 STORAGE_CACHE_POLICY。全局索引如果未指定表级 STORAGE_CACHE_POLICY，则默认为 none。

  说明

  在 V4.4.1 及其之后的版本中，全局索引如果未指定表级 STORAGE_CACHE_POLICY，如果主表为 Global 策略，就默认跟随主表的 STORAGE_CACHE_POLICY，如果主表为时间轴策略，则默认为 hot 策略。

设置表级别的 STORAGE_CACHE_POLICY 时，参数 storage_cache_policy_option 中不同属性以 Key-Value 格式定义。各个属性的语意如下：

• GLOBAL = {"hot" | "auto" | "cold" | "none"}：用于指定索引的数据的冷热存储策略，取值如下：

  • hot：表示指定索引为热存储，在缓存盘空间足够的情况下，索引表下所有数据将会缓存到本地缓存盘。
  • auto：表示指定索引表热数据为系统自动识别。
  • cold：表示指定索引表数据为冷数据，不会缓存到本地缓存盘，对于已经持久化到缓存的数据会主动触发缓存淘汰，后续访问的表数据可以存储在宏块内存缓存，不会持久化到宏块缓存和微块缓存。
  • none：默认值，表示该索引的策略跟随主表 STORAGE_CACHE_POLICY 取值。

• timeline_strategy_list：表示时间轴策略参数列表，各参数间使用英文逗号分隔。时间轴策略支持通过时间来判断分区/宏块数据冷热存储的机制，系统根据配置的策略，自动调整本地缓存盘上的数据。

  说明

  时间轴：按照 Range 分区定义的分区范围取值进行热数据缓存，当满足一定时间条件时，该分区的数据被判定为热数据。使用时间轴策略时，需要注意以下事项：

  • 仅支持 Range 分区表使用（Range 分区为一级或者二级都可以），因为需要时间来确定是否过期，但不能是双 Range 分区，会因为无法确定以哪个 Range 分区时间为准。
  • 使用时间轴时，分区表达中只能有列名，不支持表达式转换。例如 PARTITION BY RANGE COLUMNS(expr(col3))。
  • BOUNDARY_COLUMN 必须为分区键，如果分区键有多列，BOUNDARY_COLUMN 需为第一列，用来判断分区是否过期。

  • BOUNDARY_COLUMN = column_name：用于判定热数据的列。支持整型（BIGINT 或者 INT 类型，格式为 Unix 时间戳）和时间类型（TIMESTAMP、DATE、DATETIME 或者 YEAR 类型）。

    • 如果 BOUNDARY_COLUMN 类型为整型时，表的分区方式支持 Range/Range Columns 分区类型。
    • 如果 BOUNDARY_COLUMN 类型为时间类型时，表的分区方式只能使用 Range Columns 分区类型。

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}：用于指定参数 BOUNDARY_COLUMN 的时间单位。仅支持 BOUNDARY_COLUMN 为整型时设置此参数，整型数值用作时间戳时，需要给定时间戳单位，否则可能会导致错解析出来错误的时间。取值如下：

    • 当分区列为 INT 类型时，BOUNDARY_COLUMN_UNIT 取值只能为 s。
    • 当分区列为 BIGINT 类型时，BOUNDARY_COLUMN_UNIT 取值可以是 s 或者 ms。

  • HOT_RETENTION = intnum retention_time_unit：用于配置热数据的时间范围。

    • intnum：表示整数。

    • retention_time_unit：表示时间单位，取值如下：

      • YEAR：表示年。
      • MONTH：表示月。
      • WEEK：表示周。
      • DAY：表示天。
      • HOUR：表示小时。
      • MINUTE：表示分钟。

示例

• 创建索引时，指定索引的表级 STORAGE_CACHE_POLICY：

  1. 创建表 tbl3。

     CREATE TABLE tbl3 (col1 INT, col2 INT, col3 INT);
     

  2. 创建索引 idx1_tbl3 并指定索引表的数据为热数据。

     CREATE INDEX idx1_tbl3 ON tbl3 (col1) LOCAL 
         STORAGE_CACHE_POLICY (GLOBAL = "hot");
     

• 创建表时创建索引，指定索引表的数据为热数据。

  CREATE TABLE tbl4 (col1 INT, col2 INT,
      INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));
  

• 给已有表增加索引时，指定索引表的数据为热数据。

  ALTER TABLE tbl4 ADD INDEX idx3(col1, col2) GLOBAL 
      STORAGE_CACHE_POLICY (GLOBAL = "hot");
  

分区操作

• 清除分区表 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'))
           );
  

修改索引表的冷热存储策略

可以使用 ALTER TABLE 语句修改已有索引的冷热存储策略（STORAGE_CACHE_POLICY），语法格式如下：

ALTER TABLE table_name ALTER INDEX STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    | BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

相关参数说明

• GLOBAL = {"hot" | "auto" | "none"}：用于指定索引的数据的冷热存储策略，取值如下：

  • hot：表示指定索引为热存储，在缓存盘空间足够的情况下，索引表下所有数据将会缓存到本地缓存盘。
  • auto：表示指定索引表热数据为系统自动识别。
  • none：默认值，表示该索引的策略跟随主表 STORAGE_CACHE_POLICY 取值。

• timeline_strategy_list：表示时间轴策略参数列表，各参数间使用英文逗号分隔。时间轴策略支持通过时间来判断分区/宏块数据冷热存储的机制，系统根据配置的策略，自动调整本地缓存盘上的数据。

  说明

  时间轴：按照 Range 分区定义的分区范围取值进行热数据缓存，当满足一定时间条件时，该分区的数据被判定为热数据。使用时间轴策略时，需要注意以下事项：

  • 仅支持 Range 分区表使用（Range 分区为一级或者二级都可以），因为需要时间来确定是否过期，但不能是双 Range 分区，会因为无法确定以哪个 Range 分区时间为准。
  • 使用时间轴时，分区表达中只能有列名，不支持表达式转换。例如 PARTITION BY RANGE COLUMNS(expr(col3))。
  • BOUNDARY_COLUMN 必须为分区键，如果分区键有多列，BOUNDARY_COLUMN 需为第一列，用来判断分区是否过期。

  • BOUNDARY_COLUMN = column_name：用于判定热数据的列。支持整型（BIGINT 或者 INT 类型，格式为 Unix 时间戳）和时间类型（TIMESTAMP、DATE、DATETIME 或者 YEAR 类型）。

    • 如果 BOUNDARY_COLUMN 类型为整型时，表的分区方式支持 Range/Range Columns 分区类型。
    • 如果 BOUNDARY_COLUMN 类型为时间类型时，表的分区方式只能使用 Range Columns 分区类型。

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}：用于指定参数 BOUNDARY_COLUMN 的时间单位。仅支持 BOUNDARY_COLUMN 为整型时设置此参数，整型数值用作时间戳时，需要给定时间戳单位，否则可能会导致错解析出来错误的时间。取值如下：

    • 当分区列为 INT 类型时，BOUNDARY_COLUMN_UNIT 取值只能为 s。
    • 当分区列为 BIGINT 类型时，BOUNDARY_COLUMN_UNIT 取值可以是 s 或者 ms。

  • HOT_RETENTION = intnum retention_time_unit：用于配置热数据的时间范围。

    • intnum：表示整数。

    • retention_time_unit：表示时间单位，取值如下：

      • YEAR：表示年。
      • MONTH：表示月。
      • WEEK：表示周。
      • DAY：表示天。
      • HOUR：表示小时。
      • MINUTE：表示分钟。

示例

修改索引 idx2 的热数据为系统自动识别。

ALTER TABLE test_tbl2 ALTER INDEX idx2
    STORAGE_CACHE_POLICY (GLOBAL = "auto");

修改并行度

• 修改表 tbl3 的并行度为 2。

  obclient> ALTER TABLE tbl3 PARALLEL 2;
  

  除了可以使用 ALTER TABLE table_name PARALLEL int; 修改表的并行度之外，也可以通过 PARALLEL Hint 来实现，语法为 alter /*+ parallel(int) */ table。

列类型相关操作

• 修改表 tbl4 的普通列 col1 为自增列。

  obclient> CREATE TABLE tbl4 (col1 BIGINT(10) NOT NULL,col2 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 MODIFY col1 BIGINT(10) AUTO_INCREMENT;
  Query OK, 0 rows affected
  

• 为表 tbl4 增加主键 col1，之后更改主键为 col2。

  obclient> ALTER TABLE tbl4 ADD PRIMARY KEY (col1);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 DROP PRIMARY KEY,ADD PRIMARY KEY (`col2`);
  Query OK, 0 rows affected
  

• 添加 CHECK 约束。

  obclient> CREATE TABLE tbl7(col1 VARCHAR(10),col2 VARCHAR(10));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl7 ADD CONSTRAINT my_check CHECK (col1> col2) ;
  Query OK, 0 rows affected
  

• 删除表 tbl7 的 CHECK 约束。

  obclient> ALTER TABLE tbl7 DROP CHECK my_check;
  Query OK, 0 rows affected
  

• 修改列类型为 NOT NULL。

  obclient> CREATE TABLE dept(
      deptno           NUMBER(2,0),
      dname            VARCHAR(14),
      location         VARCHAR(13),
      CONSTRAINT pk_dept PRIMARY KEY(deptno)
  );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE emp(
      empno         NUMBER(4,0) NOT NULL,
      empname       VARCHAR(10) NOT NULL,
      job           VARCHAR(9) NOT NULL,
      mgr           NUMBER(4,0) NOT NULL,
      hiredate      DATE NOT NULL,
      sal           NUMBER(7,2) DEFAULT NULL,
      comm          NUMBER(7,2) DEFAULT NULL,
      deptno        NUMBER(2,0) DEFAULT NULL,
      CONSTRAINT PK_emp PRIMARY KEY (empno),
      CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE emp MODIFY deptno NUMBER(2,0) DEFAULT '12' NOT NULL;
  Query OK, 0 rows affected
  

同时修改列名和增加 NOT NULL 列约束

obclient> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (2.421 sec)

两列同时添加 NOT NULL 约束

执行如下命令，同时给C1、c2 列添加 NOT NULL 约束：

obclient> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (2.605 sec)

更改含前缀索引的列类型

1. 执行如下命令，创建名为 test_alter5 的表，包含 c1 的列，其数据类型为 CHAR(20) 并在 c1 列上创建了一个名为 idx_c1 的前缀索引：

   obclient> CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));
   

2. 执行如下命令，修改 c1 列数据类型为 varchar(20)：

   obclient> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
   

更改外键约束规则

1. 执行如下命令，创建表 t1 并为整数列 c1 添加唯一性约束:

   obclient> CREATE TABLE t1(c1 int unique key);
   

2. 执行如下命令，创建一个名为 t2 的表，包含一个整数列 c1，并定义一个外键约束：

   obclient> CREATE TABLE t2(c1 int, CONSTRAINT fk_cst_test FOREIGN KEY (c1) REFERENCES t1(c1) on update set null on delete no action);
   

3. 执行如下命令，查询系统表 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS，获取外键约束 fk_cst_test 的更新和删除规则：

   obclient> SELECT update_rule, delete_rule FROM information_schema.referential_constraints WHERE CONSTRAINT_NAME = 'fk_cst_test';
   

   返回结果如下：

   +-------------+-------------+
   | update_rule | delete_rule |
   +-------------+-------------+
   | SET NULL    | NO ACTION   |
   +-------------+-------------+
   

   • UPDATE_RULE：返回 SET NULL（表示更新时外键设为 NULL）。
   • DELETE_RULE：返回 NO ACTION（表示删除时检查约束，可能阻止操作）。

重命名列操作

• RENAME COLUMN 不改变列定义，仅修改列名。如果目标名称在表中已经存在，那么 RENAME COLUMN 执行会报错，但是重命名为原名称则不会报错。

  obclient> CREATE TABLE tbl8 (a INT, b INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO b;
  ERROR 1060 (42S21): Duplicate column name 'b'
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO a;
  Query OK, 0 rows affected
  

• 如果重命名的列上建有索引，RENAME COLUMN 可以正常执行，索引定义会自动级联修改。

  obclient> CREATE TABLE tbl9 (a INT, b INT, index idx_a(a));
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | a           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | c           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  

• 如果重命名列被前缀索引引用，RENAME COLUMN 可以正常执行，前缀索引支持级联修改。

  DROP TABLE tbl9;
  obclient> CREATE TABLE tbl9 (c1 INT PRIMARY KEY, c2 BLOB, c3 INT, INDEX i1 (c2(10)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN c2 TO c2_, RENAME COLUMN c1 TO c2_1;
  DESC tbl9;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          0 | PRIMARY  |            1 | c2_1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl9  |          1 | i1       |            1 | c2_         | A         |        NULL | 10       | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  2 rows in set
  

• 如果重命名的列上建有外键约束，RENAME COLUMN 可以正常执行，外键约束会自动级联修改。

  obclient> CREATE TABLE tbl10 (a INT PRIMARY KEY);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl11(b INT, FOREIGN KEY (b) REFERENCES tbl10(a));
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`a`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE =  16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  
  obclient> ALTER TABLE tbl10 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`c`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

OceanBase 数据库在以下场景，不支持修改或者不会自动级联修改：

• 重命名的列被生成列表达式引用，不支持修改列名，执行会报错。

  obclient> CREATE TABLE tbl12(a INT, b INT AS (a + 1), c INT, d INT, CONSTRAINT d_check CHECK(d > 0)) PARTITION BY HASH(c + 1) PARTITIONS 2;
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN a TO e;
  ERROR 3108 (HY000): Column 'a' has a generated column dependency
  

• 重命名的列被分区表达式引用，不支持修改列名，执行会报错。

  obclient> ALTER TABLE tbl12 RENAME COLUMN c TO e;
  ERROR 3855 (HY000): Column 'c' has a partitioning function dependency and cannot be dropped or renamed.
  

• 重命名的列被 CHECK 约束引用，不支持修改列名，执行会报错。

  obclient> ALTER TABLE tbl12 RENAME COLUMN d TO e;
  ERROR 3959 (HY000): Check constraint 'd_check' uses column 'd', hence column cannot be dropped or renamed.
  

• 重命名的列被函数索引引用，不支持修改列名，执行会报错。

  DROP TABLE IF EXISTS tbl12;
  obclient> CREATE TABLE tbl12(i INT, INDEX ((i+1)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN i TO j;
  ERROR 3837 (HY000): Column 'i' has a functional index dependency and cannot be dropped or renamed.
  

• 重命名的列被视图引用，RENAME COLUMN 执行成功，查询视图会报错，需要用户手动修改视图定义。

  obclient> CREATE TABLE tbl13(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE VIEW v1 AS SELECT a + 1 FROM tbl13;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  Empty set
  
  obclient> ALTER TABLE tbl13 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  ERROR 1356 (42S22): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  

• 重命名的列被存储过程引用，RENAME COLUMN 执行成功，CALL Procedure 报错，需要用户手动修改。

  obclient> CREATE TABLE tbl14(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE PROCEDURE proc() SELECT a + 1 FROM tbl14;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  Empty set
  
  obclient> ALTER TABLE tbl14 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  ERROR 1054 (42S22): Unknown column 'a' in 'field list'
  

修改表的列存属性

1. 使用下面 SQL 语句创建表 tbl1。

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
   

2. 将表 tbl1 的变更为行存列存冗余表，然后再删除行存列存冗余属性。

   ALTER TABLE tbl1 ADD COLUMN GROUP(all columns, each column);
   

   ALTER TABLE tbl1 DROP COLUMN GROUP(all columns, each column);
   

3. 将表 tbl1 的变更为列存表，然后再删除列存属性。

   ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
   

   ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
   

修改列的 Skip Index 属性

使用下面 SQL 语句创建表 test_skidx。

CREATE TABLE test_skidx(
  col1 INT SKIP_INDEX(MIN_MAX, SUM),
  col2 FLOAT SKIP_INDEX(MIN_MAX),
  col3 VARCHAR(1024) SKIP_INDEX(MIN_MAX),
  col4 CHAR(10)
  );

• 修改表 test_skidx 中列 col2 的 Skip Index 属性为 SUM Skip Index 类型。

  ALTER TABLE test_skidx MODIFY COLUMN col2 FLOAT SKIP_INDEX(SUM);
  

• 建表后新增列的 Skip Index 属性。为表 test_skidx 中列 col4 增加的 MIN_MAX Skip Index 类型。

  ALTER TABLE test_skidx MODIFY COLUMN col4 CHAR(10) SKIP_INDEX(MIN_MAX);
  

• 建表后删除列的 Skip Index 属性。删除表 test_skidx 中列 col1 的 Skip Index 属性。

  ALTER TABLE test_skidx MODIFY COLUMN col1 INT SKIP_INDEX();
  

  或

  ALTER TABLE test_skidx MODIFY COLUMN col1 INT;
  

修改表属性

关闭持久化宏块级别 bloom filter 的表 tb。

ALTER TABLE tb SET enable_macro_block_bloom_filter = False;

修改表级别 STORAGE_CACHE_POLICY

• 修改表 tbl1 的热点缓存由系统自动识别。

  ALTER TABLE tbl1 SET STORAGE_CACHE_POLICY (GLOBAL = "auto");
  

• 修改表 tbl2 中通过 col3 判断的距离当前时间 2 个月内的分区数据是热数据。

  ALTER TABLE tbl2 SET STORAGE_CACHE_POLICY (BOUNDARY_COLUMN = col3, HOT_RETENTION = 2 MONTH); 
  

修改分区级别 STORAGE_CACHE_POLICY

• 向表 tbl3 中新增分区 p2，分区 p2 中需要缓存到本地盘的热数据由系统自动识别。

  ALTER TABLE tbl3 ADD PARTITION (PARTITION p2 VALUES LESS THAN(300) STORAGE_CACHE_POLICY = "auto");
  

• 修改表 tbl3 中已有的一级分区 p1 中的数据为热数据。

  ALTER TABLE tbl3 ALTER PARTITION p1 STORAGE_CACHE_POLICY = 'hot';
  

• 修改表 tbl4 中二级分区 sp3 中需要缓存到本地盘的数据由系统自动识别。

  ALTER TABLE tbl4 ALTER SUBPARTITON sp3 STORAGE_CACHE_POLICY = 'auto';
  

修改或删除表的 TTL 策略

• 修改表的 TTL 策略。例如：将数据过期时间修改为 1 小时。

  obclient(root@mysql001)[infotest]> ALTER TABLE ttl_tbl1 SET TTL ORA_ROWSCN + INTERVAL 1 HOUR BY COMPACTION;
  

• 删除表的 TTL 策略。

  obclient(root@mysql001)[infotest]> ALTER TABLE ttl_tbl1 SET REMOVE TTL;
  

相关文档

更改表