全部产品
Search
文档中心

ALTER TABLE

更新时间: 2020-09-07

语法

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

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

注意:不允许修改拆分字段。

  • 增加列:
  1. ALTER TABLE user_log
  2. ADD COLUMN idcard varchar(30);
  • 增加索引:
  1. ALTER TABLE user_log
  2. ADD INDEX idcard_idx (idcard);
  • 删除索引:
  1. ALTER TABLE user_log
  2. DROP INDEX idcard_idx;
  • 重命名索引:
  1. ALTER TABLE user_log
  2. RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
  • 修改字段:
  1. ALTER TABLE user_log
  2. MODIFY COLUMN idcard varchar(40);

全局二级索引

版本限制:MySQL 版本 >= 5.7, 并且 DRDS 版本 >= 5.4.1

DRDS支持全局二级索引 (Global Secondary Index, GSI) ,关于GSI基本原理,详情请参见全局二级索引

列变更

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

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

索引变更

语法:

  1. ALTER TABLE tbl_name
  2. alter_specification # 全局二级索引相关变更仅支持一条 alter_specification
  3. alter_specification:
  4. | ADD GLOBAL {INDEX|KEY} index_name # 全局二级索引必须显式指定索引名
  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 # 全局二级索引必须显式指定索引名
  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. # 关于全局二级索引特有语法,详情请参见CREATE TABLE
  16. global_secondary_index_option:
  17. [COVERING (col_name,...)] # Covering Index
  18. drds_partition_options # 包含且仅包含 index_sharding_col_name 中指定的列
  19. # 指定索引表拆分方式
  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 语法
  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}

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

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

关于全局二级索引定义子句详细说明,请参见CREATE TABLE

建表后添加全局二级索引

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

  1. # 先建表
  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. # 再建全局二级索引
  13. ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);

其中:

  • 主表:t_order 只分库不分表,分库的拆分方式为按照order_id列进行哈希。
  • 索引表:g_i_buyer 只分库不分表,分库的拆分方式为按照buyer_id列进行哈希,指定覆盖列为 order_snapshot。
  • 索引定义子句:UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)

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

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

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

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

  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. +-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

删除全局二级索引

  1. # 删除索引
  2. ALTER TABLE `t_order` DROP INDEX `g_i_seller`;

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

重命名索引

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