ALTER TABLE (DRDS mode) - PolarDB - Alibaba Cloud Documentation Center

You can use the ALTER TABLE statement to modify the schema of a table. For example, you can add columns, add indexes, or change the data type or other attributes of columns. The syntax described in this topic applies only to databases in Distributed Relational Database Service (DRDS) mode.

Usage notes

You cannot use the ALTER TABLE statement to change the shard keys of a table.

Syntax

Note

You can use the ALTER TABLE statement to add a column, create an index, or change the data type or other attributes of a column. For more information about the syntax, see MySQL ALTER TABLE Statement.

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

Examples

Add a column
Execute the following statement to add a column named idcard to the user_log table:
```
ALTER TABLE user_log
    ADD COLUMN idcard varchar(30);
```
Create a local index
Execute the following statement to create an index named idcard_idx on the idcard column in the user_log table:
```
ALTER TABLE user_log
    ADD INDEX idcard_idx (idcard);
```
Rename a local index
Execute the following statement to rename the idcard_idx index as idcard_idx_new in the user_log table:
```
ALTER TABLE user_log
    RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```
Delete a local index
Execute the following statement to delete the idcard_idx index from the user_log table:
```
ALTER TABLE user_log
    DROP INDEX idcard_idx;
```
Modify a column
Execute the following statement to change the maximum length of the idcard column that uses the VARCHAR data type in the user_log table from 30 characters to 40 characters.
```
ALTER TABLE user_log
    MODIFY COLUMN idcard varchar(40);
```

GSIs

PolarDB-X supports global secondary indexes (GSIs). For more information about GSIs, see GSI.

Modify a column

For tables that have GSIs, the syntax for modifying a column is the same as that for tables that do not have GSIs.

Note

When you modify columns in tables that have GSIs, take note of the limits. For information about the limits and conventions of GSIs, see How to use global secondary indexes.

Modify an index

Syntax

ALTER TABLE tbl_name
    alter_specification # When you use the ALTER TABLE statement to modify a global secondary index, you can use the alter_specification option only once.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # To add a GSI, you must explicitly specify the name of the GSI.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # To add a GSI, you must explicitly specify the name of the GSI.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | DROP {INDEX|KEY} index_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name

global_secondary_index_option:
    [COVERING (col_name,...)] # Covering Index
    drds_partition_options # Specify one or more columns that are listed in index_sharding_col_name.

# Specify a sharding method.
drds_partition_options:
    DBPARTITION BY db_sharding_algorithm
    [TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]
db_sharding_algorithm:
    HASH([col_name])
  | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n)
table_sharding_algorithm: 
    HASH(col_name) 
  | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  | UNI_HASH(col_name)
  | RIGHT_SHIFT(col_name, n)
  | RANGE_HASH(col_name, col_name, n) 

# MySQL DDL syntax
index_sharding_col_name:
    col_name [(length)] [ASC | DESC]
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
index_type:
    USING {BTREE | HASH}

The ALTER TABLE ADD GLOBAL INDEX syntax is used to add a GSI to an existing table. The syntax introduces the GLOBAL keyword to the MySQL syntax to specify that the added index is a GSI.

You can use the ALTER TABLE { DROP | RENAME } INDEX syntax to remove or rename a GSI. When you add a GSI to an existing table, take note of the limits. For information about the limits and conventions of GSIs, see How to use global secondary indexes.

For information about the clauses that can be used to create GSIs, see CREATE TABLE (DRDS mode).

Examples

Create a GSI on an existing table

The following examples show how to create a GSI on an existing table.

# Create a table.
CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
# Create a GSI.
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

t_order is the base table. The base table is distributed to multiple database shards based on the hash value of the order_id column.
g_i_buyer is the index table. The index table is distributed to multiple database shards based on the hash value of the buyer_id column. The order_snapshot column is specified as a covering column.
The GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`) clause defines the GSI.

You can use the SHOW INDEX statement to query indexes on a table. In this example, the indexes on the t_order table include the local index on the shard key column order_id and the GSI on the buyer_id, id, order_id, and order_snapshot columns. buyer_id is the shard key of the index table, id (primary key) and order_id (shard key of the index table) are the default covering columns, and order_snapshot is an explicitly specified covering column.

Note

For information about the limits and conventions of GSIs, see How to use global secondary indexes. For information about the SHOW INDEX statement, see SHOW INDEX.

show index from t_order;

The following information is returned:

+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE   | NON_UNIQUE | KEY_NAME  | SEQ_IN_INDEX | COLUMN_NAME    | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order |          0 | PRIMARY   |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order |          1 | l_i_order |            1 | order_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order |          0 | g_i_buyer |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_buyer |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            4 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

You can execute the SHOW GLOBAL INDEX statement to query information about GSIs. For more information, see SHOW GLOBAL INDEX.

show global index from t_order;

The following information is returned:

+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA              | TABLE   | NON_UNIQUE | KEY_NAME  | INDEX_NAMES | COVERING_NAMES               | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| ZZY3_DRDS_LOCAL_APP | t_order | 0          | g_i_buyer | buyer_id    | id, order_id, order_snapshot | NULL       | buyer_id         | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

You can use the following statement to query the schema of an index table. In the following example, the g_i_buyer index table contains the primary key of the base table, the database shard key and table shard key, the default covering columns, and the custom covering columns. The primary key in the index table does not have the AUTO_INCREMENT attribute and local indexes on the base table are removed. A globally unique index is created on all shard keys to ensure a globally unique constraint.

show create table g_i_buyer;

The following information is returned:

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_buyer | CREATE TABLE `g_i_buyer` (`id` bigint(11) NOT NULL, `order_id` varchar(20) DEFAULT NULL, `buyer_id` varchar(20) DEFAULT NULL, `order_snapshot` longtext, PRIMARY KEY (`id`), UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`)               |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Delete a GSI
Delete the GSI named g_i_seller. The corresponding index table is also deleted.
```
# Delete the GSI named g_i_seller.
ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
```
Rename a GSI
By default, a GSI cannot be renamed. For information about the limits and conventions of GSIs, see How to use global secondary indexes.