ALTER TABLE變更表結構 -

雲原生資料倉儲AnalyticDB MySQL版支援通過ALTER TABLE修改表結構，包括修改表名、列名、列類型、普通索引、叢集索引、外鍵索引、分區函數的格式、冷熱階層式存放區策略。本文介紹ALTER TABLE文法。

文法

ALTER TABLE table_name
  { ADD ANN [INDEX|KEY] [index_name] (column_name) [algorithm=HNSW_PQ ] [distancemeasure=SquaredL2]
  | ADD CLUSTERED [INDEX|KEY] [index_name] (column_name,...)
  | ADD [COLUMN] column_name column_definition 
  | ADD [COLUMN] (column_name column_definition,...) 
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)
  | ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]
  | ADD {INDEX|KEY} [index_name] (column_name,...) 
  | ADD {INDEX|KEY} [index_name] (column_name|column_name->'$.json_path',...) 
  | ADD {INDEX|KEY} [index_name] (column_name->'$[*]') 
  | COMMENT 'comment'
  | DROP CLUSTERED KEY index_name
  | DROP [COLUMN] column_name
  | DROP FOREIGN KEY symbol
  | DROP FULLTEXT INDEX index_name
  | DROP {INDEX|KEY} index_name
  | MODIFY [COLUMN] column_name column_definition
  | RENAME COLUMN column_name TO new_column_name
  | RENAME new_table_name
  | storage_policy
  | PARTITION BY VALUE(column_name|date_format(column_name,'format')) LIFECYCLE N
  }
  
  column_definition:
   column_type [column_attributes][column_constraints][COMMENT 'comment']
  
  column_attributes:
   [DEFAULT{constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT]
  
  column_constraints:
   [NULL|NOT NULL]
  
  storage_policy:
   STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' hot_partition_count=N}

本文所有樣本（JSON索引樣本、外鍵索引樣本、向量索引樣本除外）均基於CREATE TABLE文檔中建立的customer表。如果您已建立customer表，可直接執行本文樣本。如果還未建立，請先複製以下建表語句建立customer表。

樣本的建表語句

CREATE TABLE customer (
  customer_id BIGINT NOT NULL COMMENT '顧客ID',
  customer_name VARCHAR NOT NULL COMMENT '顧客姓名',
  phone_num BIGINT NOT NULL COMMENT '電話',
  city_name VARCHAR NOT NULL COMMENT '所屬城市',
  sex INT NOT NULL COMMENT '性別',
  id_number VARCHAR NOT NULL COMMENT '社會安全號碼碼',
  home_address VARCHAR NOT NULL COMMENT '家庭住址',
  office_address VARCHAR NOT NULL COMMENT '辦公地址',
  age INT NOT NULL COMMENT '年齡',
  login_time TIMESTAMP NOT NULL COMMENT '登入時間',
  PRIMARY KEY (login_time,customer_id，phone_num)
 )
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT '客戶資訊表';

表

變更表名

文法

ALTER TABLE db_name.table_name RENAME new_table_name

樣本

將customer表更名為new_customer。

ALTER TABLE customer RENAME new_customer;

變更表的COMMENT

文法

ALTER TABLE db_name.table_name COMMENT 'comment'

樣本

將customer表的Comment變更為顧客表。

ALTER TABLE customer COMMENT '顧客表';;

列

增加列

文法

ALTER TABLE db_name.table_name ADD [COLUMN] 
 {column_name column_type [DEFAULT {constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT] [NULL|NOT NULL] [COMMENT 'comment']
 | (column column_type [DEFAULT {constant|CURRENT_TIMESTAMP}|AUTO_INCREMENT] [NULL|NOT NULL] [COMMENT 'comment'],...)}

注意事項

不支援增加主鍵列。

樣本

在customer表中增加一列province，資料類型為VARCHAR。

ALTER TABLE adb_demo.customer ADD COLUMN province VARCHAR COMMENT '省份';

在customer表中增加兩列，一列為vip，資料類型為Boolean，另一列為tags，資料類型為VARCHAR。

ALTER TABLE adb_demo.customer ADD COLUMN (vip BOOLEAN COMMENT '是否會員',tags VARCHAR DEFAULT '無' COMMENT '標籤');

刪除列

文法

ALTER TABLE db_name.table_name DROP [COLUMN] column_name

注意事項

不支援刪除主鍵列。

樣本

在customer表中刪除類型為VARCHAR的province列。

ALTER TABLE adb_demo.customer DROP COLUMN province;

變更列名

文法

ALTER TABLE db_name.table_name RENAME COLUMN column_name to  new_column_name

注意事項

不支援更改主鍵列的列名。

樣本

將customer表中的city_name列更名為city。

ALTER TABLE customer RENAME COLUMN city_name to city;

變更列的資料類型

文法

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name new_column_type

注意事項

不支援變更主鍵列的資料類型。
變更資料類型的限制如下：
- 僅支援將取值範圍小的資料類型更改為取值範圍大的資料類型。
- 整數資料型別：支援TINYINT、SMALLINT、INT、BIGINT間，小類型到大類型的更改，例如支援將TINYINT更改為BIGINT，不支援將BIGINT更改為TINYINT。
- 浮點數據類型：支援將FLOAT更改為DOUBLE類型，不支援將DOUBLE更改為FLOAT類型。
- 支援將整數資料型別（TINYINT、SMALLINT、INT、BIGINT）變更為浮點類型（FLOAT、DOUBLE）。
- 支援變更DECIMAL類型精度，僅支援低精度向高精度變更。
重要
僅核心版本為3.1.8.10~3.1.8.x、3.1.9.6~3.1.9.x、3.1.10.3~3.1.10.x和3.2.0.1及以上版本的叢集支援將整數資料型別變更為浮點類型和變更DECIMAL類型精度。

樣本

將customer表中age列由INT類型更改為BIGINT類型。

ALTER TABLE adb_demo.customer MODIFY COLUMN age BIGINT;

變更列的預設值

文法

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_type DEFAULT {constant | CURRENT_TIMESTAMP}

樣本

將customer表中的sex列的預設值設定為0。

ALTER TABLE adb_demo.customer MODIFY COLUMN sex INT NOT NULL DEFAULT 0;

將customer表中的login_time列的預設值設定為CURRENT_TIMESTAMP。

ALTER TABLE adb_demo.customer MODIFY COLUMN login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

允許空值（NULL）

文法

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_type {NULL}

注意事項

僅支援將NOT NULL變更為NULL。

樣本

將customer表中province列的值更改為可空（NULL）。

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR NULL;

變更列的COMMENT

文法

ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name column_type COMMENT 'new_comment'

樣本

將customer表中province列的COMMENT更改為顧客所屬省份。

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR COMMENT '顧客所屬省份';

索引

增加索引

AnalyticDB for MySQL建表時預設建立全列索引index_all='Y'。若建表時未建立全列索引，可以新增索引。

文法

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name,...)

參數說明

column_name：列名。資料類型為單一資料型別。如果列的資料類型為JSON，請參見增加JSON索引。

樣本

在customer表中為age列新增索引。

ALTER TABLE adb_demo.customer ADD KEY age_idx(age);

增加JSON索引

JSON索引

文法

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name|column_name->'$.json_path',...)

