ALTER TABLE

Modify a column

The syntax for modifying a column on a GSI table is the same as for a standard table. Before running the statement, review the ALTER TABLE usage notes to understand which column changes are restricted when a GSI is present.

Modify an index

Syntax

ALTER TABLE tbl_name
    alter_specification

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name   -- GSI name is required
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name            -- GSI name is required
      [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 contained in index_sharding_col_name.

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

-- DDL syntax supported by the MySQL engine
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}

After creating a table, use ALTER TABLE ADD GLOBAL INDEX to add a GSI. The GLOBAL keyword distinguishes this from MySQL's standard ADD INDEX syntax.

Use ALTER TABLE DROP INDEX or ALTER TABLE RENAME INDEX to drop or rename a GSI. Before adding a GSI to an existing table, review the Notes for using GSIs for known restrictions.

For the full syntax of GSI definition clauses, see CREATE TABLE.

Examples

The following examples walk through the complete lifecycle of a unique GSI on the t_order table.

1. Create the primary 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`);

   The table is partitioned into database shards using hash sharding on order_id, with no table shards.

2. Add a unique 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`)
      dbpartition by hash(`buyer_id`);

   The index table g_i_buyer is partitioned by hash on buyer_id. order_snapshot is the explicitly specified covering column. The primary key (id) and the primary table's shard key (order_id) are included as default covering columns.

3. Verify the indexes with SHOW INDEX:

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

4. Inspect GSI details with SHOW GLOBAL INDEX:

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

   For more details on this statement, see SHOW GLOBAL INDEX.

5. Inspect the index table schema:

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

   The index table contains the primary key of the primary table, the database shard key and table shard key, the default covering columns, and the custom covering columns. Note the following differences from the primary table:

   • AUTO_INCREMENT is removed from the primary key.

   • The local index from the primary table is not carried over.

   • By default, a GSI is created on all shard keys of the index table, and each GSI is globally unique.

6. Drop a GSI named g_i_seller (this also drops the corresponding index table):

    ALTER TABLE `t_order` DROP INDEX `g_i_seller`;