跳到主要内容

ALTER TABLE

描述​

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

语法​

alter_table_stmt:
    ALTER TABLE [TEMPORARY] 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] [algorithm = algorithm_value] [lock = lock_value]
    | ADD FULLTEXT [INDEX | KEY] [index_name](column_name, [column_name ...]) [WITH PARSER tokenizer_option] [PARSER_PROPERTIES[=](parser_properties_list)] [LOCAL]
    | 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]
    | 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)
    | 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)
    | FORCE

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]

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

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

algorithm_value:
    default
    | inplace
    | copy
    | instant

lock_value:
    default 
    | none 
    | shared 
    | exclusive

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:
    | 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}
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SEMISTRUCT_ENCODING_TYPE [=] 'encoding'

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)

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}

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_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] ...}

参数说明​

参数描述
TEMPORARY为可选项，表示修改临时表。
ADD [COLUMN]增加列，支持增加生成列。
[FIRST | BEFORE | AFTER column_name]将新增的列作为表的第一列或在 column_name 列之前/之后。 目前，seekdb 仅支持在 ADD COLUMN 语法中设置列的位置。
CHANGE [COLUMN]修改列名和列定义，仅支持增加特定字符数据类型（VARCHAR、VARBINARY、CHAR 等）的长度。
MODIFY [COLUMN]修改列属性。
ALTER [COLUMN] {SET DEFAULT const_value | DROP DEFAULT}修改列的默认值。
DROP [COLUMN]删除列。
不允许删除主键列。
ADD FOREIGN KEY增加外键。 如果不指定外键名，则会使用表名 + OBFK + 创建时间命名。（例如，在 2021 年 8 月 1 日 00:00:00 为 t1 表创建的外键名称为 t1_OBFK_1627747200000000）。外键允许跨表交叉引用相关数据，当 UPDATE 或 DELETE 操作影响与子表相匹配行的父表中键值时，其结果取决于 ON UPDATE 和 ON DELETE 子句的引用操作：
  • CASCADE：表示从父表中删除或更新行，并自动删除或更新子表中匹配的行。
  • SET NULL：表示从父表中删除或更新行，并将子表中的外键列设置为 NULL。
  • RESTRICT：表示拒绝对父表的删除或更新操作。
  • NO ACTION：指定延迟检查。
此外还支持 SET DEFAULT 操作。
ADD PRIMARY KEY增加主键。 可以指定一个或多个列作为主键。如果是多个列，它们将组成复合主键。
ADD {INDEX | KEY}用于增加索引。有关添加索引的介绍请参加下文 ADD INDEX KEY
ADD FULLTEXT [INDEX | KEY]增加全文索引。有关创建全文索引的介绍信息，参见 创建索引 下创建全文索引章节。
WITH PARSER tokenizer_option可选项，指定全文索引的分词器。详细介绍可参见下文 tokenizer_option。
PARSER_PROPERTIES[=](parser_properties_list)可选项，指定分词器的属性。详细介绍可参见下文 parser_properties。
ALTER INDEX修改索引是否可见，当索引状态为 INVISIBLE 时，SQL 优化器将不会选择该索引。
key_part创建（函数）索引。
index_col_name指定索引的列名，每个列名后都支持 ASC（升序），不支持 DESC（降序）。默认为升序。 建立索引的排序方式为：首先以 index_col_name 中第一个列的值排序；该列值相同的记录，按下一列名的值排序；以此类推。
expr表示合法的函数索引表达式，且允许是布尔表达式，例如 c1=c1。
注意 seekdb 当前版本禁止创建生成列上的函数索引。
ADD [PARTITION]为分区表增加分区。
DROP {PARTITION | SUBPARTITION}删除分区：
  • PARTITION：针对 Range、List 类型的一级分区，删除指定分区（如果指定分区下存在二级分区，会同时删除该分区下所有二级分区），包括分区定义和其中的数据，同时对分区上存在的索引进行维护。
  • SUBPARTITION：针对 *-RANGE、*-LIST 类型的二级分区，删除指定二级分区，包括分区定义和其中的数据。同时对分区上存在的索引进行维护。
