功能说明
执行INSERT INTO…SELECT …时,自增列不允许指定值,但是可以指定0、
NULL。
示例
示例1:自增列不指定值,自动维护。
CREATE TABLE t1(a INT AUTO_INCREMENT PRIMARY KEY,b
VARCHAR(100), c INT);
INSERT INTO t1(b,c) VALUES('a1',1),('a2',2),('a3',3),('a4',4),('a5',5);
CREATE TABLE t2(a INT AUTO_INCREMENT PRIMARY KEY,b
VARCHAR(100), c INT) DISTRIBUTED BY ('b');
gbase> select * from t1;
+----+------+------+
| a
| b
| c
|
+----+------+------+
|
2 | a1
|
1 |
|
6 | a2
|
2 |
| 10 | a3
|
3 |
| 14 | a4
|
4 |
| 18 | a5
|
5 |
+----+------+------+
5 rows in set (Elapsed: 00:00:00.03)
gbase> INSERT INTO t2(b,c) SELECT b,c FROM t1;
Query OK, 5 rows affected (Elapsed: 00:00:00.15)
Records: 5
Duplicates: 0
Warnings: 0
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
1030
gbase> SELECT * FROM t2;
+---+------+------+
| a | b
| c
|
+---+------+------+
| 3 | a2
|
2 |
| 2 | a1
|
1 |
| 6 | a5
|
5 |
| 4 | a4
|
4 |
| 1 | a3
|
3 |
+---+------+------+
5 rows in set (Elapsed: 00:00:00.02)
示例2:自增列插入0,null ,自动维护。
CREATE
TABLE
t1(a
INT
AUTO_INCREMENT
PRIMARY
KEY,b
VARCHAR(100), c INT);
INSERT INTO t1(b,c) VALUES('a1',1),('a2',2),('a3',3),('a4',4),('a5',5);
CREATE
TABLE
t2(a
INT
AUTO_INCREMENT
PRIMARY
KEY,b
VARCHAR(100), c INT) DISTRIBUTED BY ('b');
gbase> INSERT INTO t2 SELECT 0,b,c FROM t1;
Query OK, 5 rows affected (Elapsed: 00:00:00.11)
Records: 5
Duplicates: 0
Warnings: 0
gbase> SELECT * FROM t2;
+---+------+------+
| a | b
| c
|
+---+------+------+
| 3 | a2
|
2 |
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
1031
| 2 | a1
|
1 |
| 6 | a5
|
5 |
| 4 | a4
|
4 |
| 1 | a3
|
3 |
+---+------+------+
5 rows in set (Elapsed: 00:00:00.02)
gbase> INSERT INTO t2 SELECT NULL,b,c FROM t1;
Query OK, 5 rows affected (Elapsed: 00:00:00.11)
Records: 5
Duplicates: 0
Warnings: 0
gbase> SELECT * FROM t2 ORDER BY a;
+----+------+------+
| a
| b
| c
|
+----+------+------+
|
1 | a3
|
3 |
|
2 | a1
|
1 |
|
3 | a2
|
2 |
|
4 | a4
|
4 |
|
6 | a5
|
5 |
|
7 | a3
|
3 |
|
8 | a1
|
1 |
|
9 | a2
|
2 |
| 10 | a4
|
4 |
| 12 | a5
|
5 |
+----+------+------+
10 rows in set (Elapsed: 00:00:00.08)
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
1032