全部產品
Search

搜尋本產品

    PolarDB：ALTER TABLE

    文件中心

    PolarDB：ALTER TABLE

    更新時間：Jul 06, 2024

    您可以通過ALTER TABLE文法改變表的結構，如增加列、增加索引、修改資料定義等。

    注意事項

    • 不支援通過ALTER TABLE文法修改拆分欄位。
    • 在包含全域二級索引的表中使用ALTER文法，要求MySQL為5.7及以上版本，PolarDB-X 1.0為5.4.1及以上版本。

    變更普通表

    說明 PolarDB-X 1.0中，修改普通表的表結構文法與修改原生MySQL資料庫表結構的文法沒有區別。更多詳情，請參見ALTER TABLE Statement。

    文法

    ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_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”索引命修改為“idcard_idx_new”，樣本如下：

      ALTER TABLE user_log RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
    • 刪除局部索引

      刪除“user_log”表中的“idcard_idx”索引，樣本如下：

      ALTER TABLE user_log DROP INDEX idcard_idx;
    • 修改欄位

      將“user_log”表中“idcard”列（欄位類型為varchar）的長度由30改為40，文法樣本如下：

      ALTER TABLE user_log MODIFY COLUMN idcard varchar(40);

    變更包含全域二級索引的表

    列變更

    包含全域二級索引的表在進行列變更操作時，使用的文法與普通表的列變更的文法一致，但存在一些限制。詳細注意事項請參見ALTER TABLE時的注意事項。

    • 下表匯總了使用ALTER TABLE語句變更列的支援情況。
      語句是否支援變更主表拆分鍵是否支援變更主表主鍵（也即索引表主鍵）是否支援變更本地唯一索引列是否支援變更索引表拆分鍵是否支援變更Unique Index列是否支援變更Index列是否支援變更Covering列
      ADD COLUMN無該情境不支援無該情境無該情境無該情境無該情境無該情境
      ALTER COLUMN SET DEFAULT和ALTER COLUMN DROP DEFAULT不支援不支援支援不支援不支援不支援不支援
      CHANGE COLUMN不支援不支援支援不支援不支援不支援不支援
      DROP COLUMN不支援不支援僅當唯一鍵中只有1列時支援不支援不支援不支援不支援
      MODIFY COLUMN不支援不支援支援不支援不支援不支援不支援
    • 下表匯總了使用ALTER TABLE語句變更索引的支援情況。
      語句是否支援
      ALTER TABLE ADD PRIMARY KEY支援
      ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY支援，您可以同時在主表和索引表上添加局部索引，索引名稱不可與GSI重複。
      ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}禁止
      ALTER TABLE {DISABLE | ENABLE} KEYS支援，僅在主表執行（禁止變更GSI狀態）。
      ALTER TABLE DROP PRIMARY KEY禁止
      ALTER TABLE DROP INDEX僅支援刪除普通索引或全域二級索引。
      ALTER TABLE RENAME INDEX禁止

    索引變更

    文法

    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

global_secondary_index_option:
    [COVERING (col_name,...)] # Covering Index
    drds_partition_options # 包含且僅包含index_sharding_col_name中指定的列

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

# 以下為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。

    樣本

    下面以建立全域唯一索引為例，介紹在建表後如何建立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 dbpartition by hash(`order_id`);
# 建立全域二級索引
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“。
      • 索引定義子句：GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`)。
    • 通過SHOW INDEX查看索引資訊，包含拆分鍵order_id上的局部索引，和buyer_id、id、order_id和order_snapshot上的GSI，其中buyer_id為索引表的拆分鍵，id和order_id為預設的覆蓋列（主鍵和主表的拆分鍵），order_snapshot顯式指定的覆蓋列。
      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 |               |
+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
    • 通過SHOW GLOBAL INDEX可以單獨查看GSI資訊，詳情請參見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 |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
    • 查看索引表的結構，索引表包含主表的主鍵、分庫分表鍵、預設的覆蓋列和自訂覆蓋列，主鍵列去除了AUTO_INCREMENT屬性，並且去除了主表中的局部索引，全域唯一索引預設在索引表的所有分庫分表鍵上建立一個唯一索引，以實現全域唯一約束。
      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`) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    • 刪除全域二級索引

      刪除名為g_i_seller的GSI，相應的索引表也將被刪除。

      ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
    • 重新命名索引

      預設情況下限制對GSI的重新命名。