AnalyticDB:ALTER TABLE

Change the table name

Syntax

ALTER TABLE db_name.table_name RENAME new_table_name

Example

Rename the customer table to new_customer.

ALTER TABLE customer RENAME new_customer;

Change the table comment

Syntax

ALTER TABLE db_name.table_name COMMENT 'comment'

Example

Change the comment for the customer table to 'Customer table'.

ALTER TABLE customer COMMENT 'Customer table';;

Add a column

Syntax

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

Notes

You cannot add primary key columns.

Examples

• Add a column named province of the VARCHAR type to the customer table.

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

• Add two columns to the customer table: a column named vip of the BOOLEAN type and a column named tags of the VARCHAR type.

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

Delete a column

Syntax

ALTER TABLE db_name.table_name DROP [COLUMN] column_name

Notes

You cannot delete primary key columns.

Example

Delete the province column of the VARCHAR type from the customer table.

ALTER TABLE adb_demo.customer DROP COLUMN province;

Change the column name

Syntax

ALTER TABLE db_name.table_name RENAME COLUMN column_name to  new_column_name

Notes

You cannot change the name of a primary key column.

Example

Change the name of the city_name column to city in the customer table.

ALTER TABLE customer RENAME COLUMN city_name to city;

Change the column data type

Syntax

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name new_column_type

Notes

• You cannot change the data type of a primary key column.

• The following limits apply to data type changes:

  • You can only change a data type to another data type with a wider value range.

  • Integer data types: You can change a smaller integer type to a larger one, such as from TINYINT to BIGINT. You cannot change from BIGINT to TINYINT. Supported integer types are TINYINT, SMALLINT, INT, and BIGINT.

  • Floating-point data types: You can change FLOAT to DOUBLE, but you cannot change DOUBLE to FLOAT.

  • You can change an integer data type (TINYINT, SMALLINT, INT, or BIGINT) to a floating-point data type (FLOAT or DOUBLE).

  • You can increase the precision of a DECIMAL type. You cannot decrease the precision.

  Important

  Only clusters with a kernel version of 3.1.8.10 to 3.1.8.x, 3.1.9.6 to 3.1.9.x, 3.1.10.3 to 3.1.10.x, or 3.2.0.1 or later support changing an integer data type to a floating-point data type and changing the precision of the DECIMAL type.

Example

Change the data type of the age column in the customer table from INT to BIGINT.

ALTER TABLE adb_demo.customer MODIFY COLUMN age BIGINT;

Change the column default value

Syntax

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

Examples

• Set the default value of the sex column in the customer table to 0.

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

• Set the default value of the login_time column in the customer table to CURRENT_TIMESTAMP.

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

Allow NULL values

Syntax

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

Notes

You can only change a NOT NULL constraint to NULL.

Example

Allow the province column in the customer table to accept NULL values.

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR NULL;

Change the column comment

Syntax

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_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';

Add an index

By default, AnalyticDB for MySQL does not create full-column indexes for XUANWU_V2 tables (INDEX_ALL='N'), but creates them for XUANWU tables (INDEX_ALL='Y'). If full-column indexes were not created when you created a XUANWU_V2 or XUANWU table, you can add an index to a column.

Syntax

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

Parameter description

column_name: The name of the column. The column must have a simple data type. If the column is of the JSON data type, see Add a JSON index.

Example

Add an index to the age column in the customer table.

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

Modify full-column indexes

For XUANWU_V2 tables in AnalyticDB for MySQL, you can modify full-column indexes after the table is created. This is configured using the table property INDEX_ALL. This operation does not affect non-regular indexes, such as JSON, full-text, and vector indexes.

Prerequisites

The XUANWU_V2 table is in a cluster with a major engine version of 3.2.3.7 or later, or 3.2.4.3 or later.

Syntax

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

Parameter description

INDEX_ALL: Specifies whether to create regular indexes for all columns. Valid values:

• Y: Full-column index mode. Creates regular indexes for all columns.

  If you change the table property from INDEX_ALL='N' to INDEX_ALL='Y', regular indexes are created for all columns that do not have one.

• N: Non-full-column index mode. Creates a regular index only for the primary key. Other columns will not have regular indexes.

  If you change the table property from INDEX_ALL='Y' to INDEX_ALL='N', all regular indexes are deleted, except for the index on the primary key column.

Notes

