AnalyticDB for 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表。
表
變更表名
文法
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
。查看湖倉版叢集的核心版本,請執行
SELECT adb_version();
。如需升級核心版本,請聯絡支援人員。查看和升級數倉版叢集的核心版本,請參見查看和升級版本。
為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 for MySQL叢集。
如何查看叢集的核心版本,請參見如何查看執行個體版本資訊。
文法
ALTER TABLE db_name.table_name ADD FULLTEXT [INDEX|KEY] index_name (column_name) [index_option]
參數說明
column_name:全文索引的列。必須是VARCHAR類型的列。
index_option:指定全文索引的分詞器和自訂字典。可選。
注意事項
增加全文索引後,需要等待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:指定全文索引的分詞器和自訂字典。可選。
樣本
為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)2+(x2-y2)2+…...(xn-yn)2
。
樣本
假設已有表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)
) DISTRIBUTED 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或以上。
查看湖倉版叢集的核心版本,請執行SELECT adb_version();
。如需升級核心版本,請聯絡支援人員。
文法
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)
)
DISTRIBUTED 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及以上版本。
說明查看湖倉版叢集的核心版本,請執行
SELECT adb_version();
。如需升級核心版本,請聯絡支援人員。聯絡阿里雲支援人員開啟變更分區函數的功能。
文法
ALTER TABLE table_name PARTITION BY VALUE(column_name|date_format(column_name,'format')) LIFECYCLE N
注意事項
不支援將無分區表更改為分區表,也不支援將分區表變更為無分區表,即不支援新增分區鍵和刪除分區鍵。
不支援在已有分區鍵的基礎上,增加、減少或變更分區欄位。
3.2.1.1以下核心版本,以分區級管理分區的生命週期時,變更分區函數後,需要等待BUILD完成後,新的分區函數才會生效。
3.2.1.1及以上核心版本,以表級管理分區的生命週期時,變更分區函數後,需要進行兩次BUILD操作。第一次BUILD完成後,新的分區函數生效,但此次BUILD分區保留的方式為分區級。第二次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;