ALTER TABLE

Purpose

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 table_name alter_table_action_list;

alter_table_action_list:
    alter_table_action [, alter_table_action ...]

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

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [COMMENT string_value] [SKIP_INDEX(skip_index_option_list)] [ opt_position_column ]

skip_index_option_list:
    skip_index_option [,skip_index_option ...]

skip_index_option:
    MIN_MAX
    | SUM

opt_position_column:
    FIRST | BEFORE | AFTER column_name

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option ...]

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

column_desc_list:
    column_desc [, column_desc ...]

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

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

search_index_column:
    column_name [WITH (search_col_option_list)]

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

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

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

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

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

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

table_option_list:
    table_option [ table_option ...]

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | COMMENT string_value
    | parallel_clause
    | TABLE_MODE [=] 'table_mode_value'
    | AUTO_INCREMENT_CACHE_SIZE [=] INT_VALUE
    | READ {ONLY | WRITE}
    | DUPLICATE_SCOPE= 'none | cluster'
    | enable_macro_block_bloom_filter [=] {True | False}
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SEMISTRUCT_ENCODING_TYPE [=] 'encoding' # Deprecated since V4.4.1. Use SEMISTRUCT_PROPERTIES instead.
    | SEMISTRUCT_PROPERTIES [=] (encoding_type=encoding|none[,freq_threshold=INT_VALUE])
    | STORAGE_CACHE_POLICY (storage_cache_policy_option)
    | CLUSTER BY (column_name_list)
    | HMS_CATALOG_NAME [=] string_value
    | COLUMN_NAME_CASE_SENSITIVE [=] {True | False}
    | DELTA_FORMAT [=] 'flat | encoding'
    | SKIP_INDEX_LEVEL [=] {1 | 0}
    | TTL col_name + INTERVAL interval_num ttl_unit BY COMPACTION

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

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

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)

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

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

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

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

range_partition_list:
    range_partition [, range_partition ...]

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

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

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

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

add_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
        [, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
    | {SUBPARTITION subpartition_name VALUES IN list_partition_expr
        [, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}

Parameters

table_mode_value

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

For more information about major compactions, see Adaptive major compaction.

tokenizer_option

• SPACE: The default value, indicating that the text is split by spaces. You can specify the following properties:

  Property	Value Range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM: Indicates the N-Gram (Chinese) tokenization method. You can specify the following properties:

  Property	Value Range
  ngram_token_size	[1, 10]

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

  Property	Value Range
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

• BENG: Indicates the Beng (basic English) tokenization method. You can specify the following properties:

  Property	Value Range
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK: Indicates the IK (Chinese) tokenization method. Currently, only the utf-8 character set is supported. You can specify the following properties:

  Property	Value 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: The minimum token length. The default value is 3, and the value range is 1 to 16.

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

• ngram_token_size: The token length for the NGRAM tokenizer. It is valid only for the NGRAM tokenizer. The default value is 2, and the value range is 1 to 10.

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

  • smart: The default value. The vocabulary in the dictionary is used to improve the accuracy of tokenization. The boundaries of the vocabulary in the dictionary are prioritized, which may reduce unnecessary extensions.
  • max_word: The vocabulary in the dictionary is identified, but it does not affect the maximum extension of tokenization. Even if the vocabulary is defined in the dictionary, the max_word mode still tries to split the text into more words.

• min_ngram_size: The minimum token length. The value range is [1, 16].

• max_ngram_size: The maximum token length. The value range is [1, 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}: Indicates whether to enable dynamic partition management. Valid values:

  • true: The default value. Indicates that dynamic partition management is enabled.
  • false: Indicates that dynamic partition management is disabled.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: The precreation time. When dynamic partition management is scheduled, partitions are precreated so that the upper bound of the maximum partition > now() + precreate_time. Valid values:

  • -1: The default value. Indicates that no partitions are precreated.
  • 0: Indicates that only the current partition is precreated.
  • n {hour | day | week | month | year}: The time span for which partitions are precreated. For example, 3 hour indicates that partitions are precreated for the last 3 hours.

  Note

  • When multiple partitions are to be precreated, the interval between the boundaries of the partitions is TIME_UNIT.
  • The boundary of the first precreated partition is the ceiling of the upper bound of the existing maximum partition, rounded up to the nearest TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. The partition expiration time. When dynamic partition management is scheduled, all partitions whose upper bound < now() - expire_time are deleted. Valid values:

  • -1: The default value. Indicates that partitions never expire.
  • 0: Indicates that all partitions except the current one have expired.
  • n {hour | day | week | month | year}: The partition expiration time. For example, 1 day indicates that the partition expires after 1 day.

For more information about modifying dynamic partition tables, 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'
);

Examples

Add and drop columns

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

obclient> CREATE TABLE tbl1 (c1 INT(11) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra |
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |
| c2    | varchar(50)| YES      |        | NULL    |       |
+-------+------------+----------+--------+---------+-------+

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

  obclient> ALTER TABLE tbl1 ADD c3 INT;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | varchar(50)| YES      |        | NULL    |       |
  | c3    | int(11)    | YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

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

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

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

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

• 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.

  obclient> ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

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

  obclient> ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  4 row affected
  

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

  obclient> ALTER TABLE tbl1 ADD COLUMN c5 INT BEFORE c4;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c5    | int(11)    | YES      |        | NULL    |       |
  | c4    | int(11)    | YES      |        | NULL    |       |
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |
  | c3    | varchar(50)| YES      |        | NULL    |       |
  +-------+------------+----------+--------+---------+-------+
  5 row affected
  

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

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

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

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

• Drop the primary key from the tbl2 table.

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

Index operations

• Rename the ind1 index in the tbl2 table to ind2.

  obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50));
  Query OK, 0 row affected
  
  obclient> CREATE INDEX ind1 ON tbl2 (c2 ASC);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
  Query OK, 0 row affected
  

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

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

  You can execute the SHOW INDEX statement to view the created index.

  obclient> SHOW INDEX FROM tbl2;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl2  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind2     |            1 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            2 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  4 rows in set
  

