AnalyticDB for MySQL allows you to modify tables by executing the ALTER TABLE statement. This topic describes how to use the ALTER TABLE statement.

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 COLUMN column_name to new_column_name
  | 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 of the VARCHAR data type 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 comment of a column

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

    Change the comment 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;
  • Precautions
    Data types can be changed only within integer types or floating-piont types, and only from types with a narrow value range to those with a wide one. Data type changes are not supported between an integer type and a floating-piont type.
    • Integer data types include TINYINT, SMALLINT, INT, and BIGINT. Data types can be changed only from a type of a smaller value range to that of a larger value range, not the other way around. 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.
  • Example

    Create a table named test that contains the order_number column of 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

By default, index_all='Y' is used to create an index for all columns when a table is created in AnalyticDB for MySQL. If no index is created for all columns, you can use the following method to create an index. After an index is changed, BUILD operations must be performed. For more information, see BUILD.

Note AnalyticDB for MySQL does not allow you to create unique indexes.
  • 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;
  • Example

    Change the name of a table from customer to new_customer.

    ALTER TABLE customer RENAME new_customer;

Change the name of a column

Note The names of primary key columns cannot be changed.
  • Syntax
    ALTER TABLE db_name.table_name RENAME COLUMN column_name to new_column_name;
  • 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;
  • Example

    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 Elastic mode for Cluster Edition 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 is not immediately changed when 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 a 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 specify the number of hot partitions to 10
ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10;

Change a partition or distribution key

AnalyticDB for MySQL does not allow you to change partition or distribution keys. If you want to use a different partition or distribution key for your table, perform the following steps.

In this example, a table named order is used. Its distribution key is intended to be changed from order_id to customer_id.
  1. Create a temporary table named order_auto_opt_v1 by using the distribution key customer_id.
    CREATE TABLE order_auto_opt_v1 (
      order_id bigint NOT NULL COMMENT 'Order ID',
      customer_id bigint NOT NULL COMMENT 'Customer ID',
      customer_name varchar NOT NULL COMMENT 'Customer name',
      order_time timestamp NOT NULL COMMENT 'Order time',
      --Other fields are omitted.
      PRIMARY KEY (order_id, customer_id,order_time) --Add the distribution key customer_id and the partition key order_time to the primary key.
    )
    DISTRIBUTED BY HASH(customer_id) --Change the distribution key from order_id to customer_id.
    PARTITION BY VALUE(DATE_FORMAT(order_time, '%Y%m%d')) LIFECYCLE 90 --Retain list partition settings.
    COMMENT 'Order information table';
  2. Execute the INSERT OVERWRITE INTO SELECT statement to import data from the source table to the temporary table. For more information, see INSERT OVERWRITE INTO SELECT.
    INSERT OVERWRITE INTO order_auto_opt_v1
    SELECT * FROM order;
  3. After data is imported, check for issues related to the new distribution key, such as data skew. For more information, see Diagnostics on distribution field skew.
  4. Execute the RENAME TABLE <Name of the source table> to <New name of the source table>; statement to change the name of the source table.
    RENAME TABLE order to order_backup; --After data is imported, rename the source table as a backup.
  5. Execute the RENAME TABLE <Name of the temporary table> to <Name of the source table>; statement to change the name of the temporary table to the name of the source table.
    RENAME TABLE order_auto_opt_v1 to order;