Create table as select 创建表,不能自动从源表继承自增属性,必须在create 语句 中指定列的相关属性才可以,如以下两种情况可以继承自增列属性: create 表时定义自增列,源表中不指定自增列对应的列。 create table td(a int auto_increment primary key,b int ,c int) as select xxx as b from ts; create 表时定义自增列,源表中对应列也是自增列且源表自增列的数据类型 范围小于等于待建表的自增列的范围。 create table td(a int auto_increment primary key,b int ,c int) as SELECT xxx as a, yyy as b from ts; 其中ts 表中的xxx 列为自增列。 示例 示例1:源表不指定自增列对应的列 gbase> CREATE TABLE ts (a1 int ,b1 int ,c1 int) ;
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1019 Query OK, 0 rows affected (Elapsed: 00:00:00.01) gbase> INSERT INTO ts values(1,2,3),(7,8,9); Query OK, 2 rows affected (Elapsed: 00:00:00.01) Records: 2 Duplicates: 0 Warnings: 0 Total: 2 gbase> CREATE TABLE td(a int auto_increment primary key,b int ,c int) as SELECT b1 as b FROM ts; Query OK, 2 rows affected (Elapsed: 00:00:00.02) Records: 2 Duplicates: 0 Warnings: 0 gbase> SHOW CREATE TABLE td \G *************************** 1. row *************************** Table: td Create Table: CREATE TABLE "td" ( "a" int(11) NOT NULL AUTO_INCREMENT, "c" int(11) DEFAULT NULL, "b" int(11) DEFAULT NULL, PRIMARY KEY ("a") ) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' 1 row in set (Elapsed: 00:00:00.00) 示例2:源表中指定自增列对应的列,且源表对应列为自增列。 gbase> CREATE TABLE ts (a1 int auto_increment primary key,b1 int ,c1 int) ; Query OK, 0 rows affected (Elapsed: 00:00:00.10) gbase> INSERT INTO ts(b1,c1) values(2,3),(8,9);
GBase 8a MPP Cluster 产品手册 5 数据库管理指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 1020 Query OK, 2 rows affected (Elapsed: 00:00:00.11) Records: 2 Duplicates: 0 Warnings: 0 gbase> CREATE TABLE td(a int auto_increment primary key,b int ,c int) as SELECT a1 as a, b1 as b FROM ts; Query OK, 2 rows affected (Elapsed: 00:00:00.25) gbase> SHOW CREATE TABLE td \G *************************** 1. row *************************** Table: td Create Table: CREATE TABLE "td" ( "c" int(11) DEFAULT NULL, "a" int(11) NOT NULL AUTO_INCREMENT, "b" int(11) DEFAULT NULL, PRIMARY KEY ("a") ) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' 1 row in set (Elapsed: 00:00:00.00) gbase> SELECT * FROM td; +------+---+------+ | c | a | b | +------+---+------+ | NULL | 1 | 2 | | NULL | 3 | 8 | +------+---+------+ 2 rows in set (Elapsed: 00:00:00.04)