ALTER TABLE

You can alter the structure of a table using the ALTER TABLE syntax, such as adding columns, adding indexes, changing data definitions, etc.

Precautions

It is not supported to modify the partitioning field through the ALTER TABLE syntax.

Syntax

Note ALTER TABLE is used to alter the structure of a table, such as adding columns, adding indexes, and changing data definitions. For the detailed syntax, please refer to MySQL ALTER TABLE

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

Example

Add a column: To add a column "idcard" to the "user_log" table, the example is as follows:
```
ALTER TABLE user_log
ADD COLUMN idcard varchar(30);
```

Add a local index: To add an index named "idcard_idx" for the "idcard" column in the "user_log" table, the example is as follows:
```
ALTER TABLE user_log
ADD INDEX idcard_idx (idcard);
```

Rename a local index: To rename the "idcard_idx" index to "idcard_idx_new" in the "user_log" table, the example is as follows:
```
ALTER TABLE user_log
RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
```

Delete a local index: To delete the "idcard_idx" index in the "user_log" table, the example is as follows:
```
ALTER TABLE user_log
DROP INDEX idcard_idx;
```

Modify a field: To change the length of the "idcard" column (field type is varchar) from 30 to 40 in the "user_log" table, the syntax example is as follows:
```
ALTER TABLE user_log
MODIFY COLUMN idcard varchar(40);
```

Global Secondary Indexes

PolarDB-X supports Global Secondary Indexes (GSI). For the basic principles, please refer toGlobal Secondary Indexes.

Column Changes

For tables using a Global Secondary Index, the syntax for modifying columns is consistent with that of regular tables. Note When modifying a table that contains a Global Secondary Index, there are additional restrictions on column changes. For detailed information about the limitations and conventions of GSI, please refer toHow to Use Global Secondary Index.

Index Changes Syntax

ALTER TABLE tbl_name
    alter_specification # Changes related to Global Secondary Indexes only support a single alter_specification
alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # Index name must be explicitly specified for Global Secondary Indexes
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # Index name must be explicitly specified for Global Secondary Indexes
      [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 # Includes and only includes the columns specified in index_sharding_col_name
# Specifies the partitioning method for the 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) 
# Below is the MySQL DDL syntax
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}

The ALTER TABLE ADD GLOBAL INDEX syntax series is used for adding a GSI (Global Secondary Index) after a table has been created. This series of syntax introduces the new keyword GLOBAL in MySQL syntax, which is used to specify that the index being added is a GSI.

The ALTER TABLE { DROP | RENAME } INDEX syntax can also be used to modify a GSI. There are certain limitations to creating a GSI after a table has been created. For detailed information about the restrictions and conventions of GSI, please refer to How to Use Global Secondary Index.

For a detailed explanation of the global secondary index definition clause, please see CREATE TABLE.

Example

Add a Global Secondary Index After Table Creation

The following is an example of how to create a GSI with a global unique index after table creation.

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

Main table: "t_order" is only partitioned by database, not by table, with partitioning done by hashing the "order_id" column.
Index table: "g_i_buyer" is only partitioned by database, not by table, with partitioning done by hashing the "buyer_id" column, and the column "order_snapshot" is specified as the covering column.
Index definition clause: GLOBAL INDEX g_i_seller ON t_order (seller_id) dbpartition by hash(seller_id).

Using SHOW INDEX to view index information, this includes local indexes on the partition key "order_id", as well as the GSI on "buyer_id", "id", "order_id", and "order_snapshot". Here, "buyer_id" is the partition key for the index table, "id" and "order_id" are the default covering columns (primary key and the main table's partition key), and "order_snapshot" is the explicitly specified covering column. Note For details on the limitations and conventions of GSI, please refer to How to Use Global Secondary Index，For a detailed explanation of 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 |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

Using SHOW GLOBAL INDEX, you can view the information of a Global Secondary Index (GSI) separately. For more details, please refer to 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 |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

To view the structure of the index table, the index table includes the primary key of the main table, the database and table partitioning keys, the default covering columns, and custom covering columns. The primary key column has the AUTO_INCREMENT attribute removed, and the local indexes from the main table are also removed. A global unique index is created by default on all the database and table partitioning keys of the index table to enforce a global uniqueness constraint.

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`)               |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Drop Global Secondary Index

To drop the GSI named g_i_seller, the corresponding index table will also be deleted.
```
# Drop index
ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
```

Renaming Index

Renaming Index By default, renaming of Global Secondary Indexes (GSI) is restricted. For details on the limitations and conventions of GSI, please refer to Global Secondary Index Usage.

ALTER TABLE

ALTER TABLE

Precautions

Syntax

Global Secondary Indexes

results matching ""

No results matching ""