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 new_table_name
  | TRUNCATE PARTITION {partition_names | ALL}
 |STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

增加列

  • 语法
    ALTER TABLE db_name.table_name ADD 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_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

说明 仅支持将NOT NULL变更为NULL。
  • 语法
    ALTER TABLE db_name.table_name MODIFY COLUMN column_name data_type {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类型。
  • 示例

    将TEST表中order_number列由Int类型更改为Bigint类型。

    CREATE TABLE adb_demo.test(id int, order_number int NOT NULL DEFAULT 100, name varchar) DISTRIBUTE BY HASH(id);
    ALTER TABLE adb_demo.test MODIFY COLUMN order_number BIGINT NOT NULL DEFAULT 100;

新增索引

说明
  • AnalyticDB for MySQL建表时默认创建全列索引index_all='Y'
  • 若建表时未创建全列索引,可以通过以下方式新增索引。
  • 语法
    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 column column_name to column_newname;
  • 示例

    将CUSTOMER表中的age列更名为new_age。

    ALTER TABLE customer rename column age to new_age;

修改表的生命周期

  • 语法
    ALTER TABLE db_name.table_name partitions N;
  • 示例

    将CUSTOMER表的生命周期由30改为40。

    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 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。

关于如何在创建表时指定冷热存储策略,可参见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;