Skip to main content

ALTER TABLE

Description​

This statement is used to modify the structure of an existing table, such as modifying the table and its attributes, adding columns, modifying columns and their attributes, and deleting columns.

Syntax​

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

Parameter description​

ParameterDescription
TEMPORARYOptional. Modifies a temporary table.
ADD [COLUMN]Adds a column. Generated columns are supported.
[FIRST | BEFORE | AFTER column_name]Specifies the position of the added column as the first column in the table or before/after the column_name column. Currently, seekdb only supports setting the column position in the ADD COLUMN syntax.
CHANGE [COLUMN]Modifies the column name and column definition. Only the length of specific character data types (VARCHAR, VARBINARY, CHAR, etc.) can be modified.
MODIFY [COLUMN]Modifies column attributes.
ALTER [COLUMN] {SET DEFAULT const_value | DROP DEFAULT}Modifies the default value of a column.
DROP [COLUMN]Drops a column.
The primary key column cannot be dropped.
ADD FOREIGN KEYAdds a foreign key. If the foreign key name is not specified, the foreign key name is generated as the table name + OBFK + creation time. For example, the foreign key name created for the t1 table on August 1, 2021, 00:00:00 is t1_OBFK_1627747200000000. A foreign key allows cross-referencing related data across tables. When UPDATE or DELETE operations affect the key values in the parent table that match rows in the child table, the result depends on the ON UPDATE and ON DELETE clauses of the reference operation:
  • CASCADE: The rows in the parent table are deleted or updated, and the matching rows in the child table are automatically deleted or updated.
  • SET NULL: The rows in the parent table are deleted or updated, and the foreign key column in the child table is set to NULL.
  • RESTRICT: The deletion or update of the parent table is rejected.
  • NO ACTION: Specifies delayed checking.
Additionally, the SET DEFAULT operation is supported.
ADD PRIMARY KEYAdds a primary key. One or more columns can be specified as the primary key. If multiple columns are specified, they form a composite primary key.
ADD {INDEX | KEY}Adds an index. For more information about adding an index, see ADD INDEX KEY.
ADD FULLTEXT [INDEX | KEY]Adds a full-text index. For more information about creating a full-text index, see the section on creating a full-text index in Create an index.
WITH PARSER tokenizer_optionOptional. Specifies the tokenizer for the full-text index. For more information, see tokenizer_option below.
PARSER_PROPERTIES[=](parser_properties_list)Optional. Specifies the properties of the tokenizer. For more information, see parser_properties below.
ALTER INDEXModifies whether the index is visible. If the index status is INVISIBLE, the SQL optimizer will not select this index.
key_partCreates a (function) index.
index_col_nameSpecifies the column name for the index. ASC (ascending) is supported after each column name, but DESC (descending) is not. The default is ascending. The sorting method for the index is as follows: First, the values in the index_col_name column are sorted; for records with the same value in this column, the values in the next column name are sorted; and so on.
exprSpecifies a valid function index expression, which can be a Boolean expression, for example, c1=c1.
Notice In the current version of seekdb, you cannot create a function index on a generated column.
ADD [PARTITION]Adds a partition to a partitioned table.
DROP {PARTITION | SUBPARTITION}Drops a partition:
  • PARTITION: Drops the specified partition of a RANGE or LIST partitioned table. If the specified partition contains subpartitions, all subpartitions under the specified partition are also dropped. The partition definition and data in the partition are cleared. The indexes on the partition are maintained.
  • SUBPARTITION: Drops the specified subpartition of a *-RANGE or *-LIST partitioned table. The subpartition definition and data in the subpartition are cleared. The indexes on the partition are maintained.
Multiple partition names are separated by commas.
Notice When you drop a partition, try to avoid active transactions or queries on the partition. Otherwise, an error may occur when you execute an SQL statement, or an exception may be thrown.
TRUNCATE {PARTITION | SUBPARTITION}Drops the data in a partition:
  • PARTITION: Clears all data in the specified partition of a RANGE or LIST partitioned table. If the specified partition contains subpartitions, all data in the subpartitions under the specified partition are also cleared. The indexes on the partition are maintained.
  • SUBPARTITION: Clears all data in the specified subpartition of a *-RANGE or *-LIST partitioned table. The indexes on the partition are maintained.