多个分区名称之间用逗号分隔。
注意 删除分区时，请尽量避免该分区上存在活动的事务或查询，否则可能会导致 SQL 语句报错，或者一些异常情况。
TRUNCATE {PARTITION | SUBPARTITION}删除分区数据：
  • PARTITION：针对 Range、List 类型的一级分区，清除指定分区中的全部数据（如果指定分区下存在二级分区，会同时清除该分区下所有二级分区中的数据），同时对分区上存在的索引进行维护。
  • SUBPARTITION：针对 *-RANGE、*-LIST 类型的二级分区，清除指定二级分区中的全部数据。同时对分区上存在的索引进行维护。
多个分区名称之间用逗号分隔。
注意 删除分区数据时，请尽量避免该分区上存在活动的事务或查询，否则可能会导致 SQL 语句报错，或者一些异常情况。
RENAME COLUMN old_col_name [TO|AS] new_col_name修改列名称。仅修改列名，不改变列定义。
注意
  • 如果目标名称在表中已经存在，则执行会报错。
  • 如果重命名的列出现了循环，可以正常执行，例如 ALTER TABLE t1 RENAME COLUMN a to b, RENAME COLUMN b to a;。
  • 如果重命名的列上建有索引，或者有外键约束，RENAME COLUMN 可以正常执行，并且索引定义和外键约束会自动级联修改。
  • 同一条 ALTER TABLE 语句中不支持同时包含 RENAME COLUMN 和 ADD PARTITION／ALTER COLUMN 的场景。
RENAME [TO|AS] table_name表重命名。
注意 在 RENAME TABLE 操作过程中，由于加入了表锁定和读写防御措施，这将增加操作的耗时。为了避免对其他用户执行 DDL 操作造成影响，建议不要批量进行 RENAME TABLE 操作。
RENAME {INDEX | KEY}重命名索引或键。
DROP [FOREIGN KEY]删除外键。
DROP [PRIMARY KEY]删除主键。
说明 对于 seekdb，以下情况不允许删除主键：
  • 该表是包含外键信息的父表。
  • 该表是子表，但主键列包含外键引用列。
[SET] table_option设置表级属性，可选以下参数：
  • REPLICA_NUM：设置表的副本数（暂不支持）。
  • BLOCK_SIZE：设置表的微块大小，默认为 16384，即 16 KB，取值范围为 [1024,1048576]。
  • lob_inrow_threshold：用于配置 INROW 阈值，当 LOB 数据大小超过该阈值时，会转为 OUTROW 存储在 LOB Meta 表中，默认为 4KB。
  • COMPRESSION：设置表的压缩方式，默认为 None，表示不压缩。
  • AUTO_INCREMENT：设置表中自增列的下一个取值，可以增大或者减小。
    注意 当减小一个表的自增字段的值时，需注意以下情况：
    如果表中已存在数据并且自增列中的最大值不小于新指定的 AUTO_INCREMENT值时，新的 AUTO_INCREMENT 值将自动调整为表中自增列现有最大值的下一个取值。例如，自增列当前的最大值为 5，当前 AUTO_INCREMENT 的值是 8，而 AUTO_INCREMENT 设置为介于 0 到 6 之间的任何值，语句执行成功后，实际的 AUTO_INCREMENT 值都会被调整为 6。
  • comment：设置表的注释信息。
  • PROGRESSIVE_MERGE_NUM：设置渐进合并步数，取值范围为 [0,100]。
  • parallel_clause：指定表级别的并行度。
  • NOPARALLEL：并行度为 1，默认配置。
  • PARALLEL integer：指定并行度，integer 取值大于等于 1。
CHECK更改 CHECK 约束。 支持如下操作：
  • 添加新的 CHECK 约束。
  • 删除当前名称为 constraint_name 的 CHECK 约束。
[NOT] ENFORCED是否强制执行名为 constraint_name 的 CHECK 约束。
  • 如果省略或指定为 ENFORCED，则创建并强制执行约束，为默认状态。
  • 如果指定为 NOT ENFORCED，则创建约束但不强制执行。