• For XUANWU tables, the full-column index feature can be configured only during table creation. To disable full-column indexing, you must manually delete the indexes by running a statement to delete the index.

• If INDEX_ALL='Y', running a Data Definition Language (DDL) statement to delete a regular index automatically changes the property from INDEX_ALL='Y' to INDEX_ALL='N'. This operation deletes only the target regular index and does not affect other regular indexes.

• When the table property is INDEX_ALL='N', the SHOW CREATE TABLE statement might not explicitly display the property as INDEX_ALL='N'. However, the property that takes effect is still INDEX_ALL='N'.

Examples

• Assume the customer table has INDEX_ALL='Y'. Run the following statement to change the table property to INDEX_ALL='N'.

  ALTER TABLE adb_demo.customer INDEX_ALL = 'N';

  After the statement is executed, the regular indexes on all non-primary key columns, such as customer_name, city_name, and sex, are deleted.

• Assume the customer table has INDEX_ALL='N', and the customer_id, phone_num, and login_time fields already have indexes. Run the following statement to change the table property to INDEX_ALL='Y'.

  ALTER TABLE adb_demo.customer INDEX_ALL = 'Y';

  After the statement is executed, regular indexes are created for columns that do not already have one, such as customer_name, city_name, and sex.

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->'$[*]');

Delete an index or JSON index

ALTER TABLE db_name.table_name DROP KEY index_name

Add a clustered index

Syntax

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

Note

• By default, clustered indexes are sorted in ascending order (ASC) and are suitable for queries that sort in ascending order. If your queries sort in descending order, set the clustered index to descending order (DESC) when you create the table.

• A table can have only one clustered index. You cannot add another clustered index if one already exists.

• After a clustered index is added, you must trigger and complete a BUILD job to make the clustered index effective. You can execute the SHOW CREATE TABLE db_name.table_name; statement to determine if the clustered index has taken effect.

Example

You can add a clustered index to the customer_id column in the customer table.

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

Delete a clustered index

Syntax

ALTER TABLE db_name.table_name DROP CLUSTERED KEY index_name

Parameter description

index_name: The name of the clustered index. You can run SHOW CREATE TABLE db_name.table_name to find the clustered index name.

Example

Delete the clustered index named index from the customer table.

ALTER TABLE adb_demo.customer DROP CLUSTERED KEY index;

Add a full-text index

Prerequisites

Syntax

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

Parameter description

• column_name: The column on which to create the full-text index. The column must be of the VARCHAR type.

• index_option: Optional. Specifies the tokenizer and custom dictionary for the full-text index.

  • WITH ANALYZER analyzer_name: specifies the analyzer used for the full-text index. For more information about the analyzers supported by AnalyticDB for MySQL, see Analyzers for full-text indexes.

  • WITH DICT tbl_dict_name: specifies the custom dictionary used for the full-text index. For more information about the custom dictionaries supported by AnalyticDB for MySQL, see Custom dictionaries for full-text indexes.

Notes

A full-text index that you create takes effect only after a BUILD job is re-triggered and completed.

Example

Add a full-text index to the home_address column of the customer table.

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

Delete a full-text index

Syntax

ALTER TABLE db_name.table_name DROP FULLTEXT INDEX index_name

Parameter description

• column_name: The name of the column on which to create the full-text index. The column must be of the VARCHAR type.

• index_option: The analyzer and custom dictionary to use for the full-text index. This parameter is optional.

  • WITH ANALYZER analyzer_name: specifies the analyzer used for the full-text index. For more information about the analyzers supported by AnalyticDB for MySQL, see Analyzers for full-text indexes.

  • WITH DICT tbl_dict_name: specifies the custom dictionary used for the full-text index. For more information about the custom dictionaries supported by AnalyticDB for MySQL, see Custom dictionaries for full-text indexes.

Example

Delete the full-text index fidx_k from the customer table.

ALTER TABLE adb_demo.customer DROP FULLTEXT INDEX fidx_k;

For more information, see Create a full-text index.

Add a vector index

Prerequisites

An AnalyticDB for MySQL cluster of V3.1.4.0 or later is created.

Syntax

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

Parameter description

• index_name: the name of the index. For information about the naming conventions of indexes, see the "Naming limits" section of the Limits topic.

• column_name: The name of the vector column. The column type must be array <float>, array <byte>, or array <smallint>.

• algorithm: the algorithm that is used to calculate the vector distance. Set the value to HNSW_PQ.