Multiple partition names are separated by commas.
Notice When you drop the data in a partition, try to avoid active transactions or queries on the partition. Otherwise, an error may occur when you execute an SQL statement, or an exception may be thrown.
RENAME COLUMN old_col_name [TO|AS] new_col_nameRenames a column. Only the column name is modified, and the column definition remains unchanged.
Notice
  • If the target name already exists in the table, an error is returned.
  • If the renamed column forms a cycle, the operation can be executed normally. For example, ALTER TABLE t1 RENAME COLUMN a to b, RENAME COLUMN b to a;.
  • If the renamed column has an index or foreign key constraint, RENAME COLUMN can be executed normally, and the index definition and foreign key constraint are automatically modified.
  • The RENAME COLUMN statement cannot be executed together with the ADD PARTITION or ALTER COLUMN statement in the same ALTER TABLE statement.
RENAME [TO|AS] table_nameRenames a table.
Notice During the RENAME TABLE operation, table locking and read/write defense measures are added, which increases the operation time. To avoid affecting other users' DDL operations, we recommend that you do not perform batch RENAME TABLE operations.
RENAME {INDEX | KEY}Renames an index or key.
DROP [FOREIGN KEY]Drops a foreign key.
DROP [PRIMARY KEY]Drops a primary key.
Notice In seekdb, you cannot drop a primary key in the following cases:
  • The table is a parent table that contains foreign key information.
  • The table is a child table, and the primary key column contains a foreign key reference column.
[SET] table_optionSets the table-level attribute. Valid values:
  • REPLICA_NUM: the number of replicas for the table. This parameter is not supported.
  • BLOCK_SIZE: the size of microblocks in the table. The default value is 16384 (16 KB). Valid values: [1024, 1048576].
  • lob_inrow_threshold: the threshold for INROW storage. If the size of LOB data exceeds this threshold, the data is stored in the LOB Meta table in OUTROW format. The default value is 4 KB.
  • COMPRESSION: the compression method for the table. The default value is None, indicating no compression.
  • AUTO_INCREMENT: the next value for the auto-increment column. You can increase or decrease the value.
    Notice When you decrease the value of the auto-increment column of a table, consider the following:
    If the table contains data and the maximum value of the auto-increment column is not less than the new AUTO_INCREMENT value, the new AUTO_INCREMENT value will be automatically adjusted to the next value of the maximum value of the auto-increment column. For example, if the current maximum value of the auto-increment column is 5 and the current AUTO_INCREMENT value is 8, and you set AUTO_INCREMENT to any value between 0 and 6, the actual AUTO_INCREMENT value will be adjusted to 6 after the statement is executed.
  • comment: the comment for the table.
  • PROGRESSIVE_MERGE_NUM: the number of steps for progressive major compactions. Valid values: [0, 100].
  • parallel_clause: the parallelism at the table level.
  • NOPARALLEL: the default parallelism is 1.
  • PARALLEL integer: the parallelism, where integer is greater than or equal to 1.
CHECKModifies the CHECK constraint. Valid values:
  • Adds a new CHECK constraint.
  • Drops the CHECK constraint with the specified name constraint_name.
[NOT] ENFORCEDSpecifies whether to enforce the CHECK constraint with the specified name constraint_name.
  • If you omit this option or specify ENFORCED, the constraint is created and enforced. This is the default behavior.
  • If you specify NOT ENFORCED, the constraint is created but not enforced.
ADD COLUMN GROUP([all columns, ]each column) [DELAYED]Converts a rowstore table to a columnstore table. The following table describes the options.
  • ADD COLUMN GROUP(all columns, each column): converts a rowstore table to a hybrid rowstore-columnstore table.
  • ADD COLUMN GROUP(each column): converts a rowstore table to a columnstore table.
  • DELAYED: specifies that the conversion from a rowstore table to a columnstore table is performed asynchronously. After the command is executed, the storage format in the table definition is modified, but the actual conversion from a rowstore table to a columnstore table is performed during a major compaction. This operation does not block current DML operations and is Online DDL. If you do not specify DELAYED, the default is Offline DDL, which synchronously converts a rowstore table to a columnstore table.
    Notice
    • You can specify DELAYED only when you convert a rowstore table to a columnstore table (each column) or convert a rowstore table to a hybrid rowstore-columnstore table (all columns, each column).
    • Before the baseline data is merged, the data storage format has not been converted. Therefore, query performance may not meet expectations after the DELAYED operation.
DROP COLUMN GROUP([all columns, ]each column)Drops the storage format of the table. The following table describes the options.
  • DROP COLUMN GROUP(all columns, each column): drops the hybrid rowstore-columnstore format of the table.
  • DROP COLUMN GROUP(all columns): drops the rowstore format of the table.
  • DROP COLUMN GROUP(each column): drops the columnstore format of the table.