ADD COLUMN GROUP([all columns, ]each column) [DELAYED]将表变更为列存表。具体解释如下：
  • ADD COLUMN GROUP(all columns, each column)：将表变更为行存列存冗余的表。
  • ADD COLUMN GROUP(each column)：将表变更为列存表。
  • DELAYED：可选项，表示延迟（异步）执行行存表转列存表命令，命令执行后表定义中的存储格式已做修改，但是真正执行行存转列存操作的时机是执行合并任务时，此操作不阻塞当前 DML，为 Online DDL。如果没有指定 DELAYED，默认为 Offline DDL ，将同步操作行存表转为列存表。
    注意
    • 当前仅支持将行存表修改为列存表（each column）和将行存表修改为行存列存冗余格式的表（all columns, each column）时指定 DELAYED。
    • 在执行 DELAYED 延迟操作行存表转列存表命令后，在基线数据实际完成合并前，由于数据存储格式没有真正的转换，查询性能可能不符合预期。
DROP COLUMN GROUP([all columns, ]each column)移除表的存储格式。具体解释如下：
  • DROP COLUMN GROUP(all columns, each column): 移除表的行存列存冗余格式。
  • DROP COLUMN GROUP(all columns): 移除表的行存格式。
  • DROP COLUMN GROUP(each column): 移除表的列存格式。
index_column_group_option指定索引选项。具体解释如下：
  • WITH COLUMN GROUP(all columns, each column)：指定添加行存列存冗余的索引。
  • WITH COLUMN GROUP(all columns)：指定添加行存索引。
  • WITH COLUMN GROUP(each column)：指定添加列存索引。
SKIP_INDEX修改列的 Skip Index 属性。取值如下：
  • MIN_MAX：是 Skip Index 中最通用的聚合数据类型，它会在索引节点粒度下存储被索引列的最大值、最小值和 Null Count。这种类型的数据可以加速下推的 Filter 和 MIN/MAX 聚合的下推。
  • SUM：用于加速数值类型的 SUM 聚合的下推。

注意
  • JSON 和空间数据类型的列禁止创建 Skip Index。
  • 生成列禁止创建 Skip Index。
EXCHANGE PARTITION partition_name WITH TABLE origin_table_name指定分区交换。其中，partition_name 表示分区交换中分区表的分区名称。origin_table_name 表示分区交换中源表名称，为非分区表或一级分区表。
说明 seekdb 支持二级分区表的一级分区与一级分区表交换数据。更多分区交换的信息，参见 分区交换。
TABLE_MODE可选项，用于指定合并触发阈值与合并策略，即控制数据转储后的合并行为。取值的详细介绍可参见下文 table_mode_value。
AUTO_INCREMENT_CACHE_SIZE用于设置缓存的自增值个数。该参数默认值为 0，表示该项未进行配置，系统将采用租户级别的配置项（auto_increment_cache_size）作为自增列的缓存大小设置。
READ {ONLY | WRITE}指定表的读写权限。具体如下：
  • READ ONLY：表示表只能被读取，不能被修改或删除。
    注意 表级别的 READ ONLY 参数对具有 SUPER 权限的用户不生效，只对普通用户生效。
  • READ WRITE：默认值，表示表可以被读取、修改和删除。
enable_macro_block_bloom_filter [=] {True | False}用于指定是否持久化宏块级别 bloom filter。取值如下：
  • True：开启持久化宏块级别 bloom filter。
  • False：关闭持久化宏块级别 bloom filter。

注意 对于 seekdb，CREATE TABLE 语句，支持 enable_macro_block_bloom_filter 选项。
DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)修改表的动态分区管理属性。dynamic_partition_policy_list 动态分区策略的可配置参数列表，各参数用英文逗号分隔，详细介绍可参见下文 dynamic_partition_policy_option。
注意 对于 seekdb 支持指定表的动态分区管理属性。
REORGANIZE PARTITION用于手动分区分裂。相关参数如下：
  • partition_name：指定待分裂的分区名称。
  • split_partition_name：指定分裂出的新分区名称。
  • value：指定分区分裂的界限（取值范围）。分区分裂所定义的 value 范围需要保证与源分区的 value 范围相同，且需要保证按升序定义 value。
  • source_value：指定待分裂分区原始界限（取值范围）。即最后一个分区分裂的定义，其 value 等同于源分区的 value。