• distancemeasure: the formula that is used to calculate the vector distance. Set the value to SquaredL2. Calculation formula of SquaredL2: (x1 - y1)2 + (x2 - y2)2 + ...(xn - yn)2.

Example

Assume that you have a table named vector that was created using the following statement.

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

Create vector indexes on the float_feature and short_feature columns.

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

Prerequisites

Only AnalyticDB for MySQL clusters of V3.1.10 or later support the FOREIGN KEY clause.

Syntax

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

Parameter description

• db_name.table_name: The table to which you want to add the foreign key.

• symbol: the name of the foreign key constraint. The name must be unique in a table. This parameter is optional. If you do not specify this parameter, the parser automatically uses the name of the foreign key column suffixed with _fk as the name of the foreign key constraint.

• fk_column_name: the name of the foreign key column. The column must already exist.

• pk_table_name: the name of the primary table. The primary table must already exist.

• pk_column_name: the name of the foreign key constraint column, which is the primary key column of the primary table. The column must already exist.

Notes

• Each table can have multiple foreign key indexes.

• A foreign key index cannot consist of multiple columns, such as FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk).

• AnalyticDB for MySQL does not check data constraints. You must check the data constraint relationships between the primary key of the primary table and the foreign keys of the associated table.

• You cannot add foreign key constraints to external tables.

Example

Assume that you have two tables, item and store_sales, that were created using the following statements.

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

Add a foreign key ss_item_sk to the store_sales table and associate it with the i_item_sk key of the item table.

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

For more information, see Eliminate unnecessary joins using primary and foreign key constraints.

Delete a foreign key

Syntax

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 lifecycle

Syntax

ALTER TABLE db_name.table_name PARTITIONS N

Notes

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

• After you change the lifecycle of a partition, a BUILD task must be triggered and completed for the new lifecycle to take effect. You can run the SHOW CREATE TABLE db_name.table_name; statement to check whether the new lifecycle has taken effect.

Example 1

Remove the lifecycle from the customer table.

ALTER TABLE customer PARTITIONS 0;

Example 2

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

ALTER TABLE customer PARTITIONS 40;

Delete a partition

Syntax

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

Notes

After you delete a partition, the data in that partition is also deleted and cannot be recovered. Exercise caution when you perform this operation.

Examples

• Delete the 20241220 partition from the customer table.

  ALTER TABLE adb_demo.customer DROP PARTITION (20241220);

• Delete the 20241218 and 20241219 partitions from the customer table.

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

Change the tiered storage policy

Prerequisites

• The cluster is of the Enterprise Edition, Basic Edition, Data Lakehouse Edition, or Data Warehouse Edition (Elastic mode).

• Kernel version:

  • If the table engine is XUANWU, there are no restrictions on the cluster kernel version.

  • If the table engine is XUANWU_V2, the cluster kernel version must meet one of the following conditions:

    • 3.2.2.15 or later.

    • 3.2.3.13 or later.

    • 3.2.4.9 or later.

    • 3.2.5.3 or later.

    Note

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

• If the table engine is XUANWU_V2, the scheduled task for moving data between hot and cold storage must be enabled on the cluster:

  • Check whether the task is enabled: SHOW ADB_CONFIG KEY=SERVERLESS_DATA_STORAGE_CHANGE_SCHEDULE_ENABLE;. If the command returns FALSE, the task is disabled and you must enable it. If an error is returned, the parameter is not set, and the default value is TRUE.

  • Enable the task: SET ADB_CONFIG SERVERLESS_DATA_STORAGE_CHANGE_SCHEDULE_ENABLE = true;.

Syntax

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

Usage notes

After you change the tiered storage policy for hot and cold data, the new policy takes effect only after a BUILD job for the table is triggered and completed. By default, this job runs automatically in the background at regular intervals. Before the BUILD job is complete, it is normal for the number of hot partitions returned by information_schema.table_usage to be inconsistent with the defined storage policy. You can run SHOW CREATE TABLE db_name.table_name; to check whether the new storage policy has taken effect.

Examples

• Change the storage policy of the customer table to COLD.

  ALTER TABLE customer storage_policy = 'COLD';

• Change the storage policy of the customer table to HOT.

  ALTER TABLE customer storage_policy = 'HOT';

• Change the storage policy of the customer table to MIXED and set the number of hot partitions to 10.

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