如何使用ALTER TABLE数据定义语言

该语句用于修改已存在的表的结构，比如：修改表及表属性、新增列、修改列及属性、删除列等。
格式 ALTER TABLE tblname
    alter_specification [, alter_specification]... ;
alter_specification:
   ADD [COLUMN] colname column_definition
  | ADD [COLUMN] (colname column_definition,...)
  | ADD [UNIQUE]{INDEX|KEY} [indexname] (index_col_name,...) [index_options]
  | ADD PRIMARY KEY (index_col_name,...) [index_options](暂不支持)
  | ALTER [COLUMN] colname {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] oldcolname newcolname column_definition
  | MODIFY [COLUMN] colname column_definition  
  | DROP [COLUMN] colname
  | DROP PRIMARY KEY (暂不支持)
  | DROP {INDEX | KEY} indexname
  | RENAME [TO] newtblname
  | ORDER BY colname (暂不支持)
  | CONVERT TO CHARACTER SET charsetname [COLLATE collationname] (暂不支持)
  | [DEFAULT] CHARACTER SET charsetname [COLLATE collationname] (暂不支持)
  | table_options
  | partition_options
  | DROP TABLEGROUP
  | AUTO_INCREMENT [=] num
column_definition:
     data_type [NOT NULL | NULL] [DEFAULT defaultvalue]
      [AUTO_INCREMENT] [UNIQUE [KEY]](暂不支持) [[PRIMARY] KEY] (暂不支持)
      [COMMENT 'string']
table_options:
     [SET] table_option [[,] table_option]...
table_option:
[DEFAULT] {CHARACTER SET | CHARSET} [=] charsetname
| [DEFAULT] COLLATE [=] collationname
| COMMENT [=] 'string'
| COMPRESSION [=] '{NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 |ZLIB_1.0}'
| EXPIRE_INFO [=] (expr)
| REPLICA_NUM [=] num
| TABLE_ID [=] id
| BLOCK_SIZE [=] size
| USE_BLOOM_FILTER [=] {True | False}
| PROGRESSIVE_MERGE_NUM [=] num
| TABLEGROUP [=] tablegroupname
| PRIMARY_ZONE [=] zone
| AUTO_INCREMENT [=] num
partition_options:
     PARTITION BY
         HASH(expr)
         | KEY(column_list)
         [PARTITIONS num]
         [partition_definition ...]
partition_definition:
     COMMENT [=] 'commenttext' (暂不支持)


增加列 ALTER TABLE tblname
    ADD [COLUMN] colname data_type
    [NOT NULL | NULL]
    [DEFAULT defaultvalue];

• data_type请参见数据类型章节

修改列属性 ALTER TABLE tblname
   ALTER [COLUMN] colname
   [SET DEFAULT literal| DROP DEFAULT];




修改列类型 ALTER TABLE tblname
    MODIFY colname column_definition;




删除列 ALTER TABLE tblname
    DROP [COLUMN] colname;

• 不允许删除主键列或者包含索引的列。

表重命名 ALTER TABLE tblname
   RENAME TO newtblname;




列重命名 ALTER TABLE tblname
    CHANGE [COLUMN] oldcolname newcolname column_definition;

注意：

1. 对于varchar类型的列，只允许将varchar的长度变大，不允许减小。
2. 在 OceanBase 0.5 里列重命名是用 ALTER TABLE tblname RENAME [COLUMN] oldcolname TO newcolname，OceanBase 1.0的实现与MySQL兼容。

例1： #把表t2的字段d改名为c，并同时修改了字段类型
ALTER TABLE t2 CHANGE COLUMN d c CHAR(10);


设置过期数据删除 ALTER TABLE tblname
    SET EXPIRE_INFO [ = ] expr;



例2： CREATE TABLE example_1(custid INT            
    , thedate TIMESTAMP
    , cost INT
    , PRIMARY KEY(custid, thedate)
    ) EXPIRE_INFO = (thedate < date_sub(merging_frozen_time(), INTERVAL 2 DAY)),
    USE_BLOOM_FILTER = FALSE;

(thedate < date_sub(merging_frozen_time(), INTERVAL 2 DAY))，表示删除（过期）thedate字段值为冻结时间2天前的数据，删除数据动作在数据合并时候真正执行。
修改过期条件 # 删除（过期）thedate字段值为1天前的数据。
alter table example_1 set EXPIRE_INFO = (thedate < date_sub(merging_frozen_time(), INTERVAL 1 DAY))


设置Partition表BLOCK大小 ALTER TABLE tblname
    SET BLOCK_SIZE [=] blocksize;




设置该表的副本数 ALTER TABLE tblname
    SET REPLICA_NUM [=] num;



这里是指表的副本总数多少。

设置该表的压缩方式 ALTER TABLE tblname
    SET COMPRESSION [=] '{NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}';




设置是否使用BloomFilter ALTER TABLE tblname
    SET USE_BLOOM_FILTER [=] {True | Flase};




设置注释信息 ALTER TABLE tblname
    SET COMMENT [=] 'commentstring';




设置渐进合并步数 ALTER TABLE tblname
  SET PROGRESSIVE_MERGE_NUM [=] num;



此功能是设置渐近合并步数，PROGRESSIVE_MERGE_NUM现在在限制是1~64。

设置表的ZONE属性 ALTER TABLE tblname
    zone_specification...;
zone_specification:
    PRIMARY_ZONE [=] zone




修改AUTO_INCREMENT字段的起始值


可在create talbe时指定AUTO_INCREMENT的起始值，也可用alter table tbl_name AUTO_INCREMENT=n命令来重设自增的起始值，但是如果设置的n比AUTO_INCREMENT字段的当前值小的话，执行的sql不会报错，但是不会生效！ Oceanbase>create table t1(id int auto_increment primary key,name varchar(10)) auto_increment=100;
Query OK, 0 rows affected (0.10 sec)
Oceanbase>alter table t1 auto_increment=50;
Query OK, 0 rows affected (0.04 sec)
#用alter table语句把t1表的auto_increment起始值调整为50,由于50小于100，修改无效
Oceanbase>show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8mb4 REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Oceanbase>alter table t1 auto_increment=110;
Query OK, 0 rows affected (0.03 sec)
Oceanbase>show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT = 110 DEFAULT CHARSET = utf8mb4 REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)