• Drop the ind2 index from the tbl2 table.

  obclient> ALTER TABLE tbl2 DROP INDEX ind2;
  Query OK, 0 row affected
  

  Note

  In actual O&M scenarios, you can use the preceding methods to perform atomic index changes.

• Create three function indexes on 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.

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

• Create a columnstore index on 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.

  Note

  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
     

Specify the table-level cold/hot storage strategy for an index of an existing table

The syntax is as follows:

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

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

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

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

Parameters

You can configure the cold/hot storage strategy for an index table.

• For a local index, you can specify the table-level STORAGE_CACHE_POLICY. If you do not specify the table-level STORAGE_CACHE_POLICY, it defaults to none, which means that the cold/hot storage strategy of the index partitions follows that of the data table.

• For a global index, you can specify the table-level STORAGE_CACHE_POLICY. If the index table is partitioned, you can also specify the partition-level STORAGE_CACHE_POLICY. If you do not specify the table-level STORAGE_CACHE_POLICY, it defaults to none.

  Note

  Starting from V4.4.1, if you do not specify the table-level STORAGE_CACHE_POLICY for a global index, the index follows the STORAGE_CACHE_POLICY of the primary table. If the primary table uses the Global strategy, the index follows the STORAGE_CACHE_POLICY of the primary table. If the primary table uses the TimeLine strategy, the index follows the hot strategy.

When you specify the table-level STORAGE_CACHE_POLICY, you define different attributes in the storage_cache_policy_option parameter in the Key-Value format. The attributes are described as follows:

• GLOBAL = {"hot" | "auto" | "cold" | "none"}: specifies the cold/hot storage strategy for the index data. Valid values:

  • hot: specifies the index as hot storage. If the cache disk space is sufficient, all data in the index table will be cached to the local cache disk.
  • auto: specifies the hot data of the index table to be automatically identified by the system.
  • cold: specifies the data of the index table as cold data. The data will not be cached to the local cache disk. For data that has been persisted to the cache, the system will actively trigger cache eviction. Subsequent accesses to the table data can be stored in the macroblock memory cache, but will not be persisted to the macroblock cache or microblock cache.
  • none: default value. The strategy of the index follows that of the primary table STORAGE_CACHE_POLICY.

