You can execute the ALTER TABLE statement to modify the schema of a table. For example, you can add a column, create an index, or change a data type.

Note

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

Modify a standard table

Note PolarDB-X 1.0 If you execute the ALTER TABLE statement to modify the schema of a standard table, the syntax of this statement in DRDS is the same as that in open source MySQL. For more information, see ALTER TABLE statement.

Syntax

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

Examples

  • Add a column

    Add the idcard column to the user_log table. You can use the following sample code:

    ALTER TABLE user_log ADD COLUMN idcard varchar(30);
  • Create a local index

    Create an index named idcard_idx on the idcard column in the user_log table. You can use the following sample code: 

    ALTER TABLE user_log ADD INDEX idcard_idx (idcard);
  • Rename a local index

    Rename the idcard_idx index in the user_log table as idcard_idx_new. You can use the following sample code: 

    ALTER TABLE user_log RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
  • Delete a local index

    Delete the idcard_idx index from the user_log table. You can use the following sample code: 

    ALTER TABLE user_log DROP INDEX idcard_idx;
  • Modify a column

    Change the length of the idcard column in the user_log table from 30 characters to 40 characters. The values for the idcard column are of the VARCHAR type. You can use the following sample code: 

    ALTER TABLE user_log MODIFY COLUMN idcard varchar(40);

Modify a table that contains a GSI

Modify a column

When you execute the ALTER TABLE statement to modify a column in a table that contains a GSI, the syntax of this statement is the same as that you use to modify a column in a standard table. We recommend that you are familiar with the limits. For more information about the limits, see Notes for executing the ALTER TABLE statement.

Modify an index

Syntax

ALTER TABLE tbl_name
    alter_specification # If you execute the ALTER TABLE statement to modify a GSI, use the alter_specification option once.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # Explicitly specify the name of a GSI.
      [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 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 # 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 following sample code uses the DDL syntax that is 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 you create a table, you can use ALTER TABLE ADD GLOBAL INDEX to create a GSI. Compared with the syntax for MySQL, the syntax for DRDS introduces the GLOBAL keyword to specify that you create a GSI.

You can also use ALTER TABLE { DROP | RENAME } INDEX to modify a GSI. If you create a GSI after a table is created, we recommend that you are familiar with the limits. For more information about the limits, see Notes for using GSIs.

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

Examples

The following examples show how to create a unique GSI after a table is 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 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 data on the primary table t_order is partitioned into database shards but not further partitioned into table shards. The database uses hash sharding based on the order_id column.
    • Index table: The data on the index table g_i_buyer is partitioned into database shards but not further partitioned into table shards. The database uses hash sharding based on the buyer_id column. order_snapshot is the covering column that you specify.
    • Clause used to define the GSI: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`).
  • Execute the SHOW INDEX statement to query index information. For example, you can query local indexes on the shard key order_id and the GSIs on the columns buyer_id, id, order_id, and order_snapshot. For the index table, the buyer_id column is the shard key, the id column is the primary key, and the order_id column is the shard key. The id and order_id columns are the default covering columns. order_snapshot is the covering column that you explicitly specify. 
    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 |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
  • Execute the SHOW GLOBAL INDEX statement to query GSI information. 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 schema of the index table. 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. The AUTO_INCREMENT attribute is removed from the primary key. The local index is removed from the primary table. By default, GSIs are created on all the shard keys of the index table and each GSI is globally unique. 
    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 a GSI named g_i_seller. In this case, the index table named g_i_seller is also deleted. 

    ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
  • Rename a GSI.

    By default, you cannot rename a GSI.