index_column_group_optionSpecifies the index options. The following table describes the options.
  • WITH COLUMN GROUP(all columns, each column): specifies that the index is a hybrid rowstore-columnstore index.
  • WITH COLUMN GROUP(all columns): specifies that the index is a rowstore index.
  • WITH COLUMN GROUP(each column): specifies that the index is a columnstore index.
SKIP_INDEXSpecifies the Skip Index attribute for a column. Valid values:
  • MIN_MAX: the most commonly used aggregate data type in a Skip Index. It stores the maximum value, minimum value, and null count of the indexed column at the index node level. This type of data can accelerate the pushdown of filters and MIN/MAX aggregations.
  • SUM: accelerates the pushdown of SUM aggregations for numeric types.

Notice
  • You cannot create a Skip Index for a JSON or spatial column.
  • You cannot create a Skip Index for a generated column.
EXCHANGE PARTITION partition_name WITH TABLE origin_table_nameSpecifies a partition exchange. partition_name specifies the name of the partition in the partition exchange. origin_table_name specifies the name of the source table in the partition exchange, which is a non-partitioned table or a partitioned table.
Notice seekdb allows you to exchange data between a partition of a partitioned table and a partitioned table. For more information about partition exchanges, see Partition exchange.
TABLE_MODEOptional. Specifies the merge trigger threshold and merge strategy, which control the behavior after data is dumped. For more information, see table_mode_value.
AUTO_INCREMENT_CACHE_SIZESpecifies the number of auto-increment values cached. The default value is 0, indicating that the system uses the tenant-level parameter auto_increment_cache_size as the cache size for the auto-increment column.
READ {ONLY | WRITE}Specifies the read/write permissions of a table. Valid values:
  • READ ONLY: indicates that the table can be read but not modified or deleted.
    Note The READ ONLY parameter at the table level does not take effect for users with the SUPER privilege. It only takes effect for ordinary users.
  • READ WRITE: default value. Indicates that the table can be read, modified, and deleted.
enable_macro_block_bloom_filter [=] {True | False}Specifies whether to persist the bloom filter at the macroblock level. Valid values:
  • True: enables the persistence of the bloom filter at the macroblock level.
  • False: disables the persistence of the bloom filter at the macroblock level.

Note For seekdb, the CREATE TABLE statement supports the enable_macro_block_bloom_filter option.
DYNAMIC_PARTITION_POLICY [=] $dynamic_partition_policy_list$Modifies the dynamic partition management attribute of a table. dynamic_partition_policy_list specifies the configurable parameters of the dynamic partition strategy, separated by commas. For more information, see dynamic_partition_policy_option below.
Note For seekdb, you can specify the dynamic partition management attribute of a table.
REORGANIZE PARTITIONManually splits a partition. The parameters are as follows:
  • partition_name: specifies the name of the partition to be split.
  • split_partition_name: specifies the name of the new partition after splitting.
  • value: specifies the boundary of the partition split. The value range defined for the partition split must be the same as the value range of the source partition and must be defined in ascending order.
  • source_value: specifies the original boundary of the partition to be split (value range). That is, the last partition split, whose value is the same as the value of the source partition.
For more information about manual partition splitting, see Manual Partition Splitting.
Note In seekdb, you can split one partition into multiple partitions, but you cannot split multiple partitions into multiple partitions.
PARTITION BYModifies the automatic partitioning attribute or the partitioning rule of a table. The parameters are as follows:
  • partition_option: specifies the partitioning rule modification options. For more information, see Modify the Partitioning Rule.
  • RANGE [COLUMNS]([column_name_list]) [SIZE('size_value')]: specifies the automatic partitioning attribute of a table. For more information, see the syntax for modifying the automatic partitioning attribute of a table in Automatic Partition Splitting.
MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)Adds a subpartition.
Note
  • You cannot add a subpartition if the subpartition type is HASH or KEY.
  • In seekdb, you can add a subpartition.
add_subpartition_optionSpecifies the definition of the added subpartition.
range_partition_exprSpecifies the RANGE/RANGE COLUMNS partitioning expression.
list_partition_exprSpecifies the LIST/LIST COLUMNS partitioning expression.
FORCEDrops obsolete columns.
Note
  • In seekdb, you can drop obsolete columns.
  • When some columns are deleted, they still occupy physical storage space, even if they are no longer used. To remove these obsolete columns and reclaim the space, you must drop them.
