PolarDB:ALTER TABLE

Usage notes

• You cannot use the ALTER TABLE statement to modify a shard key.
• To use the ALTER statement on a table that contains a global secondary index (GSI), you must use MySQL 5.7 or later and PolarDB-X 1.0 5.4.1 or later.

Modify a standard table

Syntax

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

Example

• Add a column

  The following example adds a column named idcard to the user_log table:

  ALTER TABLE user_log ADD COLUMN idcard varchar(30);

• Create a local index

  The following example creates a local 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

  The following example renames the idcard_idx index to idcard_idx_new in the user_log table:

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

• Delete a local index

  The following example deletes the idcard_idx index from the user_log table:

  ALTER TABLE user_log DROP INDEX idcard_idx;

• Modify a column

  The following example changes the length of the idcard column, which has a varchar data type, from 30 to 40 in the user_log table:

  ALTER TABLE user_log MODIFY COLUMN idcard varchar(40);

Modify a table with a GSI

Column modifications

When you modify a column in a table that contains a GSI, the syntax is the same as for a standard table, but some limitations apply. For more information, see Considerations for using global secondary indexes.

Index modifications

Syntax

ALTER TABLE tbl_name
    alter_specification # Only one alter_specification is supported for GSI modifications.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # You must explicitly specify the index name for a GSI.
      [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 GSI.
      [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 the 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) 

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

Use the ALTER TABLE ADD GLOBAL INDEX statement to add a GSI to an existing table. This statement adds the GLOBAL keyword to the standard MySQL syntax to specify that the new index is a GSI.

For more information about the clauses used to define a global secondary index, see CREATE TABLE.

Example

The following example shows how to create a unique global secondary index after a table has been created.

• Create a GSI

  # 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 (GSI).
  ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

  • Primary table: The t_order table uses database sharding but not table sharding. Data is sharded across databases based on a hash of the order_id column.
  • Index table: The g_i_buyer table uses database sharding but not table sharding. Data is sharded across databases based on a hash of the buyer_id column. The order_snapshot column is specified as a covering column.
  • Index definition clause: GLOBAL INDEX `g_i_buyer` ON t_order (`buyer_id`) dbpartition by hash(`buyer_id`).
• Run the SHOW INDEX statement to view index information. The result includes the local index on the order_id shard key and the GSI, which covers the buyer_id, id, order_id, and order_snapshot columns. In this GSI, buyer_id is the shard key of the index table. The id (primary key of the primary table) and order_id (shard key of the primary table) are included as default covering columns, and order_snapshot is the explicitly specified covering column.

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

• You can run the SHOW GLOBAL INDEX statement to view information specific to the GSI. For more information, see 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 |
  +---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

• View the structure of the index table. The index table contains the primary key, shard key columns, default covering columns, and explicitly specified covering columns from the primary table. The AUTO_INCREMENT attribute is removed from the primary key column, and local indexes from the primary table are excluded. For a unique GSI, a unique index is created by default on all its shard key columns to enforce the global uniqueness constraint.

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

• Delete a GSI

  Delete the GSI named g_i_buyer. The corresponding index table is also deleted.

  ALTER TABLE `t_order` DROP INDEX `g_i_buyer`;

• Rename an index

  By default, renaming a GSI is prohibited.