Use the ALTER TABLE statement to change the structure of a table - PolarDB

Use the ALTER TABLE statement to change a table's structure, such as adding columns, indexes, or modifying data definitions. This statement applies only to databases in DRDS mode.

Usage notes

You cannot use the ALTER TABLE statement to modify a shard key.

Syntax

Note

For the detailed syntax, see the MySQL ALTER TABLE statement.

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

Example

Add a column

Add an idcard column to the user_log table:

ALTER TABLE user_log
    ADD COLUMN idcard varchar(30);

Add a local index

Add 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

Rename the idcard_idx index in the user_log table to idcard_idx_new:
```
ALTER TABLE user_log
    RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```
Drop a local index

Drop the idcard_idx index from the user_log table:
```
ALTER TABLE user_log
    DROP INDEX idcard_idx;
```
Modify a column

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

Global secondary indexes

PolarDB-X supports global secondary indexes (GSIs). For more information about the basic principles, see Global secondary indexes.

Column modifications

For tables that use GSIs, the syntax for modifying columns is the same as for regular tables.

Note

When you modify a table that contains a global secondary index, column modifications have additional restrictions. For more information about the limits and conventions of GSIs, see Create and use global secondary indexes.

Index modifications

Syntax

ALTER TABLE tbl_name
    alter_specification # For GSI-related changes, only one alter_specification is supported.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # You must explicitly specify the GSI name.
      [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 GSI name.
      [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 # Must contain only columns specified in index_sharding_col_name.

# Specify the sharding method for the index table.
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 statement adds a GSI to an existing table. It extends MySQL syntax by introducing the GLOBAL keyword to specify that the index being added is a GSI.

The ALTER TABLE { DROP | RENAME } INDEX syntax can also modify a GSI. These operations have specific limitations. For more information about the limits and conventions of GSIs, see Create and use global secondary indexes.

For detailed descriptions of GSI definition clauses, see CREATE TABLE (DRDS mode).

Examples

Create a global secondary index

This example creates a unique 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 global secondary index
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

base table: t_order is sharded by database only (not by table). The database sharding method is hash on the order_id column.
index table: g_i_buyer is sharded by database only (not by table). The database sharding method is hash on the buyer_id column, and the covering column is order_snapshot.
Index definition clause: GLOBAL INDEX `g_i_buyer` ON t_order (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`).

Use SHOW INDEX to view index information. The results include the local index on the shard key order_id and the GSI, which is indexed on buyer_id and includes several covering columns. In this example, buyer_id is the shard key of the index table. id and order_id are default covering columns (the primary key and shard key of the base table). order_snapshot is the explicitly specified covering column.

Note

For details about GSI limits and conventions, see Create and use global secondary indexes. For details about SHOW INDEX, see SHOW INDEX.

show index from t_order;

The following output 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 use SHOW GLOBAL INDEX to view only the GSI information. For more information, see SHOW GLOBAL INDEX.

show global index from t_order;

The following output 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 |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

View the structure of the index table. The index table includes the primary key and shard key of the base table, along with both default and custom covering columns. The AUTO_INCREMENT attribute is removed from the primary key column, and local indexes from the base table are excluded. A unique index is created by default on the shard key of the index table to enforce a global unique constraint.

show create table g_i_buyer;

The following output 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`)               |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Drop a global secondary index

Drop the GSI named g_i_seller. The corresponding index table is also dropped.
```
# Drop the index
ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
```
Rename an index

By default, renaming a GSI is restricted. For details about GSI limits and conventions, see Create and use global secondary indexes.