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] column_name data_type;
  • Example

    Add the province column whose data type is VARCHAR to the customer table.

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

Remove a column

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

    Remove the province column from the customer table.

    ALTER TABLE adb_demo.customer DROP COLUMN province;

Change the comments 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 change 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

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

    ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar NULL;

Change the default value of a column

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

    Change the default value of the sex column in the customer table to 0. This value indicates that the gender of the customer is male.

    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 change the data type of a column only when its data type is integer or floating-point. You can only change data types with a smaller value range to those with a larger 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 smaller value range to those with a larger value range. For example, you can change the data type of a column 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 from FLOAT to DOUBLE, but not from DOUBLE to FLOAT.
  • Examples

    Create a table named test that contains the order_number column in the INT data type.

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

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

    ALTER TABLE adb_demo.test MODIFY COLUMN order_number BIGINT NOT NULL DEFAULT 100;

Create 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 create 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 an index

  • Syntax
    ALTER TABLE db_name.table_name DROP KEY index_name;
  • Parameter description
    You can execute the following statement to query the indexes specified by index_name:
    SHOW INDEXES FROM db_name.table_name;
  • Example

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

    ALTER TABLE adb_demo.customer DROP KEY age_idx;

Change the name of a table

  • Syntax
    ALTER TABLE db_name.table_name RENAME new_table_name;
  • Examples

    Change the name of a table from customer to new_customer.

    ALTER TABLE customer RENAME new_customer;

Change the name of a column

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

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

    ALTER TABLE customer RENAME COLUMN age to new_age;

Change the lifecycle of a table

  • Syntax
    ALTER TABLE db_name.table_name PARTITIONS N;
  • Examples

    Create the customer table whose lifecycle is set to 30.

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

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

    ALTER TABLE customer PARTITIONS 40;

Change the hot and cold data storage policy of a table

Note Only AnalyticDB for MySQL clusters in support tiered storage of hot and cold data.

You can execute the ALTER TABLE statement to change 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, and 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 about how to specify the hot and cold data 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 the number of partitions that use the hot storage policy to 10
ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10;