CREATE TABLE

功能描述

在当前数据库中创建一个新的空白表，该表由命令执行者所有。

该文档只介绍CREATE TABLE语句兼容MySQL的特性，原Vastbase的CREATE TABLE语法未做删除和修改，详见CREATE TABLE。

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

支持指定COMMENT选项为表添加注释。
支持指定AUTO_INCREMENT属性，用于生成自增列。
支持在建表时指定存储引擎和字符集，该功能仅做语法兼容，不实现功能。
支持在建表时指定表级校对规则。
支持在建表时使用列约束ON UPDATE特性，该特性用于：执行UPDATE操作TIMESTAMP字段为缺省时，则自动更新TIMESTAMP字段的时间戳。如果更新字段的数据内容与原来的数据内容一致，则其他含有ON UPDATE字段的时间戳不会自动更新。
- 当INSERT数据时，如果该字段没有指定数值，则当前的TIMESTAMP将作为字段的缺省值插入。
- 当表中对应的行记录被UPDATE时，该字段会自动更新为CURRENT_TIMESTAMP时间戳。
支持create table指定关键字key和index创建普通索引，包括单列索引和复合索引。

注意事项

该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。
使用AUTO_INCREMENT（自增列）语法时需注意：
- 一张表只允许存在一个AUTO_INCREMENT字段。
- AUTO_INCREMENT字段必须是主键字段或联合主键字段之一。
- 对表插入数据时，若指定了AUTO_INCREMENT字段的值，且该值大于表中该自增列的最大值时，需要更新自增列的下一个值为当前插入数据值加1。
- 支持在含有自增列的表中INSERT多行数据；自增列可以插入负值或更新负值。
- 对表插入数据时，若指定AUTO_INCREMENT字段为NULL或0，则依旧触发自增机制。
- 在MySQL兼容模式下，serial类型字段与AUTO_INCREMENT类型字段特性一致。
- 自增列的值被更新后无法回退，即使其所在的事务后续回滚依旧如此。多个session并发插入时应保证可用性，需要慎重考虑插入操作导致自增值更新所带来的后果。

