AnalyticDB:ALTER TABLE

Rename a table

ALTER TABLE db_name.table_name RENAME new_table_name

Example: Rename customer to new_customer.

ALTER TABLE customer RENAME new_customer;

Change a table comment

ALTER TABLE db_name.table_name COMMENT 'comment'

Example: Update the comment on the customer table.

ALTER TABLE customer COMMENT 'Customer table';

Add a column

ALTER TABLE db_name.table_name ADD [COLUMN]
  {column_name column_type [DEFAULT {constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT] [NULL|NOT NULL] [COMMENT 'comment']
  | (column_name column_type [DEFAULT {constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT] [NULL|NOT NULL] [COMMENT 'comment'],...)}

Primary key columns cannot be added.

Example 1: Add a province column of the VARCHAR type to customer.

ALTER TABLE adb_demo.customer ADD COLUMN province VARCHAR COMMENT 'Province';

Example 2: Add two columns at once — vip of the BOOLEAN type and tags of the VARCHAR type.

ALTER TABLE adb_demo.customer ADD COLUMN (vip BOOLEAN COMMENT 'Is VIP', tags VARCHAR DEFAULT 'None' COMMENT 'Tag');

Drop a column

ALTER TABLE db_name.table_name DROP [COLUMN] column_name

Primary key columns cannot be dropped.

Example: Drop the province column from customer.

ALTER TABLE adb_demo.customer DROP COLUMN province;

Rename a column

ALTER TABLE db_name.table_name RENAME COLUMN column_name TO new_column_name

Primary key columns cannot be renamed.

Example: Rename city_name to city in customer.

ALTER TABLE customer RENAME COLUMN city_name TO city;

Change a column data type

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name new_column_type

Data type changes follow widening-only rules — you can expand a type's range but not shrink it. The table below summarizes supported changes:

Example: Change the age column from INT to BIGINT.

ALTER TABLE adb_demo.customer MODIFY COLUMN age BIGINT;

Change a column default value

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_type DEFAULT {constant | CURRENT_TIMESTAMP}

Example 1: Set the default value of sex to 0.

ALTER TABLE adb_demo.customer MODIFY COLUMN sex INT NOT NULL DEFAULT 0;

Example 2: Set the default value of login_time to CURRENT_TIMESTAMP.

ALTER TABLE adb_demo.customer MODIFY COLUMN login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Allow NULL values

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_type NULL

Only NOT NULL to NULL changes are supported. Changing NULL to NOT NULL is not supported.

Example: Allow province to accept NULL values.

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR NULL;

Change a column comment

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_type COMMENT 'new_comment'

Example: Update the comment on the province column.

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR COMMENT 'The province where the customer is located';

Add a regular index

By default, XUANWU_V2 tables are created without full-column indexes (INDEX_ALL='N'), while XUANWU tables include them (INDEX_ALL='Y'). Add an index to individual columns as needed.

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name)

The column must have a simple data type. For JSON columns, see Add a JSON index.

Example: Add an index to the age column.

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

Modify full-column indexes

For XUANWU_V2 tables, toggle full-column indexing after table creation using the INDEX_ALL property. This setting does not affect JSON, full-text, or vector indexes.

ALTER TABLE db_name.table_name INDEX_ALL = {'Y'|'N'};

ALTER TABLE adb_demo.customer INDEX_ALL = 'N';

ALTER TABLE adb_demo.customer INDEX_ALL = 'Y';

Add a JSON index

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name|column_name->'$.json_path')

CREATE TABLE json_test(
  id INT,
  vj JSON
)
DISTRIBUTED BY HASH(id);

INSERT INTO json_test VALUES(1,'{"a":1,"b":2}'),(2,'{"a":2,"b":3}');

ALTER TABLE json_test ADD KEY age_idx(vj->'$.a');

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name->'$[*]')

CREATE TABLE json_test(
  id INT,
  vj JSON
)
DISTRIBUTED BY HASH(id);

INSERT INTO json_test VALUES(1, '["CP-018673", 1, false]');

ALTER TABLE json_test ADD KEY index_vj(vj->'$[*]');

Drop a regular or JSON index

ALTER TABLE db_name.table_name DROP KEY index_name

Run SHOW INDEX FROM db_name.table_name; to find the index name.

Example 1: Drop the age_idx index from customer.

ALTER TABLE adb_demo.customer DROP KEY age_idx;

Example 2: Drop the JSON Array index index_vj from json_test.