SEMISTRUCT_ENCODING_TYPEOptional. Specifies the encoding type of semi-structured data. Valid values:
  • '': disables semi-structured encoding.
  • 'encoding': enables semi-structured encoding.
For more information about the use of semi-structured encoding, see Use Semi-Structured Encoding.

ADD INDEX KEY​

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

    • INDEX | KEY : specifies the key or index for the created table. These two keywords are equivalent.

    • index_name：optional. Specifies the index name. If you do not specify an index name, the name of the first column referenced by the index is used as the index name. If a column name is repeated, an underscore (_) and a number are appended to the column name.

    • index_type：optional. Specifies the index type.

    • key_part：specifies the column name or expression to be included in the index.

    • index_option：optional. Specifies the index options.

    • algorithm：specifies the underlying algorithm type used to execute DDL operations (such as adding an index).

      tip

      In the current version, the algorithm parameter is for compatibility with MySQL syntax and does not have any actual effect.

    • lock：specifies the table lock level applied during DDL operations, controlling the granularity of concurrent access.

      tip

      In the current version, the lock parameter is for compatibility with MySQL syntax and does not have any actual effect.

table_mode_value​

info

In the following TABLE_MODE modes, all modes except NORMAL represent QUEUING tables. The QUEUING table is the most basic table type, and the other modes listed below (except for the NORMAL mode) represent more aggressive compaction strategies.

  • NORMAL: the default value. In this mode, the probability of triggering a compaction after data is dumped is very low.
  • QUEUING: In this mode, the probability of triggering a compaction after data is dumped is low.
  • MODERATE: In this mode, the probability of triggering a compaction after data is dumped is moderate.
  • SUPER: In this mode, the probability of triggering a compaction after data is dumped is high.
  • EXTREME: In this mode, the probability of triggering a compaction after data is dumped is very high.

For more information about compaction, see Adaptive compaction.

tokenizer_option​

  • SPACE: the default value. Specifies to tokenize by space. You can specify the following properties:

    PropertyValue Range
    min_token_size[1, 16]
    max_token_size[10, 84]

  • NGRAM: specifies to tokenize based on N-Gram (Chinese). You can specify the following properties:

    PropertyValue Range
    ngram_token_size[1, 10]

  • NGRAM2: specifies to split the text into consecutive characters ranging from min_ngram_size to max_ngram_size. You can specify the following properties:

    PropertyValue Range
    min_ngram_size[1, 16]
    max_ngram_size[1, 16]

  • BENG: specifies to tokenize based on Beng (basic English). You can specify the following properties:

    PropertyValue Range
    min_token_size[1, 16]
    max_token_size[10, 84]

  • IK: specifies to tokenize based on IK (Chinese). Only the utf-8 character set is supported. You can specify the following property:

    PropertyValue Range
    ik_mode
    • smart
    • max_word

You can use the TOKENIZE function to view the tokenization result of the text in the specified tokenizer and JSON format.

parser_properties​

  • min_token_size: specifies the minimum token length. The default value is 3, and the value ranges from 1 to 16.

  • max_token_size: specifies the maximum token length. The default value is 84, and the value ranges from 10 to 84.

  • ngram_token_size: specifies the token length for the NGRAM tokenizer. This property is valid only for the NGRAM tokenizer. The default value is 2, and the value ranges from 1 to 10.

  • ik_mode: specifies the tokenization mode for the IK tokenizer. Valid values:

    • smart: the default value. This mode uses the words in the dictionary to improve tokenization accuracy and prioritizes the boundaries of the words in the dictionary, which may reduce unnecessary extensions.
    • max_word: This mode identifies the words defined in the dictionary but does not affect the maximum extension of tokenization. Even if the dictionary defines words, the max_word mode still tries to split the text into more words.

  • min_ngram_size: specifies the minimum token length, and the value ranges from 1 to 16.

  • max_ngram_size: specifies the maximum token length, and the value ranges from 1 to 16.

Here is an example:

  1. Create a table named tbl1.

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

  2. Add a full-text index named fidx to the tbl1 table.

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