參數說明

column_name：用於為JSON列建立索引。列的資料類型需要為JSON類型。
column_name->'$.json_path'：用於為JSON的某個屬性鍵建立索引。其中column_name指定的列需要為JSON類型。關於JSON索引的更多詳情，請參見JSON索引。
重要
- 僅3.1.6.8及以上核心版本的叢集支援column_name->'$.json_path。
  如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。
- 為JSON列中的指定屬性鍵建立索引時，若該JSON列已存在INDEX索引，需先刪除該列的INDEX索引，否則會報錯。

注意事項

增加JSON索引後，需要等待BUILD完成後，JSON索引才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。通過SHOW CREATE TABLE db_name.table_name;可以查詢JSON索引是否已生效。

重要

若表為分區表，增加JSON索引後，必須手動強制全表BUILD，JSON索引才會生效。

樣本

假設表結構與寫入語句如下。

CREATE TABLE json_test(
  id INT,
  vj JSON
)
DISTRIBUTED BY HASH(id);

INSERT INTO json_test VALUES(1,'{"a":1,"b":2}'),(2,'{"a":2,"b":3}'));

為json_test表的vj列的屬性a建立JSON索引。

ALTER TABLE json_test ADD KEY age_idx(vj->'$.a');

JSON Array索引

文法

ALTER TABLE db_name.table_name ADD {INDEX|KEY} [index_name] (column_name->'$[*]')

參數說明

column_name->'$[*]'：column_name為JSON Array索引的列。例如：vj->'$[*]'表示為vj列建立JSON Array索引。

注意事項

增加JSON Array索引後，需要等待BUILD完成後，JSON Array才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。

樣本

