云原生数据仓库 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表。
表
变更表名
语法
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 for MySQL支持的分词器类型,请参见全文索引的分词器。WITH DICT tbl_dict_name
:指定全文索引的自定义词典。AnalyticDB for 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 for MySQL支持的分词器类型,请参见全文索引的分词器。WITH DICT tbl_dict_name
:指定全文索引的自定义词典。AnalyticDB for 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)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)
) 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
注意事项
不支持将无分区表更改为分区表,也不支持将分区表变更为无分区表,即不支持新增分区键和删除分区键。
不支持在已有分区键的基础上,增加、减少或变更分区字段。
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;