全部产品
Search
文档中心

云原生数据库 PolarDB:ALTER TABLE(AUTO模式)

更新时间:Feb 04, 2024

您可以通过ALTER TABLE语法改变表的结构,如增加列、增加索引、修改数据定义等。本语法仅适用于AUTO模式数据库。

注意事项

实例版本小于5.4.17-16835173 时,不支持通过ALTER TABLE语法修改拆分字段。

语法

说明

ALTER TABLE用于改变表的结构,如增加列、增加索引和修改数据定义。详细语法请参见MySQL修改表语法

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

示例

  • 增加列

    在“user_log”表中增加一列“idcard”,示例如下:

    ALTER TABLE user_log
        ADD COLUMN idcard varchar(30);
  • 增加索引

    在“user_log”表中为“idcard”列增加一个名为“idcard_idx”的索引,示例如下:

    ALTER TABLE user_log
        ADD INDEX idcard_idx (idcard);
  • 删除索引

    删除“user_log”表中的“idcard_idx”索引,示例如下:

    ALTER TABLE user_log
        DROP INDEX idcard_idx;
  • 重命名索引

    将“user_log”表中“idcard_idx”索引命修改为“idcard_idx_new”,示例如下:

    ALTER TABLE user_log
        RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
  • 修改字段

    将“user_log”表中“idcard”列(字段类型为varchar)的长度由30改为40,语法示例如下:

    ALTER TABLE user_log
        MODIFY COLUMN idcard varchar(40);

全局二级索引

PolarDB-X支持全局二级索引 (Global Secondary Index,GSI),基本原理请参见全局二级索引

列变更

使用全局二级索引的表,对列的修改,语法和普通表的一致。

说明

当修改的表包含全局二级索引时,对列的修改有额外的限制,关于GSI的限制与约定,详情请参见如何使用全局二级索引

索引变更

语法

ALTER TABLE tbl_name
    alter_specification # 全局二级索引相关变更仅支持一条alter_specification

alter_specification:
  | ADD GLOBAL {INDEX|KEY} index_name # 全局二级索引必须显式指定索引名
      [index_type] (index_sharding_col_name,...)
      global_secondary_index_option
      [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
      [INDEX|KEY] index_name # 全局二级索引必须显式指定索引名
      [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

# 关于全局二级索引特有语法,详情请参见CREATE TABLE文档
global_secondary_index_option:
    [COVERING (col_name,...)] # Covering Index
    partition_options # 包含且仅包含 index_sharding_col_name 中指定的列

# 指定索引表拆分方式
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

# 分区函数定义
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

# 分区列表定义
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

# Hash/Key分区表列定义
hash_partition_list:
    PARTITIONS partition_count

# Range/Range Columns分区表列定义
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]

# List/List Columns分区表列定义
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' ]

# 以下为MySQL DDL语法
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}

ALTER TABLE ADD GLOBAL INDEX系列语法用于在建表后添加GSI,该系列语法在MySQL语法上新引入了GLOBAL关键字,用于指定添加的索引类型为GSI。

ALTER TABLE { DROP | RENAME } INDEX语法同样可以对GSI进行修改,目前建表后创建GSI存在一定限制。关于GSI的限制与约定,详情请参见如何使用全局二级索引

全局二级索引定义子句详细说明请参见CREATE TABLE(DRDS模式)

示例

  • 建表后添加全局二级索引

    下面以建立全局唯一索引为例,介绍在建表后如何创建GSI。

    # 创建表
    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;
    
    # 创建全局二级索引
    ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) partition by key(`buyer_id`) partitions 4;
    • 主表:”t_order“只分库不分表,分库的拆分方式为按照”order_id“列进行哈希。

    • 索引表:”g_i_buyer“只分库不分表,分库的拆分方式为按照”buyer_id“列进行哈希,指定覆盖列为”order_snapshot“。

    • 索引定义子句:UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING (order_snapshot) partition by key(`buyer_id`) partitions 4

    通过SHOW INDEX查看索引信息,包含拆分键order_id上的局部索引,和buyer_id、id、order_id和order_snapshot上的GSI,其中buyer_id为索引表的拆分键,id和order_id为默认的覆盖列(主键和主表的拆分键),order_snapshot显式指定的覆盖列。

    说明

    关于GSI的限制与约定,详情请参见如何使用全局二级索引,SHOW INDEX详细说明,请参见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)

    通过SHOW GLOBAL INDEX可以单独查看GSI信息,详情请参见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)
                            

    查看索引表的结构,索引表包含主表的主键、分库分表键、默认的覆盖列和自定义覆盖列,主键列去除了AUTO_INCREMENT属性,并且去除了主表中的局部索引,全局唯一索引默认在索引表的所有分库分表键上创建一个唯一索引,以实现全局唯一约束。

    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)
  • 删除全局二级索引

    删除名为g_i_seller的GSI,相应的索引表也将被删除。

    # 删除索引
    ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
  • 重命名索引

    默认情况下限制对GSI的重命名。关于GSI的限制与约定,详情请参见全局二级索引使用