dynamic_partition_policy_option​

  • ENABLE = {true | false}: specifies whether to enable dynamic partitioning. Valid values:

    • true: default value. Indicates to enable dynamic partitioning.
    • false: indicates to disable dynamic partitioning.

  • PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the time to precreate partitions. When you schedule dynamic partitioning, partitions are precreated so that the upper bound of the maximum partition > now() + precreate_time. Valid values:

    • -1: default value. Indicates to not precreate partitions.
    • 0: indicates to precreate only the current partition.
    • n {hour | day | week | month | year}: specifies the time span for which to precreate partitions. For example, 3 hour indicates to precreate partitions for the last 3 hours.
    info

    • When you need to precreate multiple partitions, the time span between the upper bounds of adjacent partitions is TIME_UNIT.

    • The upper bound of the first precreated partition is the upper bound of the existing maximum partition rounded up to the nearest multiple of TIME_UNIT.

  • EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: optional. Specifies the time after which a partition expires. When you schedule dynamic partitioning, all partitions with an upper bound < now() - expire_time are deleted. Valid values:

    • -1: default value. Indicates that partitions do not expire.
    • 0: indicates that all partitions except the current one expire.
    • n {hour | day | week | month | year}: specifies the time after which a partition expires. For example, 1 day indicates that a partition expires 1 day after it is created.

For more information about how to modify a dynamic partition table, see Modify a dynamic partition table.

Here is an example:

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

Example​

Add and drop columns​

Create a sample table tbl1 and execute the DESCRIBE command to view the table information.

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)

  • Add the c3 column and execute the DESCRIBE command to view the table information.

    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)

  • Drop the c3 column and execute the DESCRIBE command to view the table information.

    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)

  • Rename the c2 field in the tbl1 table to c3, and execute the DESCRIBE command to view the table information.

    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)

  • Add the c4 column to the tbl1 table and set it as the first column of the table. Then, execute the DESCRIBE command to view the table information.

    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)

  • Add the c2 column after the c1 column and execute the DESCRIBE command to view the table information.

    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)

  • Add the c5 column before the c4 column and execute the DESCRIBE command to view the table information.

    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)

  • Add the fk1 foreign key constraint to the tbl2 table. When the UPDATE operation affects the key values in the parent table that match the rows in the child table, execute the SET NULL operation.

    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)

  • Drop the fk1 foreign key constraint from the tbl3 table.

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

  • Drop the primary key from the tbl2 table.

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

Index operations​

  • Rename the ind1 index in the tbl2 table to 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)

  • Create the ind3 index on the tbl2 table, referencing the c1 and c2 columns.

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

    You can execute the SHOW INDEX statement to view the created 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)

  • Drop the ind2 index from the tbl2 table.

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

    In actual operation and maintenance scenarios, you can use the preceding methods to perform atomic index changes.

  • Add three function indexes to the t1_func table. One of the indexes is named i2, and the other two are automatically generated by the system with names in the format of functional_index followed by a number.

    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)

  • Create a columnstore index for the table.

    1. Execute the following SQL statement to create the tbl3 table.

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

    2. Create the idx1_tbl3 columnstore index on the tbl3 table, referencing the col1 column.

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

  • Make the index invisible.

    info

    After an index is set to invisible, you can see the /*!80000 INVISIBLE */ tag in the table structure.

    1. Create the tbl4 table.

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

    2. Create the idx1_tbl4 index.

      CREATE INDEX idx1_tbl4 ON tbl4(col2);

    3. Make the idx1_tbl4 index invisible.

      ALTER TABLE tbl4 ALTER INDEX idx1_tbl4 INVISIBLE;

    4. View the structure of the tbl4 table again.

      SHOW CREATE TABLE tbl4;

      The returned result is as follows:

      +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 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)

Partition operations​

  • Clear all data in the partitions M202001 and M202002 of the partitioned table t_log_part_by_range.

    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)

  • Add the partition M202006 to the partitioned table t_log_part_by_range.

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

Modify parallelism​

  • Change the parallelism of the table tbl3 to 2.

    ALTER TABLE tbl3 PARALLEL 2;

    You can use the ALTER TABLE table_name PARALLEL int; statement to modify the parallelism of a table. You can also use the PARALLEL hint. The syntax is alter /*+ parallel(int) */ table.

Column type operations​

  • Change the column col1 of the table tbl4 to an auto-increment column.

    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)

  • Add the primary key col1 to the table tbl4, and then change the primary key to 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)

  • Add the CHECK constraint.

    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)

  • Drop the CHECK constraint from the table tbl7.

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

  • Change the column type to 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)

Change column names and add NOT NULL constraints​

tip

You can change column names and add not null constraints only if the following conditions are met:

  • You can change the name of only one column and add the not null constraint to only one column at a time.

  • You can add only the not null constraint.

  • You can change only the column name and add only the not null constraint.

  • The column whose name is changed and the column to which the not null constraint is added must be the same.

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