语法格式

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
  ( { column_name data_type [ CHARACTER SET | CHARSET charset ]
    [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option [...] ] }
    [, ... ])
    [ AUTO_INCREMENT [ = ] value ]
    [ ENGINE [=] engine_name ]
    [ [ DEFAULT ] CHARACTER SET | CHARSET [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]
    [ INHERITS ( parent_table [, ... ] ) ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ]
    [ COLLATE [=] collation_name ];
    [ COMMENT {=| } 'text' ];

其中列约束column_constraint为：

[ CONSTRAINT [ constraint_name ] ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) [STORED] |
  AUTO_INCREMENT |
  ON UPDATE update_expr |
  GENERATED BY DEFAULT [ON NULL] AS IDENTITY [(sequence_options[,...])]|
  UNIQUE [KEY] index_parameters |
  PRIMARY KEY index_parameters |
  ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CREATE TABLE支持 key | index选项语法：
```
CREATE TABLE table_name([key|index] column_name data_type...)
...
```
该功能的详细用法请参考示例6。

支持该功能后如果要使用key和index作为列名，需要使用双引号引用，无法直接使用。

本文档仅列出部分语法，更多语法请参考CREATE TABLE。

参数说明

IF NOT EXISTS

如果已经存在相同名称的表，不会报出错误，而会发出通知，告知通知此表已存在。
AUTO_INCREMENT

该关键字将字段指定为自增列。

若在插入时不指定此列的值（或指定此列的值为0、NULL、DEFAULT），此列的值将由自增计数器自动增长得到。

若插入或更新此列为一个大于当前自增计数器的值，执行成功后，自增计数器将刷新为此值。

自增初始值由AUTO_INCREMENT [ = ] value子句设置，若不设置，默认为1。
COMMENT {=|} 'text'

为创建的表指定注释内容。“=”可省略。
collate_name

表的校对规则。可用的规则可以在系统表PG_COLLATION中查询，默认的排序规则为查询结果中以default开始的行。
[DEFAULT] { CHARSET | CHARACTER SET } [=] charset_name

该参数用于选择表所使用的字符集。目前该特性仅有语法支持，不实现功能。

CHARSET支持使用单引号，例如CHARSET='utf8'。
ENGINE [=] engine_name

该参数用于指定存储引擎。
column_constraint

列约束。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。列约束作为一个列中定义的一部分，仅影响该列。
ON UPDATE update_expr

对于字段数据类型TIMESTAMP来说，update_expr使用CURRENT_TIMESTAMP作为缺省值。
key | index

指定该选项则会在创建表时创建索引，key与index含义相同。

例如：

指定该选项语句如下所示：
```
create table t01( col1 xxx,col2 xxx,index key1(col1));
```
效果等同于：
```
create table t01 ( col1 xxx, col2 xxx);
create index ind_t01 on t01 (col1);
```

本文档仅介绍新增关键字含义，更多参数说明请参考CREATE TABLE参数说明。

示例

示例1： 使用COMMENT选项为表添加注释。

1、创建测试表，并为测试表增加comment。

CREATE TABLE mytab(id int,col text) COMMENT = '测试ABC123！@#￥';

2、查询表oid。

select oid from pg_class where relname='mytab';

查询结果为如下：

  oid
-------
 18937
(1 row)

3、查询到的oid填入object_oid中，查询表的注释（ID以上一步骤查询到的18937为例）。

select obj_Description( 18937,'pg_class');

查询结果为：

 obj_description
------------------
 测试ABC123！@#￥
(1 row)

obj_Description函数为系统函数，用于获取一个数据库对象的注释。

示例2： 使用AUTO_INCREMENT自增列。

1、创建测试表。

create table test1(a int primary key auto_increment, b int);

2、插入数据进行测试。

insert into test1(b) values(1);
insert into test1(b) values(1);
insert into test1 values(0,3);   --自增
insert into test1 values(NULL,4);  --自增
insert into test1 values(10000,5);  --此处改变自增列的下一个值为10001
insert into test1(b) values(6);
insert into test1 values(10,7);  --此处由于10较小，不改变序列下一个值
insert into test1(b) values(8);

4、查询结果。

select * from test1;

查询结果为：

   a   | b
-------+---
     1 | 1
     2 | 1
     3 | 3
     4 | 4
 10000 | 5
 10001 | 6
    10 | 7
 10002 | 8
(8 rows)

示例3： 在建表时指定表级校对规则。

1、查看数据库的pad_attribute参数。

show pad_attribute;

返回结果为：

 pad_attribute
---------------
 NO PAD
(1 row)

支持初始化数据库时指定pad_attribute以及创建数据库时指定pad_attribute的功能，以此来为新数据库设置默认的列校验属性，可选取值包括：

N,NO PAD：默认值。把字符串尾端的空格当作一个字符处理，即字符串等值比较不忽略尾端空格。

S,PAD SPACE：字符串等值比较忽略尾端空格。

2、创建具有各种字符串类型的测试表，并为每一列指定不同的列校对规则，指定表级别COLLATE为”C”。

其中列c2、c4、c6的校对规则为*-space，即忽略空格匹配。c7未指定列校对规则，使用建库时的默认校对规则，本示例建库时未指定，默认为NO PAD。

create table tb_1132414_01(
c1 text COLLATE "C",
c2 character varying(30) COLLATE "C-space",
c3 varchar(30) COLLATE "zh_CN",
c4 varchar2(30) COLLATE "zh_CN-space",
c5 nvarchar2(30) COLLATE "en_US",
c6 clob COLLATE "en_US-space",
c7 varchar(30)) COLLATE "C";

3、插入测试数据。（第一次插入的字符串尾端无空格，第二次插入的字符串尾端存在空格。）

insert into tb_1132414_01 values('qqq', 'cqvjj11', 'h2vu2hb3t', 'f4rh535', 'fr2h5fj3c', 'r2hfhcg', 'r2cgcr');
insert into tb_1132414_01 values('qqq ', 'cqvjj11 ', 'h2vu2hb3t ', 'f4rh535 ', 'fr2h5fj3c ', 'r2hfhcg ', 'r2cgcr ');

4、查看是否正确识别空格。

select c1='qqq', c2='cqvjj11', c3='h2vu2hb3t', c4='f4rh535', c5='fr2h5fj3c', c6='r2hfhcg', c7='r2cgcr' from tb_1132414_01;

返回结果如下：对于插入的第二条尾部带空格的数据，c2、c4、c6可以忽略空格，返回true，其他返回false。

 ?column? | ?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------+----------
 t        | t        | t        | t        | t        | t        | t
 f        | t        | f        | t        | f        | t        | f
(2 rows)

示例4： 在建表时指定存储引擎和字符集。

1、创建一张测试表设置ENGINE和 CHARSET。

create table tab_1131500 (id int, val1 int, val2 text) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、给表中插入数据。

insert into tab_1131500 values(1,1,1);

3、更新表中数据。

update tab_1131500 set id =2 where id =1 ;

4、查询数据。

select * from tab_1131500;

返回结果为：

 id | val1 | val2
----+------+------
  2 |    1 | 1
(1 row)

示例5： 在建表时指定ON UPDATE属性。

1、创建测试表并插入数据。表中使用CREATE_TIME和UPDATE_TIME字段记录表的创建时间和字段的更新时间。

create table test (
   ID bigint NOT NULL,
   CID bigint,
   CREATE_TIME timestamp NULL DEFAULT CURRENT_TIMESTAMP,
   UPDATE_TIME timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (ID) );

2、向表中插入一条数据。

insert into test(id, cid) values(1001, 1001);

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

select * from test;

返回结果如下，INSERT时没有为CREATE_TIME和UPDATE_TIME字段指定数值，所以当前的TIMESTAMP作为数值被插入。

  ID  | CID  |        CREATE_TIME         |        UPDATE_TIME
------+------+----------------------------+----------------------------
 1001 | 1001 | 2023-05-28 00:24:09.530599 | 2023-05-28 00:24:09.530599
(1 row)

4、再次插入一条数据。

insert into test(id, cid) values(1002, 1002);

5、查看此时测试表内容。

select * from test;

返回结果如下：

  ID  | CID  |        CREATE_TIME         |        UPDATE_TIME
------+------+----------------------------+----------------------------
 1001 | 1001 | 2023-05-28 00:24:09.530599 | 2023-05-28 00:24:09.530599
 1002 | 1002 | 2023-05-28 00:24:28.103519 | 2023-05-28 00:24:28.103519
(2 rows)

6、更新表中的记录，对于id为1002的记录，将cid改为1003。

update test set cid=1003 where id=1002;

7、查看测试表数据。

select * from test;

返回结果如下，执行UPDATE操作后，对应记录的UPDATE_TIME字段已经更新为执行UPDATE操作的时间戳。

  ID  | CID  |        CREATE_TIME         |        UPDATE_TIME
------+------+----------------------------+----------------------------
 1001 | 1001 | 2023-05-28 00:24:09.530599 | 2023-05-28 00:24:09.530599
 1002 | 1003 | 2023-05-28 00:24:28.103519 | 2023-05-28 00:24:50.624596
(2 rows)

示例6： create table使用key选项创建单列索引。

1、创建测试表和单列索引。

create table table_1158986 (id int ,c1 int, c2 text, c3 varchar,key key_table_1158986(c1) );

2、向表中插入数据。

insert into table_1158986 values(1,1,'$%^&&测试','a');
insert into table_1158986 values(2,22,'asjdkasjsad','a');
insert into table_1158986 values(3,3,'123','a');

3、查看表结构。

\d table_1158986;