全部产品
弹性计算 会员服务 网络 安全 移动云 数加·大数据分析及展现 数加·大数据应用 管理与监控 云通信 阿里云办公 培训与认证 智能硬件
存储与CDN 数据库 域名与网站(万网) 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网 更多

ALTER TABLE

更新时间:2017-06-07 13:26:11

该语句用于修改已存在的表的结构,比如:修改表及表属性、新增列、修改列及属性、删除列等。

格式

  1. ALTER TABLE tblname
  2. alter_specification [, alter_specification]... ;
  3. alter_specification:
  4. ADD [COLUMN] colname column_definition
  5. | ADD [COLUMN] (colname column_definition,...)
  6. | ADD [UNIQUE]{INDEX|KEY} [indexname] (index_col_name,...) [index_options]
  7. | ADD PRIMARY KEY (index_col_name,...) [index_options](暂不支持)
  8. | ALTER [COLUMN] colname {SET DEFAULT literal | DROP DEFAULT}
  9. | CHANGE [COLUMN] oldcolname newcolname column_definition
  10. | MODIFY [COLUMN] colname column_definition
  11. | DROP [COLUMN] colname
  12. | DROP PRIMARY KEY (暂不支持)
  13. | DROP {INDEX | KEY} indexname
  14. | RENAME [TO] newtblname
  15. | ORDER BY colname (暂不支持)
  16. | CONVERT TO CHARACTER SET charsetname [COLLATE collationname] (暂不支持)
  17. | [DEFAULT] CHARACTER SET charsetname [COLLATE collationname] (暂不支持)
  18. | table_options
  19. | partition_options
  20. | DROP TABLEGROUP
  21. | AUTO_INCREMENT [=] num
  22. column_definition:
  23. data_type [NOT NULL | NULL] [DEFAULT defaultvalue]
  24. [AUTO_INCREMENT] [UNIQUE [KEY]](暂不支持) [[PRIMARY] KEY] (暂不支持)
  25. [COMMENT 'string']
  26. table_options:
  27. [SET] table_option [[,] table_option]...
  28. table_option:
  29. [DEFAULT] {CHARACTER SET | CHARSET} [=] charsetname
  30. | [DEFAULT] COLLATE [=] collationname
  31. | COMMENT [=] 'string'
  32. | COMPRESSION [=] '{NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 |ZLIB_1.0}'
  33. | EXPIRE_INFO [=] (expr)
  34. | REPLICA_NUM [=] num
  35. | TABLE_ID [=] id
  36. | BLOCK_SIZE [=] size
  37. | USE_BLOOM_FILTER [=] {True | False}
  38. | PROGRESSIVE_MERGE_NUM [=] num
  39. | TABLEGROUP [=] tablegroupname
  40. | PRIMARY_ZONE [=] zone
  41. | AUTO_INCREMENT [=] num
  42. partition_options:
  43. PARTITION BY
  44. HASH(expr)
  45. | KEY(column_list)
  46. [PARTITIONS num]
  47. [partition_definition ...]
  48. partition_definition:
  49. COMMENT [=] 'commenttext' (暂不支持)

增加列

  1. ALTER TABLE tblname
  2. ADD [COLUMN] colname data_type
  3. [NOT NULL | NULL]
  4. [DEFAULT defaultvalue];

修改列属性

  1. ALTER TABLE tblname
  2. ALTER [COLUMN] colname
  3. [SET DEFAULT literal| DROP DEFAULT];

修改列类型

  1. ALTER TABLE tblname
  2. MODIFY colname column_definition;

删除列

  1. ALTER TABLE tblname
  2. DROP [COLUMN] colname;
  • 不允许删除主键列或者包含索引的列。

表重命名

  1. ALTER TABLE tblname
  2. RENAME TO newtblname;

列重命名

  1. ALTER TABLE tblname
  2. 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

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

设置过期数据删除

  1. ALTER TABLE tblname
  2. SET EXPIRE_INFO [ = ] expr;

例2

  1. CREATE TABLE example_1(custid INT
  2. , thedate TIMESTAMP
  3. , cost INT
  4. , PRIMARY KEY(custid, thedate)
  5. ) EXPIRE_INFO = (thedate < date_sub(merging_frozen_time(), INTERVAL 2 DAY)),
  6. USE_BLOOM_FILTER = FALSE;

(thedate < date_sub(merging_frozen_time(), INTERVAL 2 DAY)),表示删除(过期)thedate字段值为冻结时间2天前的数据,删除数据动作在数据合并时候真正执行。

修改过期条件

  1. # 删除(过期)thedate字段值为1天前的数据。
  2. alter table example_1 set EXPIRE_INFO = (thedate < date_sub(merging_frozen_time(), INTERVAL 1 DAY))

设置Partition表BLOCK大小

  1. ALTER TABLE tblname
  2. SET BLOCK_SIZE [=] blocksize;

设置该表的副本数

  1. ALTER TABLE tblname
  2. SET REPLICA_NUM [=] num;

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

设置该表的压缩方式

  1. ALTER TABLE tblname
  2. SET COMPRESSION [=] '{NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}';

设置是否使用BloomFilter

  1. ALTER TABLE tblname
  2. SET USE_BLOOM_FILTER [=] {True | Flase};

设置注释信息

  1. ALTER TABLE tblname
  2. SET COMMENT [=] 'commentstring';

设置渐进合并步数

  1. ALTER TABLE tblname
  2. SET PROGRESSIVE_MERGE_NUM [=] num;

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

设置表的ZONE属性

  1. ALTER TABLE tblname
  2. zone_specification...;
  3. zone_specification:
  4. PRIMARY_ZONE [=] zone

修改AUTO_INCREMENT字段的起始值

可在create talbe时指定AUTO_INCREMENT的起始值,也可用alter table tbl_name AUTO_INCREMENT=n命令来重设自增的起始值,但是如果设置的n比AUTO_INCREMENT字段的当前值小的话,执行的sql不会报错,但是不会生效!

  1. Oceanbase>create table t1(id int auto_increment primary key,name varchar(10)) auto_increment=100;
  2. Query OK, 0 rows affected (0.10 sec)
  3. Oceanbase>alter table t1 auto_increment=50;
  4. Query OK, 0 rows affected (0.04 sec)
  5. #用alter table语句把t1表的auto_increment起始值调整为50,由于50小于100,修改无效
  6. Oceanbase>show create table t1;
  7. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  8. | Table | Create Table |
  9. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  10. | t1 | CREATE TABLE `t1` (
  11. `id` int(11) NOT NULL AUTO_INCREMENT,
  12. `name` varchar(10) DEFAULT NULL,
  13. PRIMARY KEY (`id`)
  14. ) AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8mb4 REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE |
  15. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  16. Oceanbase>alter table t1 auto_increment=110;
  17. Query OK, 0 rows affected (0.03 sec)
  18. Oceanbase>show create table t1;
  19. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  20. | Table | Create Table |
  21. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  22. | t1 | CREATE TABLE `t1` (
  23. `id` int(11) NOT NULL AUTO_INCREMENT,
  24. `name` varchar(10) DEFAULT NULL,
  25. PRIMARY KEY (`id`)
  26. ) AUTO_INCREMENT = 110 DEFAULT CHARSET = utf8mb4 REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE |
  27. +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  28. 1 row in set (0.01 sec)
本文导读目录