更多有关手动分区分裂的信息，参见 手动分区分裂。
注意 在 seekdb 中支持一个分区分裂成多个分区，不支持多个分区分裂成多个分区。
PARTITION BY用于修改表自动分区的属性或者修改分区规则。相关参数如下：
  • partition_option：修改分区规则选项。详细介绍，参见 修改分区规则。
  • RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')]：用于修改表自动分区的属性。详细介绍，参见 自动分区分裂 中的修改表自动分区属性的语法。
MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)表示新增二级分区。
注意
  • 不支持二级分区类型是 Hash 或 Key 类型的场景新增二级分区。
  • 对于 seekdb 支持新增二级分区。
add_subpartition_option表示新增的二级分区定义。
range_partition_expr表示 Range/Range Columns 分区表达式。
list_partition_expr表示 List/List Columns 分区表达式。
FORCE清除废弃列。
注意
  • 对于 seekdb 支持清除废弃列。
  • 当某些列被删除，即使这些列不再被使用，它们仍然占据物理存储空间。如果要移除这些废弃列并回收相关空间，需要清除废弃列。
SEMISTRUCT_ENCODING_TYPE可选项，用于指定半结构化编码类型。具体如下：
  • ''，表示不启用半结构化编码。
  • 'encoding'，表示启用半结构化编码。
功能使用说明可参见使用半结构化编码。

ADD INDEX KEY​

  • ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option_list][index_column_group_option] [algorithm] [lock]

    • INDEX | KEY :为创建的表指定键或索引。这两个关键词是等价的。

    • index_name：可选项，指定索引名称。如果不指定索引名，则会使用索引引用的第一列作为索引名，如果命名存在重复，则会使用下划线（_）+ 序号的方式命名。

    • index_type：可选项，用来指定索引使用的索引类型。

    • key_part：指定索引要包含的列名或表达式。

    • index_option：可选项，指定索引选项列表。

    • algorithm：指定了执行 DDL 操作（例如添加索引）时使用的底层算法类型。

      提示

      在当前版本中，algorithm 参数仅为兼容 MySQL 语法，不产生实际影响。

    • lock：定义了执行 DDL 操作时施加的表锁级别，控制并发访问的粒度。

      提示

      在当前版本中，lock 参数仅为兼容 MySQL 语法，不产生实际影响。

table_mode_value​

信息

在以下列出的 TABLE_MODE 模式中，除了 NORMAL 模式之外，所有模式都代表 QUEUING 表。这种 QUEUING 表是最基本的表类型，并且随后列出的几种模式（除了 NORMAL 模式）代表了使用更加积极的合并策略。

  • 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'
);

示例​

增加和删除列的操作​

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

