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}

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

    Change 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 an 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

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