ALTER TABLE

Purpose

You can use this statement to modify the structure of an existing table. For example, you can use this statement to modify a table and its attributes, add columns to it, modify its columns and attributes, and delete its 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  
          [FIRST | AFTER column_name]
    | 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 {INDEX | KEY} 
          [index_name] index_desc
    | ADD PARTITION (range_partition_list)
    | ALTER [COLUMN] column_name {
          SET DEFAULT const_value 
        | DROP DEFAULT
      }
    | ALTER INDEX index_name 
          [VISIBLE | INVISIBLE]
    | CHANGE [COLUMN] column_name column_definition
    | DROP [COLUMN] column_name
    | DROP {INDEX | KEY} index_name
    | DROP {PARTITION | SUBPARTITION} partition_name_list
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | MODIFY [COLUMN] column_definition
    | RENAME [TO] table_name
    | RENAME {INDEX | KEY} old_index_name TO new_index_name
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment

index_desc:
   (column_desc_list) [index_type] [index_option_list]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
   reference_option [,reference_option...]

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

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [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

table_option_list:
    table_option [ table_option ...]

table_option:
    | primary_zone
    | table_tablegroup
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

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)

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

Parameters

Examples

Examples of adding and dropping columns

Create a table named tbl1 and execute the DESCRIBE statement 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 a column named c3 and execute the DESCRIBE statement to view the table information.

  obclient> ALTER TABLE tbl1 ADD c3 INTEGER;
  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 statement 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 in the tbl1 table to c3 and execute the DESCRIBE statement 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 a column named c4 to the tbl1 table, set this column as the first column of the table, and execute the DESCRIBE statement 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 a column named c2 behind the c1 column and execute the DESCRIBE statement 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 a column named c5 before the c4 column and execute the DESCRIBE statement to view the table information.

  obclient> ALTER TABLE tbl1 ADD COLUMN c5 INTEGER 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 a column named c5 before the c4 column and execute the DESCRIBE statement to view the table information.

  obclient> ALTER TABLE tbl1 ADD COLUMN c5 INTEGER 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 a foreign key constraint fk1 to the tbl2 table.

  obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50)); 
  Query OK, 0 row affected
  obclient> CREATE TABLE tbl3(c1 INT(11) PRIMARY KEY,c2 INT(50));
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1);
  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
  

Index operations

• Rename the ind1 index on 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 an index named ind3 on the tbl2 table to reference the c1 and c2 columns.

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

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

  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

You can use the preceding method to implement an atomic change to an index in actual O&M scenarios.

Example of partitions

• Truncate partitions M202001 and M202002 of the t_log_part_by_range table.

  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 the M202006 partition to the t_log_part_by_range table.

  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'))
           );
  Query OK, 0 rows affected 
  

Example of DOP

Change the DOP of the tbl1 table to 2.

obclient> ALTER TABLE tbl3 PARALLEL 2;
Query OK, 0 rows affected