CREATE TABLE tbl1 (c1 INT(11) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected (0.001 sec)
DESCRIBE tbl1;
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra |
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |
| c2    | varchar(50)| YES      |        | NULL    |       |
+-------+------------+----------+--------+---------+-------+
2 rows in set (0.001 sec)

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

    ALTER TABLE tbl1 ADD c3 INT;
    Query OK, 1 row affected (0.094 sec)
    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 rows in set (0.001 sec)

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

    ALTER TABLE tbl1 DROP c3;
    Query OK, 1 row affected (0.093 sec)
    DESCRIBE tbl1;
    +-------+------------+----------+--------+---------+-------+
    | Field | Type       | Null     | Key    | Default | Extra |
    +-------+------------+----------+--------+---------+-------+
    | c1    | int(11)    | NO       | PRI    | NULL    |       |
    | c2    | varchar(50)| YES      |        | NULL    |       |
    +-------+------------+----------+--------+---------+-------+
    2 rows in set (0.001 sec)

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

    ALTER TABLE tbl1 CHANGE COLUMN c2 c3 VARCHAR(50);
    Query OK, 1 row affected (0.024 sec)
    DESCRIBE tbl1;
    +-------+------------+----------+--------+---------+-------+
    | Field | Type       | Null     | Key    | Default | Extra |
    +-------+------------+----------+--------+---------+-------+
    | c1    | int(11)    | NO       | PRI    | NULL    |       |
    | c3    | varchar(50)| YES      |        | NULL    |       |
    +-------+------------+----------+--------+---------+-------+
    2 rows in set (0.001 sec)

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

    ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST;
    Query OK, 1 row affected (0.096 sec)
    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 rows in set (0.001 sec)

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

    ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1;
    Query OK, 1 row affected (0.024 sec)
    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 rows in set (0.001 sec)

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

    ALTER TABLE tbl1 ADD COLUMN c5 INT BEFORE c4;
    Query OK, 1 row affected (0.024 sec)
    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 rows in set (0.001 sec)

  • 为表 tbl2 添加外键约束 fk1。当 UPDATE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

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

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

    ALTER TABLE tbl2 DROP FOREIGN KEY fk1;
    Query OK, 0 row affected (0.037 sec)

  • 删除 tbl2 表的主键。

    ALTER TABLE tbl2 DROP PRIMARY KEY;
    Query OK, 0 row affected (0.440 sec)

索引操作​

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

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

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

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

    ALTER TABLE tbl2 ADD INDEX ind3 (c1,c2);
    Query OK, 0 row affected (0.239 sec)

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

    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 (0.006 sec)

  • 删除 tbl2 表上的索引 ind2。

    ALTER TABLE tbl2 DROP INDEX ind2;
    Query OK, 0 row affected (0.304 sec)
    信息

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

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

    ALTER TABLE t1_func ADD INDEX ((CONCAT(c1,'a')));
    Query OK, 0 rows affected (0.024 sec)
    ALTER TABLE t1_func ADD INDEX ((c1+1));
    Query OK, 0 rows affected (0.039 sec)
    ALTER TABLE t1_func ADD INDEX i2 ((CONCAT(c1,'a')));
    Query OK, 0 rows affected (0.063 sec)
    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 (0.012 sec)

  • 为表创建列存索引。

    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 (0.032 sec)

分区操作​

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

    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 (0.384 sec)

    ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
    Query OK, 0 rows affected (0.504 sec)

  • 为分区表 t_log_part_by_range 添加分区 M202006。

    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 affectedaffected (0.604 sec)

    ALTER TABLE t_log_part_by_range ADD PARTITION
             (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
             );

修改并行度​

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

    ALTER TABLE tbl3 PARALLEL 2;

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

列类型相关操作​

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

    CREATE TABLE tbl4 (col1 BIGINT(10) NOT NULL,col2 INT);
    Query OK, 0 rows affected (0.204 sec)

    ALTER TABLE tbl4 MODIFY col1 BIGINT(10) AUTO_INCREMENT;
    Query OK, 0 rows affected (0.324 sec)

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

    ALTER TABLE tbl4 ADD PRIMARY KEY (col1);
    Query OK, 0 rows affected (0.412 sec)

    ALTER TABLE tbl4 DROP PRIMARY KEY,ADD PRIMARY KEY (`col2`);
    Query OK, 0 rows affected (0.104 sec)

  • 添加 CHECK 约束。

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

  • 删除表 tbl7 的 CHECK 约束。

    ALTER TABLE tbl7 DROP CHECK my_check;
    Query OK, 0 rows affected (0.309 sec)

  • 修改列类型为 NOT NULL。

    CREATE TABLE dept(
        deptno           NUMBER(2,0),
        dname            VARCHAR(14),
        location         VARCHAR(13),
        CONSTRAINT pk_dept PRIMARY KEY(deptno)
    );
    Query OK, 0 rows affected (0.311 sec)

    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 (0.401 sec)

    ALTER TABLE emp MODIFY deptno NUMBER(2,0) DEFAULT '12' NOT NULL;
    Query OK, 0 rows aaffected (0.410 sec)

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

提示

需要满足以下条件时才允许修改列名和增加 not null 约束：

  • 只能同时改一列的列名和约束，不支持多列。

  • 只支持添加 not null 约束这一种约束。

  • 只包含 alter_column 这一种行为，不含索引、分区等其他操作。

  • 更改名字的列和添加约束的列必须是同一列。

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

两列同时添加 NOT NULL 约束​

提示

同时给两列添加 NOT NULL 约束需要满足以下条件：

  • 不支持多于两列的加约束操作。

  • 只支持添加 not null 约束这一种约束。

  • 只包含 alter_column 这一种行为，不含索引、分区等其他操作。

  • 不能有更改除加约束的两列外的其他列的操作。

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

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)

