ALTER TABLE
云原生数据仓库AnalyticDB MySQL版支持通过ALTER TABLE
修改表。本文介绍ALTER TABLE
语法。
语法
ALTER TABLE table_name
{
ADD [COLUMN] (column_name column_definition,...)
| ADD {INDEX|KEY} [index_name] (column_name,...)
| ADD CLUSTERED [INDEX|KEY] [index_name] (column_name,...)
| DROP [COLUMN] column_name
| DROP {INDEX|KEY} index_name
| DROP CLUSTERED [INDEX|KEY] index_name
| MODIFY [COLUMN] column_name column_definition
| RENAME COLUMN column_name to new_column_name
| RENAME new_table_name
| TRUNCATE PARTITION {partition_names | ALL}
| STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' hot_partition_count=N}
| PARTITION BY VALUE(column_name|date_format(column_name,'xxxxx')) LIFECYCLE N
| RT_ENGINE= {'ROWSTORE'|'COLUMNSTORE'} }
不支持修改列顺序。
增加列
语法
ALTER TABLE db_name.table_name ADD [COLUMN] column_name data_type;
注意事项
不支持增加主键列。
示例
在CUSTOMER表中增加一列province,数据类型为VARCHAR。
ALTER TABLE adb_demo.customer ADD COLUMN province varchar comment '省份';
删除列
语法
ALTER TABLE db_name.table_name DROP [COLUMN] column_name data_type;
注意事项
不支持删除主键列。
示例
在CUSTOMER表中删除类型为VARCHAR的province列。
ALTER TABLE adb_demo.customer DROP COLUMN province;
更改COMMENT
语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type comment 'new_comment';
示例
将CUSTOMER表中province列的COMMENT更改为顾客所属省份。
ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar comment '顾客所属省份';
设置NULL
语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type {NULL};
注意事项
仅支持将NOT NULL变更为NULL。
示例
将CUSTOMER表中province列的值更改为可空(NULL)。
ALTER TABLE adb_demo.customer MODIFY COLUMN province varchar NULL;
更改DEFAULT值
语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type DEFAULT 'default';
示例
将CUSTOMER表中性别sex的默认值设置为0(性别为男)。
ALTER TABLE adb_demo.customer MODIFY COLUMN sex int(11) NOT NULL DEFAULT 0;
更改列类型
语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name new_data_type;
注意事项
不支持更改主键列的数据类型。
更改数据类型的限制如下:
仅支持将取值范围小的数据类型更改为取值范围大的数据类型。
整型数据类型:支持TINYINT、SMALLINT、INT、BIGINT间,小类型到大类型的更改,例如支持将TINYINT更改为BIGINT,不支持将BIGINT更改为TINYINT。
浮点数据类型:支持将FLOAT更改为DOUBLE类型,不支持将DOUBLE更改为FLOAT类型。
支持将整型数据类型(TINYINT、SMALLINT、INT、BIGINT、SHORT、LONG)变更为浮点类型(FLOAT、DOUBLE)和DECIMAL类型。
支持更改DECIMAL类型精度,仅支持低精度向高精度变更。
重要仅3.1.8内核版本的AnalyticDB MySQL集群支持整型数据类型变更为浮点类型和DECIMAL类型,以及更改DECIMAL类型精度,且该功能目前处于公测中。如需使用,请联系阿里云技术支持升级集群的内核版本并开启该功能。
如何查看集群的内核版本,请参见如何查看集群的内核版本。
示例
以TEST表为例,order_number列原本为INT类型,建表语句如下。
CREATE TABLE adb_demo.test(id int, order_number int NOT NULL DEFAULT 100, name varchar) DISTRIBUTE BY HASH(id);
将TEST表中order_number列由INT类型更改为BIGINT类型。
ALTER TABLE adb_demo.test MODIFY COLUMN order_number BIGINT NOT NULL DEFAULT 100;
新增索引
AnalyticDB MySQL建表时默认创建全列索引index_all='Y'
。若建表时未创建全列索引,可以通过以下方式新增索引。变更索引后需要Build,Build的详细信息,请参见BUILD。
AnalyticDB MySQL不支持创建唯一索引。
语法
ALTER TABLE db_name.table_name ADD KEY index_name(column_name);
示例
在CUSTOMER表中为age列新增索引。
ALTER TABLE adb_demo.customer ADD KEY age_idx(age);
删除索引
语法
ALTER TABLE db_name.table_name DROP KEY index_name;
参数说明
可以通过以下命令获取
index_name
。SHOW INDEXES FROM db_name.table_name;
示例
删除CUSTOMER表中age列的索引。
ALTER TABLE adb_demo.customer DROP KEY age_idx;
更改表名
语法
ALTER TABLE db_name.table_name RENAME new_table_name;
示例
将customer表更名为new_customer。
ALTER TABLE customer RENAME new_customer;
更改列名
语法
ALTER TABLE db_name.table_name RENAME COLUMN column_name to new_column_name;
注意事项
不支持更改主键列的列名。
示例
将customer表中的age列更名为new_age。
ALTER TABLE customer RENAME COLUMN age to new_age;
更改表的生命周期
语法
ALTER TABLE db_name.table_name PARTITIONS N;
执行ALTER TABLE语句后,表的生命周期不会立即变更。如需立即变更,可以手动执行
BUILD TABLE db_name.table_name
,待Build完成后才会生效。如何查看Build任务状态,请参见查看BUILD任务的状态。您可以执行
SHOW CREATE TABLE <table_name>;
,通过执行结果中的PARTITION BY
查看表的生命周期。示例
以customer表为例,原本的生命周期为30,建表语句如下。
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 '客户信息表';
将customer表的生命周期由30改为40。语句如下。
ALTER TABLE customer PARTITIONS 40;
更改表的冷热数据存储策略
目前仅弹性模式集群版(新版)支持冷热数据分层存储功能。
您可以执行ALTER TABLE语句更改表的冷热数据存储属性。
ALTER TABLE table_name storage_policy;
storage_policy:
STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' hot_partition_count=N}
COLD、HOT、MIXED三种策略之间可以任意转换。
执行ALTER TABLE语句后,存储策略不会立即变更。如需立即变更,可以手动执行BUILD TABLE db_name.table_name
,待Build完成后,存储策略才会生效。如何查看Build任务状态,请参见查看BUILD任务的状态。
在创建表时指定冷热存储策略的方法,请参见CREATE TABLE。
示例1:更改表的存储策略为COLD
ALTER TABLE test_table storage_policy = 'COLD';
示例2:更改表的存储策略为HOT
ALTER TABLE test_table storage_policy = 'HOT';
示例3:更改表的存储策略为MIXED,其中热分区的个数为10个
ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10;
更改分区函数
AnalyticDB MySQL集群默认关闭更改分区函数功能。仅3.1.6及以上内核版本的集群支持该功能。如需使用,请联系阿里云技术支持升级集群的内核版本并开启该功能。
如何查看集群的内核版本,请参见如何查看集群的内核版本。
语法
ALTER TABLE table_name PARTITION BY VALUE(column_name|date_format(column_name,'xxxxx')) LIFECYCLE N;
注意事项
不支持将无分区表更改为有分区表,即新增分区字段。
仅支持更改分区函数,不支持更改分区字段。
示例1
ALTER TABLE adb_demo.test PARTITION BY VALUE(c2) LIFECYCLE 10;
示例2
ALTER TABLE adb_demo.test PARTITION BY VALUE(date_format(c2, '%Y%m%d')) LIFECYCLE 10;
更改分区键/分布键
AnalyticDB 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;