ALTER TABLE

功能描述

Vastbase在MySQL兼容模式下，支持ALTER TABLE的如下特性用法：

支持修改具有AUTO_INCREMENT属性的自增字段的下一个值。

关于创建自增属性的表请参考MySQL兼容模式下的SQL语法CREATE TABLE。
支持使用MODIFY子句修改表的已有字段时使用可选关键字COLUMN。
支持使用MODIFY子句修改表的已有字段时定义列的注释，修改后可通过\d+查看。
支持使用ADD COLUMN子句新增列时定义列的注释。
在ALTER TABLE ADD或者ALTER TABLE MODIFY的同时支持通过FIRST或AFTER关键字指定新增字段的位置或被修改后字段的位置。

ALTER TABLE ADD 用于向表中增加一个新的字段。ALTER TABLE MODIFY 用于修改已有字段的定义。

本章节仅介绍ALTER TABLE在MySQL兼容模式下的特性用法，更多语法及参数说明详见SQL参考中的ALTER TABLE。

注意事项

上述功能仅在数据库兼容模式为MySQL时能够使用（即创建DB时DBCOMPATIBILITY='B'），在其他数据库兼容模式下不能使用以上特性。
当前仅支持修改普通表和分区表的AUTO_INCREMENT属性，不支持物化视图，复合类型等其他类型的自增属性。
在使用MODIFY子句修改表的已有字段时使用可选关键字COLUMN，以及通过FIRST或AFTER关键字指定字段位置，上述两种MySQL特性用法仅在V2.2 Build 10(Patch No.11)及以后补丁版本中支持。
指定列注释的特性仅在V2.2 Build 10(Patch No.17)及以后补丁版本中支持。
在V2.2 Build 10(Patch No.17)及以后补丁版本中，当修改字段的COLLATE时，若指定的排序规则不存在，则设置为DEFAULT。

语法格式

修改自增字段的下一个值：
```
ALTER TABLE table_name AUTO_INCREMENT = ai_val;
```
一张表仅支持具有一个自增字段，因此修改自增字段时仅需指定表名，无需指定自增字段名，数据库会找到该字段并修改其自增属性。

新增列：

ALTER TABLE table_name ADD [ COLUMN ] column_name column_clause [ FIRST | AFTER col_name ] ;

修改表已存在字段的属性：

ALTER TABLE table_name MODIFY [COLUMN] column_name column_clause [ COLLATE collation ] [ COMMENT 'string' ][ FIRST | AFTER col_name ];

参数说明

table_name

需要修改的表名。
ai_val

修改后自增字段的下一个值。

仅当ai_val的值大于当前自增字段的最大值时，修改才真正生效。
[ COLUMN ] column_name

现存的或新字段的名称。

其中COLUMN关键字可以隐去。
column_clause

列的定义语句。支持的字段属性包括：字段的数据类型、是否可空、以及字段默认值等。

更多有关此子句的内容详见SQL参考中的ALTER TABLE。
string

列的注释语句。
FIRST | AFTER

通过FIRST或者AFTER关键字指定新增字段的位置或修改后该字段的位置，该关键字可以与其它修改动作同时指定。
- FIRST 表示修改后该字段位于所有字段之首。
- AFTER col_name 表示修改后该字段位于col_name字段之后。

示例

前置步骤

创建兼容MySQL的库db_mysql，并进入。

CREATE DATABASE db_mysql dbcompatibility='B';
\c db_mysql

示例1： 修改自增列的下一个值。

1、创建带有auto_increment字段的表并插入数据。

create table test1(a int primary key auto_increment, b int);
insert into test1(b) values(1);
insert into test1(b) values(1);
insert into test1 values(0,3);   
insert into test1 values(NULL,4);

2、查看测试表的当前数据。

select * from test1;

返回结果如下：

 a | b
---+---
 1 | 1
 2 | 1
 3 | 3
 4 | 4
(4 rows)

3、修改自增字段的下一个值为10。

