You can execute the ALTER TABLE
statement to add or remove an attribute column in an existing mapping table.
For information about how to create a mapping table, see Create a mapping table for a table.
Usage notes
You can execute the
ALTER TABLE
statement to update an attribute column in only a mapping table that is created by executing theCREATE TABLE
statement. You cannot execute theALTER TABLE
statement to update an attribute column in a mapping table that is automatically created for a table by executing theDESCRIBE
statement.You can add or remove only one attribute column in a mapping table by executing the
ALTER TABLE
statement. If you want to add or remove multiple attribute columns in a mapping table, you can execute theALTER TABLE
statement multiple times.You can execute the
ALTER TABLE
statement to update only the schema of a mapping table. The schema of the Tablestore table for which the mapping table is created is not updated.You cannot execute the
ALTER TABLE
statement to add or remove the primary key columns in a mapping table.After you execute the
ALTER TABLE
statement, the SQL engine asynchronously refreshes the mapping table. Up to 30 seconds are required to complete the refresh. During the refresh period, the column that you added may not be returned when you perform the operations that are supposed to return all columns.
Syntax
ALTER TABLE table_name option column_name [data_type];
Parameters
Parameter | Required | Description |
table_name | Yes | The name of the mapping table, which is used to identify the mapping table. |
option | Yes | The update operation that you want to perform. Valid values:
|
column_name | Yes | The name of the attribute column. The name of the attribute column that you want to add must be different from the names of existing attribute columns in the mapping table. The column name in SQL must be equivalent to the column name in the Tablestore table. For example, if the column name in the Tablestore table is Aa, the column name in SQL must be Aa, AA, aA, or aa. |
data_type | No | The data type of the attribute column, such as BIGINT, DOUBLE, or BOOL. This parameter is required only if you set option to ADD COLUMN. The data type of the column in SQL must match the data type of the column in the Tablestore table. For information about data type mappings, see Data type mappings in SQL. |
Examples
Add attribute columns to a mapping table
You can execute the following statements to add the colvalue attribute column and the content attribute column to the exampletable mapping table. The data type of the colvalue attribute column is BIGINT and the data type of the content attribute column is MEDIUMTEXT:
ALTER TABLE exampletable ADD COLUMN colvalue BIGINT;
ALTER TABLE exampletable ADD COLUMN content MEDIUMTEXT;
Remove an attribute column from a mapping table
You can execute the following statement to remove the colvalue column of the BIGINT type from the exampletable mapping table:
ALTER TABLE exampletable DROP COLUMN colvalue;