ALTER TABLE adb_demo.customer DROP KEY index_vj;

Add a clustered index

ALTER TABLE db_name.table_name ADD CLUSTERED [INDEX|KEY] [index_name] (column_name1 [ASC|DESC], column_name2 [ASC|DESC])

Usage notes:

• Clustered indexes sort in ascending order (ASC) by default. For workloads that sort in descending order, set DESC when you create the table.

• A table can have only one clustered index.

• After adding a clustered index, trigger and complete a BUILD job for it to take effect. Run SHOW CREATE TABLE db_name.table_name; to confirm.

Example: Add a clustered index on customer_id.

ALTER TABLE adb_demo.customer ADD CLUSTERED KEY (customer_id ASC);

Drop a clustered index

ALTER TABLE db_name.table_name DROP CLUSTERED KEY index_name

Run SHOW CREATE TABLE db_name.table_name to find the clustered index name.

Example: Drop the clustered index named index from customer.

ALTER TABLE adb_demo.customer DROP CLUSTERED KEY index;

Add a full-text index

ALTER TABLE db_name.table_name ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]

ALTER TABLE adb_demo.customer ADD FULLTEXT INDEX fidx_k(home_address) WITH ANALYZER standard;

Drop a full-text index

ALTER TABLE db_name.table_name DROP FULLTEXT INDEX index_name

Example: Drop the fidx_k full-text index from customer.

ALTER TABLE adb_demo.customer DROP FULLTEXT INDEX fidx_k;

Add a vector index

ALTER TABLE db_name.table_name ADD ANN [INDEX|KEY] [index_name] (column_name) [algorithm=HNSW_PQ] [distancemeasure=SquaredL2]

CREATE TABLE vector (
  xid BIGINT NOT NULL,
  cid BIGINT NOT NULL,
  uid VARCHAR NOT NULL,
  vid VARCHAR NOT NULL,
  wid VARCHAR NOT NULL,
  float_feature array<FLOAT>(4),
  short_feature array<SMALLINT>(4),
  PRIMARY KEY (xid, cid, vid)
) DISTRIBUTED BY HASH(xid);

ALTER TABLE vector ADD ANN INDEX idx_float_feature(float_feature);
ALTER TABLE vector ADD ANN INDEX idx_short_feature(short_feature);

Add a foreign key

ALTER TABLE db_name.table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES db_name.pk_table_name (pk_column_name)

CREATE TABLE item
(
 i_item_sk BIGINT NOT NULL,
 i_current_price BIGINT,
 PRIMARY KEY(i_item_sk)
)
DISTRIBUTED BY HASH(i_item_sk);

CREATE TABLE store_sales
(
 ss_sale_id BIGINT,
 ss_store_sk BIGINT,
 ss_item_sk BIGINT NOT NULL,
 PRIMARY KEY(ss_sale_id)
);

ALTER TABLE store_sales ADD CONSTRAINT ss_item_sk FOREIGN KEY (ss_item_sk) REFERENCES item (i_item_sk);

Drop a foreign key

ALTER TABLE db_name.table_name DROP FOREIGN KEY fk_symbol

Example:

ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

Change the partition function format

Prerequisites

To change the partition function, your AnalyticDB for MySQL cluster must meet all of the following conditions:

• The kernel version is 3.2.1.6, 3.2.2.5, 3.2.3.1, or 3.2.4.0 or later.

  Note

  To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

• Contact Alibaba Cloud technical support to enable the feature that lets you change the partition function.

Syntax

ALTER TABLE table_name PARTITION BY VALUE{(column_name)|(DATE_FORMAT(column_name, 'format'))|(FROM_UNIXTIME(column_name, 'format'))} LIFECYCLE N 

Notes

• For XUANWU tables in clusters with a kernel version of 3.2.4.1 or later, you can remove the partition lifecycle by setting the LIFECYCLE parameter N to 0 or by omitting LIFECYCLE N.

• You cannot change a non-partitioned table to a partitioned table, or a partitioned table to a non-partitioned table. This means you cannot add or delete partition keys.

• You cannot add, delete, or change partition fields based on existing partition keys.

• You can change the partition function between PARTITION BY VALUE(column) and PARTITION BY VALUE(DATE_FORMAT(...)) or PARTITION BY VALUE(FROM_UNIXTIME(...)).

• You can change the format.

• After you change the partition function, the new partition function format takes effect only after a BUILD job for the table is re-triggered and completed. You can run the SHOW CREATE TABLE db_name.table_name; statement to check whether the new partition function format has taken effect.