更改含前缀索引的列类型​

提示

更改含前缀索引的列类型需要满足以下条件：

  • 原列类型为 char 类型。

  • 目标列类型为 varchar 类型。

  • 原列和目标列的长度需要一致。

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

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

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

    ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);

更改外键约束规则​

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

    CREATE TABLE t1(c1 int unique key);

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

    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 的更新和删除规则：

    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 执行会报错，但是重命名为原名称则不会报错。

    CREATE TABLE tbl8 (a INT, b INT);
    Query OK, 0 rows affected (0.239 sec)

    ALTER TABLE tbl8 RENAME COLUMN a TO b;
    ERROR 1060 (42S21): Duplicate column name 'b'

    ALTER TABLE tbl8 RENAME COLUMN a TO a;
    Query OK, 0 rows affected (0.249 sec)

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

    CREATE TABLE tbl9 (a INT, b INT, index idx_a(a));
    Query OK, 0 rows affected (0.061 sec)

    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 (0.006 sec)

    ALTER TABLE tbl9 RENAME COLUMN a TO c;
    Query OK, 0 rows affected (0.037 sec)

    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 (0.116 sec)

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

    DROP TABLE tbl9;
    CREATE TABLE tbl9 (c1 INT PRIMARY KEY, c2 BLOB, c3 INT, INDEX i1 (c2(10)));
    Query OK, 0 rows affected (0.074 sec)

    ALTER TABLE tbl9 RENAME COLUMN c2 TO c2_, RENAME COLUMN c1 TO c2_1;
    DESC tbl9;
    Query OK, 0 rows affected (0.108 sec)

    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 (0.003 sec)

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

    CREATE TABLE tbl10 (a INT PRIMARY KEY);
    Query OK, 0 rows affected (0.024 sec)

    CREATE TABLE tbl11(b INT, FOREIGN KEY (b) REFERENCES tbl10(a));

    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 (0.002 sec)

    ALTER TABLE tbl10 RENAME COLUMN a TO c;
    Query OK, 0 rows affected

    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 (0.005 sec)

seekdb 在以下场景，不支持修改或者不会自动级联修改：

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

    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;

    ALTER TABLE tbl12 RENAME COLUMN a TO e;
    ERROR 3108 (HY000): Column 'a' has a generated column dependency

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

    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 约束引用，不支持修改列名，执行会报错。

    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;
    CREATE TABLE tbl12(i INT, INDEX ((i+1)));
    Query OK, 0 rows affected (0.304 sec)

    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 执行成功，查询视图会报错，需要用户手动修改视图定义。

    CREATE TABLE tbl13(a INT);
    Query OK, 0 rows affected (0.303 sec)

    CREATE VIEW v1 AS SELECT a + 1 FROM tbl13;
    Query OK, 0 rows affected (0.308 sec)

    SELECT * FROM v1;
    Empty set

    ALTER TABLE tbl13 RENAME COLUMN a TO b;
    Query OK, 0 rows affected (0.306 sec)

    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 报错，需要用户手动修改。

    CREATE TABLE tbl14(a INT);
    Query OK, 0 rows affected (0.309 sec)

    CREATE PROCEDURE proc() SELECT a + 1 FROM tbl14;
    Query OK, 0 rows affected (0.307 sec)

    CALL proc();
    Empty set

    ALTER TABLE tbl14 RENAME COLUMN a TO b;
    Query OK, 0 rows affected (0.310 sec)

    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;

相关文档​

更改表

Contents

编辑此页