Use ALTER TABLE to modify columns and Global Secondary Indexes in DRDS mode - PolarDB

ALTER TABLE modifies the schema of an existing table in a PolarDB-X database running in DRDS mode. You can add or drop columns, create or remove indexes, and change column data types.

Supported operations

ALTER TABLE in DRDS mode supports the following operations: add columns, add indexes, and change the data type or other attributes of columns.

When you use ALTER TABLE to modify a GSI, the alter_specification option can only appear once in the statement.

Limitations

You cannot change the shard key of a table using `ALTER TABLE`.

For GSI-specific constraints, see How to use global secondary indexes.

Syntax

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

For the full MySQL-compatible syntax, see the MySQL ALTER TABLE statement.

GSI syntax

When modifying a global secondary index (GSI), use the following syntax. Include alter_specification only once per statement.

ALTER TABLE tbl_name
    alter_specification

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name    -- 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           -- 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 columns
    drds_partition_options               -- Specify one or more columns that are listed in index_sharding_col_name.

-- Sharding options
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 ADD GLOBAL INDEX syntax extends MySQL DDL with the GLOBAL keyword to create a GSI. ALTER TABLE { DROP | RENAME } INDEX drops or renames a GSI by its index name.

For the clauses used to create GSIs, see CREATE TABLE (DRDS mode).

Examples

Basic column and index operations

The following examples use a user_log table to demonstrate common schema changes.

Add a column

ALTER TABLE user_log
    ADD COLUMN idcard varchar(30);

Modify a column

Change the idcard column from varchar(30) to varchar(40):

ALTER TABLE user_log
    MODIFY COLUMN idcard varchar(40);

Create a local index

ALTER TABLE user_log
    ADD INDEX idcard_idx (idcard);

Rename a local index

ALTER TABLE user_log
    RENAME INDEX `idcard_idx` TO `idcard_idx_new`;

Drop a local index

ALTER TABLE user_log
    DROP INDEX idcard_idx;

GSI operations

Create a GSI on an existing table

Start with a t_order table sharded by order_id:

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`);

Add a unique GSI named g_i_buyer to support queries by buyer_id:

ALTER TABLE t_order
    ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`)
    COVERING (`order_snapshot`)
    dbpartition by hash(`buyer_id`);

This creates an index table g_i_buyer with the following structure:

Shard key: buyer_id, distributed by hash across database shards
Default covering columns: id (primary key of the base table) and order_id (shard key of the base table)
Explicit covering column: order_snapshot

To verify, check indexes on t_order:

SHOW INDEX FROM t_order;

The output lists both the local index on order_id and the GSI on buyer_id, id, order_id, and order_snapshot:

+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| 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 |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

For GSI-specific partition details such as shard key, partition policy, and partition count, use SHOW GLOBAL INDEX:

SHOW GLOBAL INDEX FROM t_order;

+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| 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 |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

To inspect the index table schema directly:

SHOW CREATE TABLE g_i_buyer;

+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`)               |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Note that the primary key in the index table does not have the AUTO_INCREMENT attribute. Local indexes from the base table are removed, and a globally unique index is created on all shard keys to ensure a globally unique constraint.

For GSI limits and conventions, see How to use global secondary indexes. For SHOW INDEX output details, see SHOW INDEX. For SHOW GLOBAL INDEX output details, see SHOW GLOBAL INDEX.

Drop a GSI

Drop the GSI named g_i_seller. The corresponding index table is also dropped.

ALTER TABLE `t_order` DROP INDEX `g_i_seller`;

Rename a GSI

By default, GSIs cannot be renamed. For details, see How to use global secondary indexes.

PolarDB:ALTER TABLE (DRDS mode)