Add NOT NULL constraints to two columns​

tip

You can add NOT NULL constraints to two columns only if the following conditions are met:

  • You can add NOT NULL constraints to only two columns at a time.

  • You can add only the not null constraint.

  • You can change only the column name and add only the not null constraint.

  • You cannot change the names of any columns other than the two columns to which the not null constraints are added.

Execute the following command to add NOT NULL constraints to the C1 and c2 columns:

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)

Change the column type of a column with a prefix index​

tip

You can change the column type of a column with a prefix index only if the following conditions are met:

  • The original column type is char.

  • The target column type is varchar.

  • The length of the original column and the target column is the same.

  1. Execute the following command to create a table named test_alter5 that contains a column named c1 of the CHAR(20) data type and has a prefix index named idx_c1 on the c1 column:

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

  2. Execute the following command to change the data type of the c1 column to varchar(20):

    ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);

Change the foreign key constraint rules​

  1. Execute the following command to create a table named t1 and add a uniqueness constraint to the integer column c1:

    CREATE TABLE t1(c1 int unique key);

  2. Execute the following command to create a table named t2 that contains an integer column named c1 and define a foreign key constraint:

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

  3. Execute the following command to query the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS system table to obtain the update and delete rules of the foreign key constraint fk_cst_test:

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

    The returned result is as follows:

    +-------------+-------------+
    | update_rule | delete_rule |
    +-------------+-------------+
    | SET NULL    | NO ACTION   |
    +-------------+-------------+
    • UPDATE_RULE: SET NULL indicates that the foreign key is set to NULL during an update.
    • DELETE_RULE: NO ACTION indicates that the constraint is checked during a delete, which may prevent the operation.

Rename column operations​

  • The RENAME COLUMN operation does not change the column definition, only the column name. If the target name already exists in the table, the RENAME COLUMN operation will return an error, but renaming to the original name will not.

    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)

  • If an index is created on the renamed column, the RENAME COLUMN operation can be executed normally, and the index definition will be automatically updated.

    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)

  • If the renamed column is referenced by a prefix index, the RENAME COLUMN operation can be executed normally, and the prefix index will be automatically updated.

    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)

  • If a foreign key constraint is defined on the renamed column, the RENAME COLUMN operation can be executed normally, and the foreign key constraint will be automatically updated.

    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 does not support modifying or automatically updating in the following scenarios:

  • If the renamed column is referenced by a generated list expression, the column name cannot be modified, and an error will be returned during execution.

    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

  • If the renamed column is referenced by a partitioning expression, the column name cannot be modified, and an error will be returned during execution.

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

  • If the renamed column is referenced by a CHECK constraint, the column name cannot be modified, and an error will be returned during execution.

    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.

  • If the renamed column is referenced by a function index, the column name cannot be modified, and an error will be returned during execution.

    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.

  • If the renamed column is referenced by a view, the RENAME COLUMN operation will succeed, but querying the view will return an error. You need to manually modify the view definition.

    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

  • If the renamed column is referenced by a stored procedure, the RENAME COLUMN operation will succeed, but the CALL procedure will return an error. You need to manually modify the stored 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'

Modify the column store attribute of a table​

  1. Run the following SQL statement to create a table named tbl1.

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

  2. Change the table tbl1 to a rowstore-columnstore redundant table, and then drop the rowstore-columnstore redundant attribute.

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

  3. Change the table tbl1 to a columnstore table, and then drop the columnstore attribute.

    ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
    ALTER TABLE tbl1 DROP COLUMN GROUP(each column);

Modify the Skip Index attribute of a column​

Run the following SQL statement to create a table named 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)
  );

  • Change the Skip Index attribute of the col2 column in the test_skidx table to the SUM Skip Index type.

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

  • Add the Skip Index attribute to a new column after the table is created. Add the MIN_MAX Skip Index type to the col4 column in the test_skidx table.

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

  • Drop the Skip Index attribute from a column after the table is created. Drop the Skip Index attribute from the col1 column in the test_skidx table.

    ALTER TABLE test_skidx MODIFY COLUMN col1 INT SKIP_INDEX();

    or

    ALTER TABLE test_skidx MODIFY COLUMN col1 INT;

Modify table attributes​

Disable the Bloom filter at the macroblock level for the tb table.

ALTER TABLE tb SET enable_macro_block_bloom_filter = False;

References​

Change a table

Contents

Edit this page