云原生数据仓库AnalyticDB：ALTER TABLE变更表结构

增加列

语法

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索引

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->'$[*]');

删除索引、JSON索引

ALTER TABLE db_name.table_name DROP KEY index_name

增加聚集索引

语法

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

增加全文索引

前提条件

语法

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及以上版本。

语法

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或以上。

语法

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完成后，表级管理分区才能重新生效。

示例

• 将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;