alter table test1 auto_increment = 10;

4、再次插入一条数据。

insert into test1(b) values(1);

5、查看测试表当前数据。

select * from test1;

返回结果如下，其中具有自增属性的列a的下一个值被修改为10：

  a | b
----+---
  1 | 1
  2 | 1
  3 | 3
  4 | 4
 10 | 1
(5 rows)

示例2： ALTER TABLE MODIFY修改表字段时使用关键字COLUMN。

1、创建测试表。

create table test_modify (a text,b int not null);

2、查看测试表的表结构信息。

\d+ test_modify

返回结果如下：

                           Table "public.test_modify"
 Column |  Type   | Modifiers | Storage  | Stats target | Description | Attalias
--------+---------+-----------+----------+--------------+-------------+----------
 a      | text    |           | extended |              |             | a
 b      | integer | not null  | plain    |              |             | b
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

3、将字段b的数据类型由integer修改为tinyint。

alter table test_modify modify column b tinyint;

4、再次查看测试表结构。

\d+ test_modify

返回结果如下，其中b列的数据类型为integer：

                           Table "public.test_modify"
 Column |  Type   | Modifiers | Storage  | Stats target | Description | Attalias
--------+---------+-----------+----------+--------------+-------------+----------
 a      | text    |           | extended |              |             | a
 b      | tinyint | not null  | plain    |              |             | b
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

5、向测试表中插入一条数据，由于向字段b插入了一个空值，违反了非空约束，故如下语句执行失败。

insert into test_modify values(1);

报错信息如下：

ERROR:  null value in column "b" violates not-null constraint
DETAIL:  Failing row contains (1, null).

6、修改字段b的约束，允许向字段b插入空值。

alter table test_modify modify column b null;

7、向测试表中插入一条数据，向字段b中插入一个空值。

insert into test_modify values(1);

8、插入成功，查看测试表数据。

select * from test_modify;

返回结果如下：

 a | b
---+---
 1 |
(1 row)

示例3： 修改表时在ADD或MODIFY的同时指定FIRST/AFTER关键字。

1、创建测试表。

CREATE TABLE my_table_1156249 (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT
);

2、新增字段new1_column，不指定其位置。

ALTER TABLE my_table_1156249 ADD COLUMN new1_column VARCHAR(100);

3、查看测试表的表结构。

\d my_table_1156249;

返回结果如下，在不指定新增字段位置的前提下，新增字段被插入到了表结构的末尾。

                                     Table "public.my_table_1156249"
   Column    |     Type     |                           Modifiers                           |  Attalias
-------------+--------------+---------------------------------------------------------------+-------------
 id          | integer      | not null default nextval('my_table_1156249_id_seq'::regclass) | id
 name        | varchar(50)  |                                                               | name
 age         | integer      |                                                               | age
 new1_column | varchar(100) |                                                               | new1_column
Indexes:
    "my_table_1156249_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

4、插入新字段，指定其插入位置为表结构中所有字段的开头。

ALTER TABLE my_table_1156249 ADD COLUMN new2_column VARCHAR(100) FIRST;

5、查看当前测试表结构。

\d my_table_1156249;

返回结构如下，新增字段new2_column位于所有字段之首。

                                     Table "public.my_table_1156249"
   Column    |     Type     |                           Modifiers                           |  Attalias
-------------+--------------+---------------------------------------------------------------+-------------
 new2_column | varchar(10)  |                                                               | new2_column
 id          | integer      | not null default nextval('my_table_1156249_id_seq'::regclass) | id
 name        | varchar(50)  |                                                               | name
 age         | integer      |                                                               | age
 new1_column | varchar(100) |                                                               | new1_column
Indexes:
    "my_table_1156249_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

6、插入新字段，指定其位置在字段“id”之后。

ALTER TABLE my_table_1156249 ADD COLUMN new3_column VARCHAR(10) AFTER id;

7、查看当前表结构。

\d my_table_1156249;

