You can execute the ALTER TABLE statement to modify the schema of a table. For example, you can add columns, add indexes, or change data types.

Usage notes

You cannot execute the ALTER TABLE statement to change shard keys.

Syntax

Note Modify the schema of a table. For example, you can add columns, add indexes, or change data types. For more information, see MySQL ALTER TABLE.
ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
  [partition_options]

Examples

  • Add a column

    The following example shows how to add 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 shows how to create 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

    The following example shows how to rename the idcard_idx index as 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 shows how to delete the idcard_idx index from the user_log table:

    ALTER TABLE user_log
        DROP INDEX idcard_idx;
  • Modify a column

    The following example shows how to change the length of the idcard column in the user_log table from 30 characters to 40 characters. The data type of the idcard column is VARCHAR.

    ALTER TABLE user_log
        MODIFY COLUMN idcard varchar(40);

Global secondary indexes

PolarDB-X supports global secondary indexes. For more information, see GSI.

Modify a column

You can modify columns in tables that use global secondary indexes in the same manner in which you modify regular tables.
Note However, the system has limits on modifying columns in tables that use global secondary indexes. For more information about the limits and conventions of global secondary indexes, see How to use global secondary indexes.

Modify an index

Syntax

ALTER TABLE tbl_name
    alter_specification # Use the alter_specification option only once in each ALTER TABLE statement when you modify a global secondary index.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # Explicitly specify the name of a global secondary index.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # Explicitly specify the name of a global secondary index.
      [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 an 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 MySQL DDL syntax is used in the following example:
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 you create a table, you can execute ALTER TABLE ADD GLOBAL INDEX to create a global secondary index. Compared with the MySQL syntax, the ALTER TABLE ADD GLOBAL INDEX syntax introduces the GLOBAL keyword. This keyword specifies that the index to be added is a global secondary index.

You can also execute ALTER TABLE { DROP | RENAME } INDEX to modify a global secondary index. However, the syntax also has limits on adding a global secondary index after you create a table. For more information about the limits and conventions of global secondary indexes, see How to use global secondary indexes.

For more information about the clauses that are used to define global secondary indexes, see CREATE TABLE.

Examples

  • Add a global secondary index after a table is created

    The following examples show how to create a table and then add a global secondary index.

    # 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`);
    # Add 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: Only database sharding is performed on the t_order base table. The order_id column is specified as the hashed shard key. Table sharding is not performed.
    • Index table: Only database sharding is performed on the g_i_buyer index table. The buyer_id column is specified as the hashed shard key and order_snapshot is specified as the covering column. Table sharding is not performed.
    • Clause used to define the global secondary index: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`).
    Execute the SHOW INDEX statement to query index information. The index information includes the local index on the order_id shard key and the global secondary indexes on the buyer_id, id, order_id, and order_snapshot columns. The buyer_id column is the shard key of the index table. The id column is the primary key of the base table. The order_id column is the shard key of the base table. The id and order_id columns are the default covering columns. order_snapshot is the covering column that you explicitly specify.
    Note For more information about the limits and conventions of global secondary indexes, see How to use global secondary indexes. For more information about SHOW INDEX, see 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 |               |
    +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
    You can execute the SHOW GLOBAL INDEX statement to query only global secondary indexes. 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 |
    +---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
    The following sample code can be used to view the schema of the index table. The index table contains the primary key of the base table, the database shard key and table shard key, the default covering columns, and the custom covering columns. The AUTO_INCREMENT attribute is removed from the primary key column. 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.
    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 global secondary index

    Delete the global secondary index named g_i_seller. The corresponding index table is also deleted.

    # Delete an index.
    ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
  • Rename an index

    By default, you cannot rename a global secondary index. For more information about the limits and conventions of global secondary indexes, see Use global secondary indexes.