云原生数据仓库AnalyticDB Mysql(ADB分析型数据库)-DDL语法之ALTER TABLE
ADB分析性数据库语法-DDL之ALTER TABLE
ADB分析型数据库,是阿里云平台上资源,原名叫云原生数据仓库AnalyticDB Mysql,俗称ADB分析型数据库,那么ADB的语法与mysql关系型数据库语法存在一定的差异。
本文为笔记,介绍ADB的DDL中ALTER TABLE语法的应用。
云原生数据仓库AnalyticDB MySQL版支持通过ALTER TABLE修改表。
下文举例的表都使用order_Info表(订单表)
db_name(schema_name)使用adb_order
1. 增加列
- 语法
ALTER TABLE db_name.table_name ADD [COLUMN] column_name data_type;
- 示例
在order_info 表(订单表)中增加一列orderNo(订单号),数据类型为VARCHAR。
ALTER TABLE adb_order.order_info ADD COLUMN orderNO varchar comment '订单号';
2. 删除列
- 语法
ALTER TABLE db_name.table_name DROP [COLUMN] column_name data_type;
- 示例
在order_info 表(订单表)中删除类型为VARCHAR的orderNo(订单号)列。
ALTER TABLE adb_order.order_info DROP COLUMN orderNO;
3. 更改COMMENT
- 语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type comment 'new_comment';
- 示例
将order_info 表(订单表)中orderNo(订单号)列的COMMENT更改为“客户订单号”。
ALTER TABLE adb_order.order_info MODIFY COLUMN orderNo varchar comment '客户订单号';
4. 设置NULL
说明 仅支持将NOT NULL变更为NULL。
- 语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type {NULL};
- 示例
将order_info 表(订单表)中orderNo(订单号)列的值更改为可空(NULL)。
ALTER TABLE adb_order.order_info MODIFY COLUMN orderNo varchar NULL;
5. 更改DEFAULT值
- 语法
ALTER TABLE db_name.table_name MODIFY [COLUMN] column_name data_type DEFAULT 'default';
- 示例
将order_info 表(订单表)中order_num(订单量)的默认值设置为0。
ALTER TABLE adb_order.order_info MODIFY COLUMN order_num int(18) NOT NULL DEFAULT 0;
6. 更改列类型
- 语法
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类型。 - 示例
order_info 表(订单表)order_num(订单量)列原本为INT类型,将由INT类型更改为BIGINT类型。
ALTER TABLE adb_order.order_info MODIFY COLUMN order_num BIGINT NOT NULL DEFAULT 100;
7. 新增索引
AnalyticDB MySQL建表时默认创建全列索引index_all=‘Y’。若建表时未创建全列索引,可以通过以下方式新增索引。变更索引后需要Build。
说明: AnalyticDB MySQL不支持创建唯一索引。
- 语法
ALTER TABLE db_name.table_name ADD KEY index_name(column_name);
- 示例
在order_info 表(订单表)中为order_no(订单号)列新增索引。
ALTER TABLE adb_order.order_info ADD KEY order_no_idx(order_no);
8. 删除索引
- 语法
ALTER TABLE db_name.table_name DROP KEY index_name;
- 参数说明
可以通过以下命令获取index_name。
SHOW INDEXES FROM db_name.table_name;
- 示例
删除order_info 表(订单表)中order_no列的索引。
ALTER TABLE adb_order.order_info DROP KEY order_no_idx;
9. 更改表名
- 语法
ALTER TABLE db_name.table_name RENAME new_table_name;
- 示例
将order_info 表(订单表)更名为new_order_info。
ALTER TABLE adb_order.order_info RENAME new_order_info;
10. 更改列名
说明: 不支持更改主键列的列名。
- 语法
ALTER TABLE db_name.table_name RENAME COLUMN column_name to new_column_name;
- 示例
将order_info 表(订单表)中的order_no列更名为new_order_no。
ALTER TABLE adb_order.order_info RENAME COLUMN order_no to new_order_no;
11. 更改表的生命周期
- 语法
ALTER TABLE db_name.table_name PARTITIONS N;
- 示例
以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。语句如下。
TABLE customer PARTITIONS 40;
12. 更改表的冷热数据存储策略
说明: 目前仅弹性模式集群版(新版)支持冷热数据分层存储功能。
您可以执行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。
在创建表时指定冷热存储策略的方法,请参见CREATE TABLE。
示例:更改表的存储策略为COLD
ALTER TABLE test_table storage_policy = 'COLD';
示例:更改表的存储策略为HOT
ALTER TABLE test_table storage_policy = 'HOT';
示例:更改表的存储策略为MIXED,其中热分区的个数为10个
ALTER TABLE test_table storage_policy = 'MIXED' hot_partition_count = 10;
13. 更改分区键/分布键
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) --分布键customer_id需要添加到主键中
)
DISTRIBUTED BY HASH(customer_id) --修改order_id为customer_id
PARTITION BY VALUE(DATE_FORMAT(order_time, '%Y%m%d')) LIFECYCLE 90 --二级分区保持不变
COMMENT '订单信息表';
- 使用INSERT OVERWRITE INTO SELECT将源表的数据导入到临时表,详情请参见INSERT
OVERWRITE INTO SELECT。
INSERT OVERWRITE INTO 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;