All Products
Search
Document Center

AnalyticDB for MySQL:ALTER TABLE

Last Updated:Sep 26, 2023

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]}
  | PARTITION BY VALUE(column_name|date_format(column_name,'xxxxx')) LIFECYCLE N
  | RT_ENGINE= {'ROWSTORE'|'COLUMNSTORE'} }
Important

You cannot modify the order of columns.

Add a column

  • Syntax

    ALTER TABLE db_name.table_name ADD [COLUMN] column_name data_type;
  • Usage notes

    You cannot add primary key columns.

  • Example

    Add the province column of the VARCHAR 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;
  • Usage notes

    You cannot remove primary key columns.

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

  • Syntax

    ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type {NULL};
  • Usage notes

    You can change the value constraint from NOT NULL to NULL, but not the other way around.

  • 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;
  • Usage notes

    • You cannot change the data type of primary key columns.

    • Data type changes are subject to the following limits:

      • You can change data types only from types with a narrow value range to types with a wide value range.

      • Integer data types include TINYINT, SMALLINT, INT, and BIGINT. You can change data types only from types with a narrow value range to types with a wide value range. For example, you can change the data type of a column from TINYINT to BIGINT, but not the other way around.

      • Floating-point data types include FLOAT and DOUBLE. You can change the data type of a column from FLOAT to DOUBLE, but not the other way around.

      • You can change an integer type such as TINYINT, SMALLINT, INT, BIGINT, SHORT, and LONG to a floating-point type such as FLOAT and DOUBLE, or the DECIMAL type.

      • You can change the precision of the DECIMAL type only from low to high.

      Important

      Only AnalyticDB for MySQL clusters of V3.1.8 support the feature of changing an integer type to a floating-point type or the DECIMAL type, and changing the precision of the DECIMAL type. The feature is now in public preview. To enable the feature, contact Alibaba Cloud technical support to update the minor version of your cluster.

      For information about how to query the minor version of a cluster, see the "How do I query the minor version of an AnalyticDB for MySQL cluster?" section of the Purchase, configuration change, and upgrade topic.

  • Example

    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

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.

Important

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;
  • Parameters

    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

  • Syntax

    ALTER TABLE db_name.table_name RENAME COLUMN column_name to new_column_name;
  • Usage notes

    You cannot change the names of primary key columns.

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

    The lifecycle of a table 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. After the execution is complete, the change takes effect. For information about how to query the state of a BUILD task, see the "Query the state of a BUILD task" section of the BUILD topic.

    To view the lifecycle of a table, you can execute the SHOW CREATE TABLE <table_name>; statement and view the value of PARTITION BY in the results.

  • 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 after the ALTER TABLE statement is executed. To trigger an immediate change, you can execute the BUILD TABLE db_name.table_name statement. After the execution is complete, the change takes effect. For information about how to query the state of a BUILD task, see the "Query the state of a BUILD task" section of the BUILD topic.

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

  • Example 1: Change the storage policy of a table to COLD

    ALTER TABLE test_table storage_policy = 'COLD';
  • Example 2: Change the storage policy of a table to HOT

    ALTER TABLE test_table storage_policy = 'HOT';
  • Example 3: Change the storage policy of a table to MIXED and set the number of hot partitions to 10

    ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10;

Change the partition function of a table

Important

By default, the partition function change feature is disabled for AnalyticDB for MySQL clusters. Only AnalyticDB for MySQL clusters of V3.1.6 or later support the feature. To enable the feature, contact Alibaba Cloud technical support to update the minor version of your cluster.

For information about how to query the minor version of a cluster, see the "How do I query the minor version of an AnalyticDB for MySQL cluster?" section of the Purchase, configuration change, and upgrade topic.

  • Syntax

    ALTER TABLE table_name PARTITION BY VALUE(column_name|date_format(column_name,'xxxxx')) LIFECYCLE N; 
  • Usage notes

    • You cannot add a partition field to a non-partitioned table.

    • You can change the partition function but not the partition field of a table.

  • Example 1

    ALTER TABLE adb_demo.test PARTITION BY VALUE(c2) LIFECYCLE 10;
  • Example 2

    ALTER TABLE adb_demo.test PARTITION BY VALUE(date_format(c2, '%Y%m%d')) LIFECYCLE 10;

Change a partition or distribution key

AnalyticDB for MySQL does not allow you to change or add 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 SELECT statement to import data from the source table to the temporary table. For more information, see INSERT OVERWRITE SELECT.

    INSERT OVERWRITE 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 the "Diagnostics on distribution field skew" section of the Data modeling diagnostics topic.

  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;