假設表結構與寫入語句如下。

CREATE TABLE json_test(
  id INT,
  vj JSON
)
DISTRIBUTED BY HASH(id);

INSERT INTO json_test VALUES(1, '["CP-018673", 1, false]');

為json_test表的vj列建立JSON Arrary索引。

ALTER TABLE json_test ADD KEY index_vj(vj->'$[*]');

刪除索引、JSON索引

文法

ALTER TABLE db_name.table_name DROP KEY index_name

參數說明

index_name：普通索引名稱。您通過SHOW INDEX FROM db_name.table_name;查詢index_name。

樣本

刪除customer表中名為age_idx的索引。

ALTER TABLE adb_demo.customer DROP KEY age_idx;

刪除json_test表中名為index_vj的JSON Array索引。
```
ALTER TABLE adb_demo.customer DROP KEY index_vj;
```

增加叢集索引

文法

ALTER TABLE db_name.table_name ADD CLUSTERED [INDEX|KEY] [index_name] (column_name,...)

注意事項

如果表已存在叢集索引，則無法再添加叢集索引。原因為一個表只能有一個叢集索引。
增加叢集索引後，需要等待BUILD完成後，叢集索引才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。通過SHOW CREATE TABLE db_name.table_name;可以查詢叢集索引是否已生效。

樣本

在customer表中為customer_id列新增叢集索引。

ALTER TABLE adb_demo.customer ADD CLUSTERED KEY (customer_id);

刪除叢集索引

文法

ALTER TABLE db_name.table_name DROP CLUSTERED KEY index_name

參數說明

index_name：叢集索引名稱。您可以通過SHOW CREATE TABLE db_name.table_name查詢叢集索引名稱。

樣本

在customer表中為customer_id刪除叢集索引。

ALTER TABLE adb_demo.customer ADD CLUSTERED KEY (customer_id);

增加全文索引

前提條件

叢集核心版本需為3.1.4.9及以上版本。

說明

推薦使用核心版本為3.1.4.17及以上的AnalyticDB MySQL叢集。
如何查看叢集的核心版本，請參見如何查看執行個體版本資訊。

文法

ALTER TABLE db_name.table_name ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]

參數說明

column_name：全文索引的列。必須是VARCHAR類型的列。
index_option：指定全文索引的分詞器和自訂字典。可選。
- WITH ANALYZER analyzer_name：指定全文索引的分詞器。AnalyticDB MySQL版支援的分詞器類型，請參見全文索引的分詞器。
- WITH DICT tbl_dict_name：指定全文索引的自訂字典。AnalyticDB MySQL版自訂字典的詳細資料，請參見全文索引的自訂字典。

注意事項

增加全文索引後，需要等待BUILD完成後，全文索引才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。

樣本

為customer表的home_address列添加全文索引。

ALTER TABLE adb_demo.customer ADD FULLTEXT INDEX fidx_k(home_address) WITH ANALYZER standard;

刪除全文索引

文法

ALTER TABLE db_name.table_name DROP FULLTEXT INDEX index_name

參數說明

column_name：全文索引的列。必須是VARCHAR類型的列。
index_option：指定全文索引的分詞器和自訂字典。可選。
- WITH ANALYZER analyzer_name：指定全文索引的分詞器。AnalyticDB MySQL版支援的分詞器類型，請參見全文索引的分詞器。
- WITH DICT tbl_dict_name：指定全文索引的自訂字典。AnalyticDB MySQL版自訂字典的詳細資料，請參見全文索引的自訂字典。

樣本

為customer表刪除全文索引fidx_k。

ALTER TABLE adb_demo.customer DROP FULLTEXT INDEX fidx_k;

更多詳情請參見建立全文索引。

增加向量索引

前提條件

叢集的核心版本需為3.1.4.0及以上版本。

說明

核心版本為3.1.5.16、3.1.6.8、3.1.8.6及以上版本的叢集向量索引功能相對穩定。
若您的叢集不是上述列舉的穩定版本，建議您先將參數CSTORE_PROJECT_PUSH_DOWN和CSTORE_PPD_TOP_N_ENABLE設定為false，再使用向量索引功能。
如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。

文法

ALTER TABLE db_name.table_name ADD ANN [INDEX|KEY] [index_name] (column_name) [algorithm=HNSW_PQ ] [distancemeasure=SquaredL2]

參數說明

