How to change a table schema using ALTER TABLE (AUTO mode)-PolarDB(PolarDB)-阿里云帮助中心

Use the ALTER TABLE statement to modify a table schema, for example, to add columns, create indexes, or modify column definitions. This statement applies only to databases in AUTO mode.

Usage notes

If your instance version is earlier than 5.4.17-16835173, you cannot use the ALTER TABLE statement to modify a shard key.

Syntax

Note

For detailed syntax, see MySQL ALTER TABLE.

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

Example

Add a column

Add a column named idcard to the user_log table:
```
ALTER TABLE user_log
    ADD COLUMN idcard varchar(30);
```
Create an index

Create an index named idcard_idx on the idcard column of the user_log table:
```
ALTER TABLE user_log
    ADD INDEX idcard_idx (idcard);
```
Drop an index

Drop the idcard_idx index from the user_log table:
```
ALTER TABLE user_log
    DROP INDEX idcard_idx;
```
Rename an index

Rename the idcard_idx index to idcard_idx_new on the user_log table:
```
ALTER TABLE user_log
    RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```
Modify a column

Change the length of the idcard column (a varchar column) from 30 to 40 on the user_log table:
```
ALTER TABLE user_log
    MODIFY COLUMN idcard varchar(40);
```

Global secondary indexes

PolarDB-X supports the global secondary index (GSI). To learn about the underlying principles, see Global Secondary Indexes.

Column changes

For a table with a GSI, the column modification syntax is the same as for a regular table.

Note

When you modify a column in a table with a GSI, additional limitations apply. For more information about the limitations and conventions for GSIs, see Create and use global secondary indexes.

Index changes

Syntax

ALTER TABLE tbl_name
    alter_specification # Only one alter_specification is supported for changes related to global secondary indexes.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # You must explicitly specify the index name for a global secondary index.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # You must explicitly specify the index name for a global secondary index.
      [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

# For more information about the syntax specific to global secondary indexes, see the CREATE TABLE documentation.
global_secondary_index_option:
    [COVERING (col_name,...)] # Covering Index
    partition_options # Includes only the columns specified in index_sharding_col_name.

# Specify the sharding method for the index table.
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)})
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)} }
    partition_list_spec

# Partition function definitions
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# Partition list definitions
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

# Column definitions for Hash/Key partitioned tables
hash_partition_list:
    PARTITIONS partition_count

# Column definitions for Range/Range Columns partitioned tables
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]

# Column definitions for List/List Columns partitioned tables
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

partition_spec_options:
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]

# The following is 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 family of statements adds a GSI to an existing table. These statements use the GLOBAL keyword, an extension to standard MySQL syntax, to specify that the index is a GSI.

You can also use the ALTER TABLE { DROP | RENAME } INDEX statement to modify a GSI. Currently, some limitations apply when you add a GSI to an existing table. For more information about the limitations and conventions for GSIs, see Create and use global secondary indexes.

For more information about the GSI definition clauses, see CREATE TABLE (DRDS mode).

Example

Add a global secondary index after table creation

This example creates a globally unique index, which is a type of 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
partition by key(`order_id`)
partitions 4;

# Create a global secondary index.
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) partition by key(`buyer_id`) partitions 4;

Base table: The t_order table is sharded by key on the order_id column.
Index table: The g_i_buyer index table is sharded by key on the buyer_id column. The order_snapshot column is specified as a covering column.
Index definition clause: UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING (order_snapshot) partition by key(`buyer_id`) partitions 4.

Run SHOW INDEX to view the indexes on the base table. The output shows the PRIMARY key and the local secondary index l_i_order, but not the GSI. The GSI itself is composed of the following columns: buyer_id is the shard key of the index table; id (the base table's primary key) and order_id (the base table's shard key) are included as default covering columns; and order_snapshot is an explicitly specified covering column.

Note

For more information about the limitations and conventions for GSIs, see Create and use global secondary indexes. For more information about SHOW INDEX, see SHOW INDEX.

show index from t_order;
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_order_syes_00000 |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order_syes_00000 |          1 | l_i_order |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.05 sec)

You can run SHOW GLOBAL INDEX to view only the GSI information. For more information, see SHOW GLOBAL INDEX.

show global index;
+--------+---------+------------+-----------------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| 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 |
+--------+--------+------------+-----------------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| d1     | t_order | 0          | g_i_buyer_$c1a0 | buyer_id    | id, order_id, order_snapshot | NULL       |                  |                     | NULL               |                  |                     | NULL               | PUBLIC |
+--------+---------+------------+-----------------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
1 row in set (0.04 sec)

View the structure of the index table. The index table contains the primary key and shard key of the base table, default covering columns, and custom covering columns. The AUTO_INCREMENT attribute is removed from the primary key column, and local secondary indexes from the base table are not replicated on the index table. By default, to enforce a globally unique constraint, a unique index is automatically created on the index table's shard key.

show create table g_i_buyer;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE           | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                  |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_buyer_$c1a0 | CREATE TABLE `g_i_buyer_$c1a0` (
    `id` bigint(11) NOT NULL,
    `order_id` varchar(20) DEFAULT NULL,
    `buyer_id` varchar(20) DEFAULT NULL,
    `order_snapshot` longtext,
    UNIQUE KEY `auto_shard_key_buyer_id` USING BTREE (`buyer_id`),
    KEY `_gsi_pk_idx_` USING BTREE (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
PARTITION BY KEY(`buyer_id`)
PARTITIONS 4 |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Drop a global secondary index

When you drop a GSI, such as g_i_seller, its corresponding index table is also dropped.
```
# Drop the index.
ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
```
Rename an index

By default, you cannot rename a GSI. For more information about the limitations and conventions for GSIs, see Use global secondary indexes.