AnalyticDB:ALTER 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'],...)}

Usage notes

You cannot add primary key columns.

Examples

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

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

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

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

Remove a column

Syntax

ALTER TABLE db_name.table_name DROP [COLUMN] column_name

Usage notes

You cannot remove primary key columns.

Example

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

ALTER TABLE adb_demo.customer DROP COLUMN province;

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 name of a primary key column.

Example

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

ALTER TABLE customer RENAME COLUMN city_name to city;

Change the data type of a column

Syntax

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name new_column_type

Usage notes

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

• 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 with a narrow value range to those 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 from DOUBLE to FLOAT.

  • An integer data type such as TINYINT, SMALLINT, INT, or BIGINT can be changed to a floating-point data type such as FLOAT or DOUBLE.

  • The precision of the DECIMAL type can be changed from low to high.

  Important

  Only AnalyticDB for MySQL clusters whose minor versions are 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, and 3.2.0.1 or later support the features of 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 the BIGINT.

ALTER TABLE adb_demo.customer MODIFY COLUMN age BIGINT;

Change the default value of a column

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;

Change the value constraint for a column to NULL

Syntax

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

Usage notes

You can change the value constraint for a column from NOT NULL to NULL, but not from NULL to NOT NULL.

Example

Change the value constraint for the province column in the customer table to NULL.

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR NULL;

Change the comment of a column

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

Create an index

When you create a table in AnalyticDB for MySQL, index_all='Y' is used to create a full-column index by default. If no full-column index is created, you can use the following method to create an index.

Syntax

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

Parameters

column_name: the name of the column. The preceding statement is used to create an index on columns of simple data types. To create an index on columns of the JSON type, see the "Create a JSON index" section of this topic.

Example

Create an index on the age column in the customer table.

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

Create 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

Create a clustered index

Syntax

ALTER TABLE db_name.table_name ADD CLUSTERED [INDEX|KEY] [index_name] (column_name,...)

Usage notes

• You cannot create a clustered index for a table that already has a clustered index. A table can have only one clustered index.

• The created clustered index takes effect only after the BUILD job is complete. For information about automatic BUILD jobs, manual BUILD jobs, and the status of BUILD jobs, see BUILD. You can execute the SHOW CREATE TABLE db_name.table_name; statement to query whether the clustered index takes effect.

Example

Create a clustered index on the customer_id column in the customer table.

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

Delete a clustered index

Syntax

ALTER TABLE db_name.table_name DROP CLUSTERED KEY index_name

Parameters

index_name: the name of the clustered index that you want to delete. You can execute the SHOW CREATE TABLE db_name.table_name statement to query the name of the clustered index.

Example

Delete the clustered index on the customer_id column from the customer table.

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

Create a full-text index

Prerequisites

Syntax

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

Parameters

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

• index_option: the analyzer and the custom dictionary that you want 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.

Usage notes

The created full-text index takes effect only after the BUILD job is complete. For information about automatic BUILD jobs, manual BUILD jobs, and the status of BUILD jobs, see BUILD.

Example

Create a full-text index on the home_address column in 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

Parameters

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

• index_option: the analyzer and the custom dictionary that you want 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 named 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.

Create 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]

Parameters

• 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 vector column must be of the ARRAY <FLOAT>, ARRAY <BYTE>, or ARRAY <SMALLINT> type.

• 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

The vector table is created by executing 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 for the float_feature and short_feature columns in the vector table.

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

Create a foreign key index

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)

Parameters

• db_name.table_name: the name of the table for which you want to create the foreign key index.

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

Usage 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

The item and store_sales tables are created by executing 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)
);

Create a foreign key index named ss_item_sk for the store_sales table and allow the foreign key index to be associated 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 Use primary and foreign key constraints to eliminate unnecessary joins.

Delete a foreign key index

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 function format of a table

Prerequisites

Before you change the partition function format, make sure that your AnalyticDB for MySQL cluster meets the following requirements:

• The minor version of the cluster is 3.1.6 or later.

  Note

  To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.

• Contact Alibaba Cloud technical support to allow you to change the partition function format.

Syntax

ALTER TABLE table_name PARTITION BY VALUE(column_name|date_format(column_name,'format')) LIFECYCLE N 

Usage notes

• You cannot change a non-partitioned table to a partitioned table or a partitioned table to a non-partitioned table. Essentially, you cannot add or remove partition keys.

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

• For AnalyticDB for MySQL clusters earlier than V3.2.1.1 that allow you to manage the partition lifecycle at the shard level, the new partition function format takes effect only after the BUILD job is complete.

• For AnalyticDB for MySQL clusters of V3.2.1.1 or later that allow you to manage the partition lifecycle at the table level, you must run two BUILD jobs after you change the partition function format. After the first BUILD job is complete, the new partition function format takes effect but the partition lifecycle is managed at the shard level. After the second BUILD job is complete, table-level partition lifecycle management takes effect.

Examples

• Change the partition function format of the customer table from PARTITION BY VALUE(date_format(login_time, '%Y%m%d')) LIFECYCLE 30 to PARTITION BY VALUE(login_time) LIFECYCLE 10.

ALTER TABLE adb_demo.customer PARTITION BY VALUE(login_time) LIFECYCLE 10;

• Change the partition function format of the customer table from PARTITION BY VALUE(date_format(login_time, '%Y%m%d')) LIFECYCLE 30 to PARTITION BY VALUE(date_format(login_time, '%Y%m')) LIFECYCLE 30.

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

Change the partition lifecycle of a table

Syntax

ALTER TABLE db_name.table_name PARTITIONS N

Usage notes

The new partition lifecycle takes effect only after the BUILD job is complete. For information about automatic BUILD jobs, manual BUILD jobs, and the status of BUILD jobs, see BUILD. You can execute the SHOW CREATE TABLE db_name.table_name; statement to query whether the new partition lifecycle takes effect.

Example

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

ALTER TABLE customer PARTITIONS 40;