ALTER TABLE变更表结构_云原生数据仓库AnalyticDB MySQL版(AnalyticDB for MySQL)-阿里云帮助中心

云原生数据仓库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类型精度，仅支持低精度向高精度变更。

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}

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

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

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

ALTER TABLE adb_demo.customer MODIFY COLUMN province VARCHAR NULL;

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

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

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

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

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

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}'));

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

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

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

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

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

ALTER TABLE db_name.table_name DROP KEY index_name

ALTER TABLE adb_demo.customer DROP KEY age_idx;

```
ALTER TABLE adb_demo.customer DROP KEY index_vj;
```

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

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

ALTER TABLE db_name.table_name DROP CLUSTERED KEY index_name

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

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

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

ALTER TABLE adb_demo.customer DROP FULLTEXT INDEX fidx_k;

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

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);

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

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

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)
);

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

ALTER TABLE db_name.table_name DROP FOREIGN KEY fk_symbol

ALTER TABLE store_returns DROP FOREIGN KEY sr_item_sk_fk;

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

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

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

ALTER TABLE db_name.table_name PARTITIONS N

ALTER TABLE customer PARTITIONS 40;

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

ALTER TABLE customer storage_policy = 'COLD';

ALTER TABLE customer storage_policy = 'HOT';

```
ALTER TABLE customer storage_policy = 'MIXED' hot_partition_count = 10;
```

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 order_auto_opt_v1
SELECT * FROM order;
```

RENAME TABLE order to order_backup; --数据导入完成后，重命名源表作为备份

```
RENAME TABLE order_auto_opt_v1 to order;
```

ALTER TABLE变更表结构

语法

表

变更表名

变更表的COMMENT

列

增加列

语法

注意事项

示例

删除列

语法

注意事项

示例

变更列名

语法

注意事项

示例

变更列的数据类型

语法

注意事项

示例

变更列的默认值

语法

示例

允许空值（NULL）

语法

注意事项

示例

变更列的COMMENT

语法

示例

索引

增加索引

语法

参数说明

示例

增加JSON索引

JSON索引

语法

参数说明

注意事项

示例

JSON Array索引

语法

参数说明

注意事项

示例

删除索引、JSON索引

语法

参数说明

示例

增加聚集索引

语法

注意事项

示例

删除聚集索引

语法

参数说明

示例

增加全文索引

前提条件

语法

参数说明

注意事项

示例

删除全文索引

语法

参数说明

示例

增加向量索引

前提条件

语法

参数说明

示例

增加外键

前提条件

语法

参数说明

示例