• timeline_strategy_list: specifies the list of time-based strategies. Parameters are separated by commas. The system automatically adjusts data on the local cache disk based on the configured strategy.

  Note

  A timeline is a strategy that determines whether partition or macroblock data is hot based on time. When you use the timeline strategy, note the following:

  • Only Range-partitioned tables are supported. Range partitions can be at the primary or secondary level. Double Range partitions are not supported because it is unclear which Range partition to use for time-based expiration.
  • When you use the timeline strategy, the partition expression can only contain column names. Expressions are not supported. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is not supported.
  • BOUNDARY_COLUMN must be a partition key. If the partition key consists of multiple columns, BOUNDARY_COLUMN must be the first column to determine whether the partition has expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine whether data is hot. Supported data types are integer types (BIGINT or INT, in the Unix timestamp format) and time types (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If BOUNDARY_COLUMN is an integer type, the table can be partitioned by Range or Range Columns.
    • If BOUNDARY_COLUMN is a time type, the table can only be partitioned by Range Columns.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit for the BOUNDARY_COLUMN parameter. This parameter is only supported when BOUNDARY_COLUMN is an integer type. If the integer value is used as a timestamp, you must specify the timestamp unit. Otherwise, the system may misinterpret the timestamp. Valid values:

    • If the partition column is of the INT type, BOUNDARY_COLUMN_UNIT can only be s.
    • If the partition column is of the BIGINT type, BOUNDARY_COLUMN_UNIT can be s or ms.

  • HOT_RETENTION = intnum retention_time_unit: specifies the time range for hot data.

    • intnum: specifies an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR: specifies years.
      • MONTH: specifies months.
      • WEEK: specifies weeks.
      • DAY: specifies days.
      • HOUR: specifies hours.
      • MINUTE: specifies minutes.

Examples

• Create an index and specify the table-level STORAGE_CACHE_POLICY for the index.

  1. Create a table named tbl3.

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

  2. Create an index named idx1_tbl3 and specify the data of the index table as hot data.

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

• Create an index and specify the data of the index table as hot data when you create the table.

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

• Add an index to an existing table and specify the data of the index table as hot data.

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

Partition operations

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

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  Query OK, 0 rows affected
  

• Add a partition named M202006 to the partitioned table t_log_part_by_range.

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );
  

Change the cold/hot storage strategy of an index table

You can use the ALTER TABLE statement to change the cold/hot storage strategy (STORAGE_CACHE_POLICY) of an existing index table. The syntax is as follows:

ALTER TABLE table_name ALTER INDEX STORAGE_CACHE_POLICY (storage_cache_policy_option);

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

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

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

Parameter description

• GLOBAL = {"hot" | "auto" | "none"}: specifies the cold/hot storage strategy for the data of the index table. Valid values:

  • hot: specifies that the data of the index table is stored in hot storage. If the cache disk space is sufficient, all data in the index table will be cached to the local cache disk.
  • auto: specifies that the system automatically identifies the hot data of the index table.
  • none: the default value. This option indicates that the cold/hot storage strategy of the index table follows that of the primary table, specified by the STORAGE_CACHE_POLICY parameter.

