PolarDB:ALTER TABLE (AUTO mode)

Examples

Add a column

ALTER TABLE user_log
    ADD COLUMN idcard VARCHAR(30);

Create an index

ALTER TABLE user_log
    ADD INDEX idcard_idx (idcard);

Drop an index

ALTER TABLE user_log
    DROP INDEX idcard_idx;

Rename an index

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

Modify a column

-- Extend the idcard column from 30 to 40 characters.
ALTER TABLE user_log
    MODIFY COLUMN idcard VARCHAR(40);

Add or drop a GSI

Syntax

ALTER TABLE tbl_name
    alter_specification   -- Use alter_specification only once per statement when modifying a GSI.

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

-- Applies only to global secondary indexes. See CREATE TABLE (DRDS mode) for full definitions.
global_secondary_index_option:
    [COVERING (col_name,...)]   -- Covering index
    partition_options           -- Must include columns from index_sharding_col_name.

-- Specify a partitioning 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

-- Supported partition functions.
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

-- Hash and KEY partitioning: specify the number of partitions.
hash_partition_list:
    PARTITIONS partition_count

-- Range partitioning: specify value boundaries.
range_partition_list:
    range_partition [, range_partition ...]

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

-- List partitioning: specify value sets.
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' ]

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

ADD GLOBAL INDEX introduces the GLOBAL keyword, which identifies the index as a global secondary index. Use ALTER TABLE DROP INDEX and ALTER TABLE RENAME INDEX to manage existing GSIs, subject to the limits described in How to use global secondary indexes.

For the full syntax of clauses used to define global secondary indexes, see CREATE TABLE (DRDS mode).

Example: add a GSI after table creation

The following example creates a table and then adds a GSI on the buyer_id column.

-- Create the base table, partitioned 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
PARTITION BY KEY(`order_id`)
PARTITIONS 4;

-- Add a GSI on buyer_id, with order_snapshot as a covering column.
ALTER TABLE t_order
    ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`)
    COVERING (`order_snapshot`)
    PARTITION BY KEY(`buyer_id`) PARTITIONS 4;

After this statement:

• Base table (t_order): sharded by order_id using the hash algorithm.

• Index table (g_i_buyer): sharded by buyer_id using the hash algorithm. The order_snapshot column is included as a covering column.

Run SHOW INDEX to confirm the indexes on a base table shard:

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)

The index information includes the local secondary index on the order_id shard key and the global secondary indexes on the buyer_id, id, order_id, and order_snapshot columns. For more information, see SHOW INDEX.

Run SHOW GLOBAL INDEX to query all GSIs:

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)

The COVERING_NAMES column shows all covering columns: id and order_id are included automatically (primary key and base table shard key), and order_snapshot is the covering column you specified explicitly.

To inspect the index table schema:

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)

The index table contains the primary key of the base table, the shard key, and all covering columns. AUTO_INCREMENT is removed from the primary key column in the index table, and local indexes on the base table are also removed. By default, a globally unique index is created on all shard keys of the index table. The index is used as a globally unique constraint of the base table.

For limits and precautions on using GSIs, see How to use global secondary indexes. For more information about SHOW GLOBAL INDEX, see SHOW GLOBAL INDEX.

Example: drop a GSI

Dropping a GSI also drops its index table.

ALTER TABLE `t_order` DROP INDEX `g_i_seller`;

Rename a GSI

By default, global secondary indexes cannot be renamed. For limits and conventions, see How to use global secondary indexes.