This topic describes how to execute the ALTER TABLE statement to modify a table.

Syntax

ALTER TABLE table_name
    ADD COLUMN (column_name column_definition,...)
  | ADD {INDEX|KEY} [index_name] (column_name,...) 
  | ADD CLUSTERED [INDEX|KEY] [index_name] (column_name,...)
  | DROP COLUMN column_name
  | DROP {INDEX|KEY} index_name
  | DROP CLUSTERED [INDEX|KEY] index_name
  | MODIFY COLUMN column_name column_definition
  | RENAME new_table_name
  | TRUNCATE PARTITION {partition_names | ALL}
 | STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

Add a column

  • Syntax
    ALTER TABLE db_name.table_name ADD column_name data_type;
  • Example

    Add the province column to the customer table. Set the data type of this column to VARCHAR.

    ALTER TABLE adb_demo.customer ADD COLUMN province varchar comment 'Province';

Delete a column

  • Syntax
    ALTER TABLE db_name.table_name DROP column_name data_type;
  • Example

    Delete the province column from the customer table.

    ALTER TABLE adb_demo.customer DROP COLUMN province;

Modify the comment of a column

  • Syntax
    ALTER TABLE db_name.table_name MODIFY COLUMN column_name data_type comment 'new_comment';
  • Example

    Change the comments of the province column in the customer table to "The province where the customer is located".

    ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar comment 'The province where the customer is located';

Change the value constraint for a column from NOT NULL to NULL

Note You can modify only the value constraint from NOT NULL to NULL, but not from NULL to NOT NULL.
  • Syntax
    ALTER TABLE db_name.table_name MODIFY COLUMN column_name data_type {NULL};
  • Example

    Modify the value of the province column to NULL in the customer table.

    ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar NULL;

Modify the default value of a column

  • Syntax
    ALTER TABLE db_name.table_name MODIFY COLUMN column_name data_type DEFAULT 'default';
  • Example

    Modify the default value of the sex column to 0 (indicating male) in the customer table.

    ALTER TABLE adb_demo.customer MODIFY COLUMN sex int(11) NOT NULL DEFAULT 0;

Change the data type of a column

  • Syntax
    ALTER TABLE db_name.table_name MODIFY COLUMN column_name new_data_type;
  • Note
    You can only change data types within the scope of integer data types and floating-point data types. You can only change data types with a small value range to those with a large value range, or single-precision data types to double-precision data types.
    • Integer data types include TINYINT, SMALLINT, INT, and BIGINT. You can only change data types with a small value range to those with a large value range. For example, you can change the data type of a column only from TINYINT to BIGINT, but not from BIGINT to TINYINT
    • Floating-point data types include FLOAT and DOUBLE. You can change the data type of a column only from FLOAT to DOUBLE, but not from DOUBLE to FLOAT.
  • Example

    Change the data type of the order_number column in the test table from INT to BIGINT.

    CREATE TABLE adb_demo.test(id int, order_number int NOT NULL DEFAULT 100, name varchar) DISTRIBUTE BY HASH(id);
    ALTER TABLE adb_demo.test MODIFY COLUMN order_number BIGINT NOT NULL DEFAULT 100;

Add an index

Note
  • AnalyticDB for MySQL automatically creates an index_all='Y' index for all columns during table creation.
  • If index_all='Y' is not created, you can use the following method to add an index.
  • Syntax
    ALTER TABLE db_name.table_name ADD KEY index_name(column_name);
  • Example

    Create an index for the age column in the customer table.

    ALTER TABLE adb_demo.customer ADD KEY age_idx(age);

Delete a clustered index

  • Syntax
    ALTER TABLE db_name.table_name DROP KEY index_name;
  • Parameter description
    Execute the following statement to obtain Index_name.
    SHOW INDEXES FROM db_name.table_name;
  • Example

    Delete the index for the age column from the customer table.

    ALTER TABLE adb_demo.customer DROP KEY age_idx;

Modify the name of a column

Note You cannot modify the name of a primary key column.
  • Syntax
    ALTER TABLE db_name.table_name rename column column_name to column_newname;
  • Example

    Modify the name of the age column in the customer table to new_age.

    ALTER TABLE customer rename column age to new_age;

Modify the lifecycle of a table

  • Syntax
    ALTER TABLE db_name.table_name partitions N;
  • Example

    Change the lifecycle of the customer table from 30 to 40.

    CREATE TABLE customer (
    customer_id bigint NOT NULL COMMENT 'Customer ID',
    customer_name varchar NOT NULL COMMENT 'Customer name',
    phone_num bigint NOT NULL COMMENT 'Phone number',
    city_name varchar NOT NULL COMMENT 'City',
    sex int NOT NULL COMMENT 'Gender',
    id_number varchar NOT NULL COMMENT 'ID card number',
    home_address varchar NOT NULL COMMENT 'Home address',
    office_address varchar NOT NULL COMMENT 'Office address',
    age int NOT NULL COMMENT 'Age',
    login_time timestamp NOT NULL COMMENT 'Logon time',
    PRIMARY KEY (login_time,customer_id,phone_num)
     )
    DISTRIBUTED BY HASH(customer_id)
    PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
    COMMENT 'Customer information table';                   
    alter table customer partitions 40;

Modify the hot and cold data storage policy for a table

Note Only AnalyticDB for MySQL elastic clusters support the tiered storage of hot and cold data feature.

You can execute the ALTER TABLE statement to modify the hot and cold data storage policy of a table.

ALTER TABLE table_name storage_policy;

storage_policy:
   STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

You can change the storage policy between cold, hot or mixed.

The storage policy does not change immediately after the ALTER TABLE statement is executed. To trigger an immediate change, you can execute the BUILD TABLE db_name.table_name statement.

For information on how to specify the hot and cold storage policy when you create a table, see CREATE TABLE.

Example: Change the storage policy of a table to cold
ALTER TABLE test_table storage_policy = 'COLD';
Example: Change the storage policy of a table to hot
ALTER TABLE test_table storage_policy = 'HOT';
Example: Change the storage policy of a table to mixed and 10 partitions use the hot storage policy
alter table test_table storage_policy = 'MIXED' hot_partition_count = 10;