• timeline_strategy_list: specifies the list of time-axis strategy parameters. The parameters are separated by commas. The time-axis strategy enables the system to automatically adjust the data on the local cache disk based on the configured strategy. The system determines whether to store the data of a partition or macroblock in hot storage based on the time.

  Note

  A time-axis strategy is based on the partition range defined by the RANGE partitioning. The system caches the hot data of a partition when the specified time condition is met. When you use a time-axis strategy, note the following:

  • A time-axis strategy is supported only for RANGE-partitioned tables (RANGE partitioning can be at the primary or secondary level). This is because the system needs to determine whether the data has expired based on time. However, a double RANGE partitioning is not supported because the system cannot determine which RANGE partition to use for time-based expiration.
  • When you use a time-axis strategy, the partition expression can contain only column names. Expressions are not supported. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is invalid.
  • BOUNDARY_COLUMN must be a partition key. If the partition key contains multiple columns, BOUNDARY_COLUMN must be the first column, which is used to determine whether the partition has expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine the hot data. Supported data types: integer (BIGINT or INT, in the format of a Unix timestamp) and time (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If the data type of BOUNDARY_COLUMN is an integer, the table can be partitioned by RANGE or RANGE COLUMNS.
    • If the data type of BOUNDARY_COLUMN is a time type, the table can be partitioned only by RANGE COLUMNS.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit of the BOUNDARY_COLUMN parameter. This parameter is supported only when the data type of BOUNDARY_COLUMN is an integer. If the integer value is used as a timestamp, you must specify the timestamp unit to avoid incorrect parsing. Valid values:

    • If the partition column is of the INT type, BOUNDARY_COLUMN_UNIT can be only s.
    • If the partition column is of the BIGINT type, BOUNDARY_COLUMN_UNIT can be s or ms.

  • HOT_RETENTION = intnum retention_time_unit: specifies the time range for hot data.

    • intnum: specifies an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR: specifies years.
      • MONTH: specifies months.
      • WEEK: specifies weeks.
      • DAY: specifies days.
      • HOUR: specifies hours.
      • MINUTE: specifies minutes.

Example

Configure the system to automatically identify the hot data of the index table idx2.

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

Change the parallelism

• Change the parallelism of the table tbl3 to 2.

  obclient> ALTER TABLE tbl3 PARALLEL 2;
  

  You can also use the PARALLEL hint to change the parallelism of a table. The syntax is as follows: alter /*+ parallel(int) */ table.

Operations related to column types

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

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

• Add a primary key col1 to the tbl4 table, and then change the primary key to col2.

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

• Add a CHECK constraint.

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

• Delete the CHECK constraint from the tbl7 table.

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

• Change the column type to NOT NULL.

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

Change column names and add NOT NULL constraints

obclient> 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

Execute the following statement to add the NOT NULL constraint to the C1 and c2 columns:

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

Change the type of a column with a prefix index

1. Execute the following statement 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:

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

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

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

Change the foreign key constraint rules

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

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

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

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

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

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

   The return result is as follows:

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

   • UPDATE_RULE: SET NULL is returned, which indicates that the foreign key is set to NULL during an update.
   • DELETE_RULE: NO ACTION is returned, which indicates that the constraint is checked during a deletion, and the operation may be blocked.

Rename a column

• The RENAME COLUMN statement does not change the column definition. It only changes the column name. If the target name already exists in the table, the RENAME COLUMN statement will return an error. However, renaming the column back to its original name will not return an error.

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

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

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

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

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

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

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

OceanBase Database does not support modifying or automatically updating 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.

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

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

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

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

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

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

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

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

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

• If the renamed column is referenced by a stored procedure, the RENAME COLUMN statement will execute successfully, but the CALL procedure will return an error. You need to manually modify the stored procedure.

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

Modify the column storage 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-redundant table and then delete the rowstore-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 delete 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)
  );

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

• Delete the Skip Index attribute of a column after the table is created. Delete the Skip Index attribute of 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 persistent macroblock-level Bloom filter for the tb table.

ALTER TABLE tb SET enable_macro_block_bloom_filter = False;

Modify the STORAGE_CACHE_POLICY at the table level

• Enable the system to automatically identify hot data for the tbl1 table.

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

• Enable the system to automatically identify hot data for the tbl2 table based on the col3 column, where the data is within 2 months of the current time.

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

Modify the STORAGE_CACHE_POLICY at the partition level

• Add a partition named p2 to the tbl3 table. Enable the system to automatically identify hot data for the p2 partition, which needs to be cached to the local disk.

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

• Enable the system to automatically identify hot data for the existing partition p1 in the tbl3 table.

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

• Enable the system to automatically identify hot data for the sp3 subpartition in the tbl4 table, which needs to be cached to the local disk.

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

Modify or delete the TTL policy of a table

• Modify the TTL policy of a table. For example, set the data expiration time to 1 hour.

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

• Delete the TTL policy of a table.

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

References

Change a table