PolarDB:ALTER TABLE ( DRDS mode )

Note

You cannot use the ALTER TABLE statement to change the shard key of a table.

Syntax

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

Examples

• Add a column

  Use the following statement to add a column that is named idcard to the user_log table:

  ALTER TABLE user_log
      ADD COLUMN idcard varchar(30);

• Add a local index

  Use the following statement to add an index that is named idcard_idx to the idcard column in the user_log table:

  ALTER TABLE user_log
      ADD INDEX idcard_idx (idcard);

• Rename a local index

  Use the following statement to change the name of the idcard_idx index in the user_log table to idcard_idx_new:

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

• Delete a local index

  Use the following statement to delete the idcard_idx index from the user_log table:

  ALTER TABLE user_log
      DROP INDEX idcard_idx;

• Modify a configuration of a column

  In this example, the idcard field in the user_log table is of the VARCHAR data type. You can use the following statement to change the maximum length of data in the idcard column from 30 bytes to 40 bytes:

  ALTER TABLE user_log
      MODIFY COLUMN idcard varchar(40);

Manage global secondary indexes

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

Manage columns

Modify an index

Syntax

ALTER TABLE tbl_name
    alter_specification # When you use the ALTER TABLE statement to modify a global secondary index, you can use only one alter_specification option.

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # Specify the name of the global secondary index.
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # Specify the name of the 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 listed in index_sharding_col_name.

# Specify a sharding method.
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) 

# Use the DDL syntax that is supported by MySQL databases.
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 the ALTER TABLE ADD GLOBAL INDEX syntaxes to create global secondary indexes. The syntax that is defined by PolarDB-X supports the GLOBAL keyword. You can use the GLOBAL keyword to specify an index as a global secondary index.

You can also use ALTER TABLE { DROP | RENAME } INDEX to modify a global secondary index. If you want to create a global secondary index for an existing table, you must take note of the limits on global secondary indexes. For more information about the limits and conventions on global secondary indexes, see How to use global secondary indexes.

For more information about the clauses that can be used to create global secondary indexes, see CREATE TABLE ( DRDS mode ).

Examples

• Create a global secondary index for an existing table

  The following examples show how to create a unique global secondary index for an existing table.

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

  • Base table: In the preceding example, a table named t_order is created in each database shard. The data is sharded by using the hash algorithm based on the order_id column.

  • Index table: In the preceding example, an index table named g_i_buyer is created for each database shard. The index values are sharded by using the hash algorithm based on the buyer_id column. The order_snapshot column is specified as a custom covering column.

  • Clause that defines the global secondary index: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`).

  You can use the SHOW INDEX statement to query indexes of a table. In the following example, the returned information includes local indexes that are created based on the order_id shard key and the global secondary indexes that are created based on the buyer_id, id, order_id, and order_snapshot columns. The shard key of the index table is buyer_id. The id field is the primary key, and the order_id field is the shard key of the base table. The id and order_id columns are the default covering columns. The order_snapshot column is a custom covering column.

  Note

  For more information about the limits and conventions on 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 use the SHOW GLOBAL INDEX statement to query information about global secondary indexes of a table. 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 |
  +---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

  You can use the following statement to query the schema of an index table. A global secondary index table contains the primary key of the base table, shard keys, default covering columns, and custom covering columns. In a global secondary index table, the primary key column is not auto-increment, and local indexes are not contained. In the following example, the index table contains a unique index that is created based on the database sharding key. This is the only index 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

  You can use the following statement to delete a global secondary index that is named g_i_seller. When you delete the global secondary index, the index table that corresponds to the global secondary index is deleted at the same time.

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

• Rename a global secondary index

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