index_name：索引名。索引的命名規則，請參見命名約束。
column_name：向量列的名稱。向量列的類型需要為array <float>、array <byte>、array <smallint>。
algorithm：向量距離計算公式使用的演算法，取值僅支援：HNSW_PQ。
distancemeasure：向量距離計算公式，取值僅支援：SquaredL2。SquaredL2的計算公式為：(x1-y1)²+(x2-y2)²+…...(xn-yn)²。

樣本

假設已有表vector，建表語句如下。

CREATE TABLE vector (
  xid BIGINT not null,
  cid BIGINT not null,
  uid VARCHAR not null,
  vid VARCHAR not null,
  wid VARCHAR not null,
  float_feature array < FLOAT >(4),
  short_feature array < SMALLINT >(4),
  PRIMARY KEY (xid, cid, vid)
) DISTRIBUTE BY HASH(xid);

為float_feature和short_feature建立向量索引，樣本如下。

ALTER TABLE vector ADD ANN INDEX idx_float_feature(float_feature);
ALTER TABLE vector ADD ANN INDEX idx_short_feature(short_feature);

增加外鍵

前提條件

AnalyticDB for MySQL叢集核心版本需為3.1.10或以上。

說明

如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。

文法

ALTER TABLE db_name.table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES db_name.pk_table_name (pk_column_name)

參數說明

db_name.table_name：需要添加或刪除外鍵的表。
symbol：可選項，外鍵約束名，在表內唯一。不指定時，解析器將會在外鍵列名後面自動補充尾碼_fk用作外鍵約束名。
fk_column_name：指定外鍵列。外鍵列需要在建表語句中定義。
pk_table_name：指定主表名。主表必須已存在。
pk_column_name：指定外鍵約束列，該列必須存在且為主表的主鍵列。

注意事項

每個表可以有多個外鍵索引。
不支援複合的外鍵索引，即不支援多個列組成的外鍵索引，例如：FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk)。
AnalyticDB for MySQL不會進行資料的約束檢查。您需要自行確保主表的主鍵和從表的外鍵之間的資料約束關係。
外表不支援建立外鍵約束。

樣本

假設有兩張表item和store_sales，建表語句如下。

CREATE TABLE item
(
 i_item_sk BIGINT NOT NULL,
 i_current_price BIGINT,
 PRIMARY KEY(i_item_sk)
)
DISTRIBUTE BY HASH(i_item_sk);

CREATE TABLE store_sales
(
 ss_sale_id BIGINT,
 ss_store_sk BIGINT,
 ss_item_sk BIGINT NOT NULL,
 PRIMARY KEY(ss_sale_id)
);

為表store_sales添加外鍵ss_item_sk，與主表item的i_item_sk相關聯，樣本如下。

ALTER TABLE store_sales ADD CONSTRAINT ss_item_sk FOREIGN KEY (ss_item_sk) REFERENCES item (i_item_sk);

更多詳情請參見通過主外鍵約束消除多餘的JOIN。

刪除外鍵

文法

ALTER TABLE db_name.table_name DROP FOREIGN KEY fk_symbol

樣本

ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

分區

變更分區函數格式

前提條件

變更分區函數，AnalyticDB for MySQL叢集需滿足以下全部條件：

核心版本為3.1.6及以上版本。
說明
如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。
聯絡阿里雲支援人員開啟變更分區函數的功能。

文法

ALTER TABLE table_name PARTITION BY VALUE(column_name|date_format(column_name,'format')) LIFECYCLE N

注意事項

變更分區函數後，需要等待BUILD完成後，新的分區函數才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。通過SHOW CREATE TABLE db_name.table_name;可以查詢新的分區函數是否已生效。
不支援將無分區表更改為分區表，也不支援將分區表變更為無分區表，即不支援新增分區鍵和刪除分區鍵。
不支援在已有分區鍵的基礎上，增加、減少或變更分區欄位。

樣本

將customer表的分區方式由PARTITION BY VALUE(date_format(login_time, '%Y%m%d')) LIFECYCLE 30變更為 PARTITION BY VALUE(login_time) LIFECYCLE 10。

ALTER TABLE adb_demo.customer PARTITION BY VALUE(login_time) LIFECYCLE 10;

將customer表的分區方式由PARTITION BY VALUE(date_format(login_time, '%Y%m%d')) LIFECYCLE 30變更為 PARTITION BY VALUE(date_format(login_time, '%Y%m')) LIFECYCLE 30。

ALTER TABLE adb_demo.test PARTITION BY VALUE(date_format(login_time, '%Y%m')) LIFECYCLE 30;

變更分區的生命週期

文法

