All Products
Search
Document Center

ALTER TABLE

Last Updated: Sep 07, 2020

Syntax

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

The ALTER TABLE statement is used to modify table structures, such as adding a column, adding an index, and modifying a data definition. For the detailed syntax, see MySQL ALTER TABLE Statement.

Note: Shard keys cannot be modified.

  • Add a column:
  1. ALTER TABLE user_log
  2. ADD COLUMN idcard varchar(30);
  • Add an index:
  1. ALTER TABLE user_log
  2. ADD INDEX idcard_idx (idcard);
  • Delete an index:
  1. ALTER TABLE user_log
  2. DROP INDEX idcard_idx;
  • Rename an index:
  1. ALTER TABLE user_log
  2. RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
  • Modify a field:
  1. ALTER TABLE user_log
  2. MODIFY COLUMN idcard varchar(40);

GSI

Global secondary indexes (GSIs) can be used in Distributed Relational Database Service (DRDS) only when the DRDS version is 5.4.1 or later and the MySQL version is 5.7 or later.

For more information about how to use GSIs in DRDS, see GSIs.

Modify a column

The syntax of the ALTER TABLE statement for modifying a column of a table that uses a GSI is the same as that used to modify a column of a common table.

Note: When you modify a column of a table that contains a GSI, you must also follow the limits on GSIs described in Use GSIs.

Modify an index

Syntax:

  1. ALTER TABLE tbl_name
  2. alter_specification # Only one alter_specification parameter can be included for a GSI-related change.
  3. alter_specification:
  4. | ADD GLOBAL {INDEX|KEY} index_name # An index name must be explicitly specified for a GSI.
  5. [index_type] (index_sharding_col_name,...)
  6. global_secondary_index_option
  7. [index_option] ...
  8. | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
  9. [INDEX|KEY] index_name # An index name must be explicitly specified for a GSI.
  10. [index_type] (index_sharding_col_name,...)
  11. global_secondary_index_option
  12. [index_option] ...
  13. | DROP {INDEX|KEY} index_name
  14. | RENAME {INDEX|KEY} old_index_name TO new_index_name
  15. # GSI-specific syntax. For more information, see MySQL CREATE TABLE Statement.
  16. global_secondary_index_option:
  17. [COVERING (col_name,...)] # Covering Index
  18. drds_partition_options # Only the columns specified by index_sharding_col_name are included.
  19. # Specify a sharding method for an index table
  20. drds_partition_options:
  21. DBPARTITION BY db_sharding_algorithm
  22. [TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]
  23. db_sharding_algorithm:
  24. HASH([col_name])
  25. | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  26. | UNI_HASH(col_name)
  27. | RIGHT_SHIFT(col_name, n)
  28. | RANGE_HASH(col_name, col_name, n)
  29. table_sharding_algorithm:
  30. HASH(col_name)
  31. | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  32. | UNI_HASH(col_name)
  33. | RIGHT_SHIFT(col_name, n)
  34. | RANGE_HASH(col_name, col_name, n)
  35. # MySQL DDL syntax
  36. index_sharding_col_name:
  37. col_name [(length)] [ASC | DESC]
  38. index_option:
  39. KEY_BLOCK_SIZE [=] value
  40. | index_type
  41. | WITH PARSER parser_name
  42. | COMMENT 'string'
  43. index_type:
  44. USING {BTREE | HASH}

The ALTER TABLE ADD GLOBAL INDEX syntax is used to add a GSI to a table after the table is created. This syntax introduces the GLOBAL keyword to the MySQL syntax to set the type of index you want to add to GSI.

The ALTER TABLE { DROP | RENAME } INDEX syntax can also be used to modify a GSI. Limits are imposed on creating a GSI for a table after the table is created. For limits on GSIs, see Use GSIs.

For the GSI definition clause, see CREATE TABLE.

Add a GSI to a table after the table is created

The following example describes how to create a GSI after a table is created.

  1. # Create a table first.
  2. CREATE TABLE t_order (
  3. `id` bigint(11) NOT NULL AUTO_INCREMENT,
  4. `order_id` varchar(20) DEFAULT NULL,
  5. `buyer_id` varchar(20) DEFAULT NULL,
  6. `seller_id` varchar(20) DEFAULT NULL,
  7. `order_snapshot` longtext DEFAULT NULL,
  8. `order_detail` longtext DEFAULT NULL,
  9. PRIMARY KEY (`id`),
  10. KEY `l_i_order` (`order_id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
  12. # Then, create a GSI.
  13. ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

Where:

  • Primary table: The t_order is the primary table, on which database sharding is performed by hashing based on the order_id column.
  • Index table: g_i_buyer is used for databases sharding by hashing based on the buyer_id column. The order_snapshot column is specified as the covering column.
  • GSI definition clause: UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`).

Execute SHOW INDEX to view index information, including the local indexes on shard key order_id and GSIs on buyer_id, id, order_id, and order_snapshot. In particular, buyer_id is the shard key of the index table, id and order_id are the default covering columns (which are the primary key and the shard key of the primary table, respectively), and order_snapshot is an explicitly specified covering column.

Note: For limits on GSIs, see Use GSIs. For more information about SHOW INDEX, see SHOW INDEX.

  1. mysql> show index from t_order;
  2. +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
  3. | TABLE | NON_UNIQUE | KEY_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT | INDEX_COMMENT |
  4. +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
  5. | t_order | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
  6. | t_order | 1 | l_i_order | 1 | order_id | A | 0 | NULL | NULL | YES | BTREE | | |
  7. | t_order | 0 | g_i_buyer | 1 | buyer_id | NULL | 0 | NULL | NULL | YES | GLOBAL | INDEX | |
  8. | t_order | 1 | g_i_buyer | 2 | id | NULL | 0 | NULL | NULL | | GLOBAL | COVERING | |
  9. | t_order | 1 | g_i_buyer | 3 | order_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | |
  10. | t_order | 1 | g_i_buyer | 4 | order_snapshot | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | |
  11. +---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

To view GSI information only, execute SHOW GLOBAL INDEX. For more information, see SHOW GLOBAL INDEX.

  1. mysql> show global index from t_order;
  2. +---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
  3. | 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 |
  4. +---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
  5. | 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 |
  6. +---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

View the structure of an index table. The index table includes the primary key of the primary table, the shard key, default covering columns, and custom covering columns. The AUTO_INCREMENT attribute has been deleted from the primary key column. By default, a local unique index is created on the index table that contains all the index columns of the GSI to achieve the global unique constraint of the primary table.

  1. mysql> show create table g_i_buyer;
  2. +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | g_i_buyer | CREATE TABLE `g_i_buyer` (
  6. `id` bigint(11) NOT NULL,
  7. `order_id` varchar(20) DEFAULT NULL,
  8. `buyer_id` varchar(20) DEFAULT NULL,
  9. `order_snapshot` longtext,
  10. PRIMARY KEY (`id`),
  11. UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`) |
  13. +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Delete a GSI

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

Delete the g_i_seller GSI. The corresponding index table will also be deleted.

Rename an index

Note: By default, GSIs cannot be renamed. For limits on GSIs, see Use GSIs.