返回结构如下，新增字段new3_column被插入到了字段“id”的后面。

                                     Table "public.my_table_1156249"
   Column    |     Type     |                           Modifiers                           |  Attalias
-------------+--------------+---------------------------------------------------------------+-------------
 new2_column | varchar(10)  |                                                               | new2_column
 id          | integer      | not null default nextval('my_table_1156249_id_seq'::regclass) | id
 new3_column | varchar(10)  |                                                               | new3_column
 name        | varchar(50)  |                                                               | name
 age         | integer      |                                                               | age
 new1_column | varchar(100) |                                                               | new1_column
Indexes:
    "my_table_1156249_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

8、修改当前已有字段new3_column，将其位置修改到age字段之后，同时将其数据类型改为VARCHAR(30)。

ALTER TABLE my_table_1156249 modify new3_column VARCHAR(30) AFTER age;

9、查看当前测试表结构。

\d my_table_1156249;

返回结果如下，new3_column字段的位置和数据类型均按预期完成了修改。

                                     Table "public.my_table_1156249"
   Column    |     Type     |                           Modifiers                           |  Attalias
-------------+--------------+---------------------------------------------------------------+-------------
 new2_column | varchar(10)  |                                                               | new2_column
 id          | integer      | not null default nextval('my_table_1156249_id_seq'::regclass) | id
 name        | varchar(50)  |                                                               | name
 age         | integer      |                                                               | age
 new3_column | varchar(30)  |                                                               | new3_column
 new1_column | varchar(100) |                                                               | new1_column
Indexes:
    "my_table_1156249_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

示例4： 修改列的排序规则为不存在的COLLATE，同时定义列的注释。

1、创建测试表。

CREATE TABLE my_table(
id INT PRIMARY KEY,
my_column text charset  utf8
);

2、查看列的排序规则。

select a.attname, b.collname from pg_attribute a inner join pg_collation b on a.attrelid=regclass('my_table') and a.attname = 'my_column' and a.attcollation=b.oid;

返回结果为：

  attname  |      collname
-----------+--------------------
 my_column | utf8mb4_general_ci
(1 row)

3、修改列的排序规则为无效值，并增加列的注释。

alter table my_table modify column my_column varchar(32) CHARACTER SET gb18030  COLLATE error  DEFAULT NULL COMMENT '测试alter comment';

返回结果为：

WARNING:  Invalid collation for column my_column detected. default value set
ALTER TABLE

4、查看列的排序规则和表结构。

select a.attname, b.collname from pg_attribute a inner join pg_collation b on a.attrelid=regclass('my_table') and a.attname = 'my_column' and a.attcollation=b.oid;
\d+ my_table

返回结果为：

  attname  | collname
-----------+----------
 my_column | default
(1 row)

                                          Table "public.my_table"
  Column   |    Type     |       Modifiers       | Storage  | Stats target |    Description    | Attalias
-----------+-------------+-----------------------+----------+--------------+-------------------+-----------
 id        | integer     | not null              | plain    |              |                   | id
 my_column | varchar(32) | default NULL::varchar | extended |              | 测试alter comment | my_column
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

5、新增列时定义注释。

alter table my_table add column add_column varchar(32) COMMENT '新增列注释';
\d+ my_table

返回结果为：

                                           Table "public.my_table"
   Column   |    Type     |       Modifiers       | Storage  | Stats target |    Description    |  Attalias
------------+-------------+-----------------------+----------+--------------+-------------------+------------
 id         | integer     | not null              | plain    |              |                   | id
 my_column  | varchar(32) | default NULL::varchar | extended |              | 测试alter comment | my_column
 add_column | varchar(32) |                       | extended |              | 新增列注释        | add_column
Indexes:
    "my_table_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no, fillfactor=80

清理环境

\c postgres
DROP DATABASE db_mysql;

VastbaseG100

ALTER TABLE

功能描述

注意事项

语法格式

参数说明

示例

弹框头部