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. This syntax applies to only AUTO mode databases.
Usage notes
The ALTER TABLE statement cannot be executed to change shard keys.
Syntax
The ALTER TABLE statement is executed to modify the schema of a table. For example, you can add columns, add indexes, or change data types. For more information about the syntax, see MySQL ALTER TABLE.
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
[local_partition_alter_options]Example
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 an 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);Delete an 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;Rename an 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`;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.
However, PolarDB-X has limits on the modification of 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
# The syntax applies only to global secondary indexes. For more information, see the documentation of the CREATE TABLE statement.
global_secondary_index_option:
[COVERING (col_name,...)] # Covering Index
partition_options # Specify one or more columns that are contained in index_sharding_col_name.
# Specify a sharding method for an index table.
partition_options:
PARTITION BY
HASH({column_name | partition_func(column_name)})
| KEY(column_list)
| RANGE{({column_name | partition_func(column_name)})
| RANGE COLUMNS(column_list)}
| LIST{({column_name | partition_func(column_name)})
| LIST COLUMNS(column_list)} }
partition_list_spec
# Specify the partitioning function.
partition_func:
YEAR
| TO_DAYS
| TO_SECOND
| UNIX_TIMESTAMP
| MONTH
# Specify the type of the partition list.
partition_list_spec:
hash_partition_list
| range_partition_list
| list_partition_list
# Specify the number of partitions that you want to generate by hash partitioning or key partitioning.
hash_partition_list:
PARTITIONS partition_count
# Specify the ranges by which data is partitioned in range partitioning or range columns partitioning.
range_partition_list:
range_partition [, range_partition ...]
range_partition:
PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
# Specify the lists by which data is partitioned in list partitioning or list columns partitioning.
list_partition_list:
list_partition [, list_partition ...]
list_partition:
PARTITION partition_name VALUES IN (value_list) [partition_spec_options]
partition_spec_options:
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
# 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 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 ( DRDS mode ).
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 partition by key(`order_id`) partitions 4; # Add a global secondary index. ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) partition by key(`buyer_id`) partitions 4;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. The order_id column is specified as the hashed shard key.
Index table: In the preceding example, an index table named g_i_buyer is created in each database shard. The data is sharded by using the hash algorithm. The order_id column is specified as the hashed shard key. The order_snapshot column is specified as a covering column.
Clause used to define the global secondary index:
UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING (order_snapshot) partition by key(`buyer_id`) partitions 4.
Execute the
SHOW INDEXstatement to query index information. The index information includes the local secondary 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.NoteFor more information about the limits and precautions on using global secondary indexes, see How to use global secondary indexes. For more information about the SHOW INDEX statement, see SHOW INDEX.
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_syes_00000 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | t_order_syes_00000 | 1 | l_i_order | 1 | order_id | A | 0 | NULL | NULL | YES | BTREE | | | +--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.05 sec)You can execute the
SHOW GLOBAL INDEXstatement to query only global secondary indexes. For more information, see SHOW GLOBAL INDEX.show global index; +--------+---------+------------+-----------------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ | 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 | +--------+--------+------------+-----------------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ | d1 | t_order | 0 | g_i_buyer_$c1a0 | buyer_id | id, order_id, order_snapshot | NULL | | | NULL | | | NULL | PUBLIC | +--------+---------+------------+-----------------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+ 1 row in set (0.04 sec)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.
show create table g_i_buyer; +-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TABLE | CREATE TABLE | +-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | g_i_buyer_$c1a0 | CREATE TABLE `g_i_buyer_$c1a0` ( `id` bigint(11) NOT NULL, `order_id` varchar(20) DEFAULT NULL, `buyer_id` varchar(20) DEFAULT NULL, `order_snapshot` longtext, UNIQUE KEY `auto_shard_key_buyer_id` USING BTREE (`buyer_id`), KEY `_gsi_pk_idx_` USING BTREE (`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 PARTITION BY KEY(`buyer_id`) PARTITIONS 4 | +-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.10 sec)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, a global secondary index cannot be renamed. For more information about the limits and conventions of global secondary indexes, see How to use global secondary indexes.