Examples

• Change the partitioning method for the customer table from PARTITION BY VALUE(login_time) LIFECYCLE 10 to PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m')) LIFECYCLE 20.

  ALTER TABLE adb_demo.test PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m')) LIFECYCLE 20;

• Change the partitioning method for the customer table from PARTITION BY VALUE(FROM_UNIXTIME(login_time, '%Y%m%d')) LIFECYCLE 20 to PARTITION BY VALUE(FROM_UNIXTIME(login_time, '%Y%m')) LIFECYCLE 30.

  ALTER TABLE adb_demo.test PARTITION BY VALUE(FROM_UNIXTIME(login_time, '%Y%m')) LIFECYCLE 30;

• Change the partitioning method for the customer table from PARTITION BY VALUE(login_time) LIFECYCLE 20 to PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m')) .

  ALTER TABLE adb_demo.test PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m'));

Change the partition function format

Prerequisites

To change the partition function, your AnalyticDB for MySQL cluster must meet all of the following conditions:

• The kernel version is 3.2.1.6, 3.2.2.5, 3.2.3.1, or 3.2.4.0 or later.

  Note

  To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

• Contact Alibaba Cloud technical support to enable the feature that lets you change the partition function.

Syntax

ALTER TABLE table_name PARTITION BY VALUE{(column_name)|(DATE_FORMAT(column_name, 'format'))|(FROM_UNIXTIME(column_name, 'format'))} LIFECYCLE N 

Notes

• For XUANWU tables in clusters with a kernel version of 3.2.4.1 or later, you can remove the partition lifecycle by setting the LIFECYCLE parameter N to 0 or by omitting LIFECYCLE N.

• You cannot change a non-partitioned table to a partitioned table, or a partitioned table to a non-partitioned table. This means you cannot add or delete partition keys.

• You cannot add, delete, or change partition fields based on existing partition keys.

• You can change the partition function between PARTITION BY VALUE(column) and PARTITION BY VALUE(DATE_FORMAT(...)) or PARTITION BY VALUE(FROM_UNIXTIME(...)).

• You can change the format.

• After you change the partition function, the new partition function format takes effect only after a BUILD job for the table is re-triggered and completed. You can run the SHOW CREATE TABLE db_name.table_name; statement to check whether the new partition function format has taken effect.

Examples

• Change the partitioning method for the customer table from PARTITION BY VALUE(login_time) LIFECYCLE 10 to PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m')) LIFECYCLE 20.

  ALTER TABLE adb_demo.test PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m')) LIFECYCLE 20;

• Change the partitioning method for the customer table from PARTITION BY VALUE(FROM_UNIXTIME(login_time, '%Y%m%d')) LIFECYCLE 20 to PARTITION BY VALUE(FROM_UNIXTIME(login_time, '%Y%m')) LIFECYCLE 30.

  ALTER TABLE adb_demo.test PARTITION BY VALUE(FROM_UNIXTIME(login_time, '%Y%m')) LIFECYCLE 30;

• Change the partitioning method for the customer table from PARTITION BY VALUE(login_time) LIFECYCLE 20 to PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m')) .

  ALTER TABLE adb_demo.test PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m'));

Change the partition lifecycle

ALTER TABLE db_name.table_name PARTITIONS N

Usage notes:

• On clusters with kernel version 3.2.4.1 or later, set N to 0 to remove partition lifecycle management.

• The new lifecycle takes effect only after a BUILD job is triggered and completed. Run SHOW CREATE TABLE db_name.table_name; to verify.

Example 1: Remove the lifecycle from customer.

ALTER TABLE customer PARTITIONS 0;

Example 2: Change the lifecycle from 30 days to 40 days.

ALTER TABLE customer PARTITIONS 40;

Drop a partition

ALTER TABLE DROP PARTITION has the same effect as TRUNCATE TABLE PARTITION.

ALTER TABLE db_name.table_name DROP PARTITION (partition_name,...)

Example 1: Drop the 20241220 partition from customer.

ALTER TABLE adb_demo.customer DROP PARTITION (20241220);

Example 2: Drop the 20241218 and 20241219 partitions.

ALTER TABLE adb_demo.customer DROP PARTITION (20241218,20241219);

Change the tiered storage policy

ALTER TABLE db_name.table_name STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' hot_partition_count=N}

ALTER TABLE customer storage_policy = 'COLD';

ALTER TABLE customer storage_policy = 'HOT';

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