ALTER TABLE db_name.table_name PARTITIONS N

注意事項

變更分區的生命週期後，需要等待BUILD完成後，新的生命週期才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。通過SHOW CREATE TABLE db_name.table_name;查詢新的生命週期是否已生效。

樣本

將customer表的生命週期由30改為40。

ALTER TABLE customer PARTITIONS 40;

儲存策略

變更冷熱階層式存放區策略

前提條件

叢集的產品系列為湖倉版或數倉版彈性模式彈性叢集。

文法

ALTER TABLE db_name.table_name STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' hot_partition_count=N}

注意事項

變更冷熱階層式存放區策略後，需要等待BUILD完成後，新的儲存策略才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。通過SHOW CREATE TABLE db_name.table_name;可以查詢新的儲存策略是否生效。

樣本

更改customer表的儲存策略為COLD。

ALTER TABLE customer storage_policy = 'COLD';

更改customer表的儲存策略為HOT。

ALTER TABLE customer storage_policy = 'HOT';

更改customer表的儲存策略為MIXED，其中熱分區的個數為10個。
```
ALTER TABLE customer storage_policy = 'MIXED' hot_partition_count = 10;
```

常見問題

是否支援變更列的順序？

不支援變更列順序。

怎樣把VARCHAR類型的列變更為LONGTEXT類型？

AnalyticDB for MySQL支援的字串類型包括varchar和binary。其中，varchar對應MySQL中的char、varchar、text、mediumtext或者longtext。因此，您無需將VARCHAR類型的列變更為LONGTEXT類型。

已建表且表中已有資料，現新增一個自增列，歷史資料的自增列值為空白，能否自增填充？

不能。已建表且表中已有資料時，增加自增列，只有新寫入資料的自增列的值是自增的。如果需要歷史資料的自增列值自增填充，您需要建立一個包含自增列的表，並遷移資料到新表。

是否支援變更分布鍵或分區鍵？

AnalyticDB for MySQL不支援增加、刪除或變更分布鍵或分區鍵。如果您的業務需要變更分布鍵或分區鍵，可嘗試以下方案解決。

假設您有一個表order需要將現有分布鍵order_id更改為customer_id，操作如下：

使用分布鍵customer_id建立一個暫存資料表order_auto_opt_v1。

CREATE TABLE order_auto_opt_v1 (
  order_id bigint NOT NULL COMMENT '訂單ID',
  customer_id bigint NOT NULL COMMENT '顧客ID',
  customer_name varchar NOT NULL COMMENT '顧客姓名',
  order_time timestamp NOT NULL COMMENT '訂單時間',
  --省略其他欄位
  PRIMARY KEY (order_id,customer_id,order_time) --分布鍵customer_id和分區鍵order_time需要添加到主鍵中
)
DISTRIBUTED BY HASH(customer_id) --修改order_id為customer_id
PARTITION BY VALUE(DATE_FORMAT(order_time, '%Y%m%d')) LIFECYCLE 90 --二級分區保持不變
COMMENT '訂單資訊表';

使用INSERT OVERWRITE SELECT將源表的資料匯入到暫存資料表，詳情請參見INSERT OVERWRITE SELECT。
```
INSERT OVERWRITE order_auto_opt_v1
SELECT * FROM order;
```
判斷分布鍵是否合理。資料匯入後，需要判斷新的分布鍵是否有資料扭曲問題，詳情請參見資料建模診斷。

使用RENAME TABLE <源表表名> to <new_源表表名>;更改源表表名。

RENAME TABLE order to order_backup; --資料匯入完成後，重新命名源表作為備份

使用RENAME TABLE <暫存資料表表名> to <源表表名>;將暫存資料表表名更改為源表表名。
```
RENAME TABLE order_auto_opt_v1 to order;
```

是否支援增加或變更主鍵？

不支援增加、刪除主鍵，即無主鍵表不能變更為有主鍵表，有主鍵表也不能變更為無主鍵表。
不支援增加或減少主鍵列。
不支援變更主鍵列的列名。
不支援變更主鍵列的資料類型。

修改生命週期或冷熱階層式存放區策略，為什麼沒有生效？

修改生命週期或冷熱階層式存放區策略後，需要等待BUILD完成後，新的生命週期或儲存策略才會生效。自動BUILD、手動BUILD以及BUILD任務的進度，請參見BUILD。

當通過SHOW CREATE TABLE db_name.table_name;查詢到新設定的生命週期或儲存策略時，說明新的生命週期或儲存策略已生效。