对象数据复制
📄字数 3.5K
👁️阅读量 加载中...
概述
本章介绍的对象数据复制指的是：在同一数据库复制“对象”（表）及其数据的过程，重点是包含结构与数据的复制。
对象数据复制的常见应用场景包括：
- 数据表结构优化重组：用户旧表 --> 用户新表
- 环境复制：生产环境 --> 测试/开发环境；主数据库 --> 报表专用数据库
- 数据归档：活动表 --> 历史归档表、冷热数据分级存储
对象数据复制当前支持的方式有三种：
- 创建并复制表：创建一张新表后再将查询数据复制到新表，请参阅CREATE TABLE AS SELECT
- 插入或替换复制表：通过插入或替换方式将查询数据复制到已有表，请参阅INSERT/REPLACE INTO SELECT
- 流式复制表：通过流式导入方式将查询数据复制到已有表，请参阅IMPORT SELECT
一、CREATE TABLE AS SELECT
根据查询结果复制表，可以仅复制表结构，也可同时复制表结构与表数据，使用插入方式。
1.1 语法格式
1.2 参数说明
OptTemp：指定创建的表是否为临时表及其作用范围- 未指定：创建为普通表
TEMPORARY、TEMP、LOCAL TEMPORARY、LOCAL TEMP：
均创建为局部临时表，在会话结束时自动删除结构与数据GLOBAL TEMPORARY、GLOBAL TEMP：
均创建为全局临时表，在会话结束时保留结构，仅删除数据
name_space：表名标识符，完整格式为[schema_name .]table_name，其中：schema_name：可选的模式名table_name：表名- 当省略模式名时，默认将表创建在当前模式下
ColumnName：可选的列名，未指定时为查询中的列名
1.3 示例
sql
-- 创建基础表
SQL> CREATE TABLE tab_base(id INT, str VARCHAR(20));
SQL> INSERT INTO tab_base VALUES(1, 'VALUE BASE');
-- 同时复制表结构和表数据
SQL> CREATE TABLE tab_copy1 AS SELECT * FROM tab_base;
SQL> SELECT * FROM tab_copy1;
+----+------------+
| ID | STR |
+----+------------+
| 1 | VALUE BASE |
+----+------------+
-- 仅复制一个表的结构到另一个表：
SQL> CREATE TABLE tab_copy2 AS SELECT * FROM tab_base WHERE 1=0;
SQL> SELECT * FROM tab_copy2;
+----+-----+
| ID | STR |
+----+-----+
+----+-----+1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
二、INSERT/REPLACE INTO SELECT
将查询结果插入或替换到现有的表中，需要保证现有表已创建。
2.1 语法格式
(opt_parti_clip_clause::=,SelectStmt::=)
2.2 参数说明
('INSERT' | 'REPLACE')：插入或者替换表中数据INSERT：向表中插入数据，不影响表中原有数据REPLACE：替换表中数据
name_space：表名，可带模式名也可忽略模式名columnList：可选列名，仅插入指定列，未指定则插入所有列，使用,逗号分隔
2.3 示例
sql
SQL> CREATE TABLE tab_copy3(col1 INT,col2 VARCHAR);
SQL> INSERT INTO tab_copy3 VALUES(1,'VALUE1')(2,'VALUE2');
SQL> CREATE TABLE tab_copy4(col1 INT,col2 VARCHAR);
SQL> CREATE TABLE tab_copy5(col1 INT PRIMARY KEY,col2 VARCHAR);
SQL> INSERT INTO tab_copy5 VALUES(5,'VALUE5');
SQL> CREATE TABLE tab_copy6(col1 INT,col2 VARCHAR);
-- 通过 INSERT INTO ..SELECT 复制表 tab_copy3 数据到 tab_copy4;
SQL> INSERT INTO tab_copy4 SELECT * FROM tab_copy3;
SQL> SELECT * FROM tab_copy4;
+------+--------+
| COL1 | COL2 |
+------+--------+
| 1 | VALUE1 |
| 2 | VALUE2 |
+------+--------+
-- 通过 INSERT INTO ..SELECT 复制表 tab_copy3 数据到 tab_copy5;
SQL> INSERT INTO tab_copy5(col1, col2) SELECT * FROM tab_copy3;
SQL> SELECT * FROM tab_copy5;
+------+--------+
| COL1 | COL2 |
+------+--------+
| 5 | VALUE5 |
| 1 | VALUE1 |
| 2 | VALUE2 |
+------+--------+
-- 通过 INSERT INTO ..SELECT 复制表 tab_copy3 数据到 tab_copy5;
-- 遇到唯一值约束冲突则报错[E13001] 违反唯一值约束
SQL> UPDATE tab_copy3 SET col2='VALUE22' WHERE col1=2;
SQL> INSERT INTO tab_copy5(col1, col2) SELECT * FROM tab_copy3;
Error: [E13001] 违反唯一值约束
SQL> SELECT * FROM tab_copy5;
+------+--------+
| COL1 | COL2 |
+------+--------+
| 5 | VALUE5 |
| 1 | VALUE1 |
| 2 | VALUE2 |
+------+--------+
-- 通过 REPLACE INTO ..SELECT 复制表 tab_copy3 数据到 tab_copy5;
-- 遇到唯一值约束冲突则删除原记录，否则同 insert
SQL> REPLACE INTO tab_copy5(col1, col2) SELECT * FROM tab_copy3;
SQL> SELECT * FROM tab_copy5;
+------+---------+
| COL1 | COL2 |
+------+---------+
| 5 | VALUE5 |
| 1 | VALUE1 |
| 2 | VALUE22 |
+------+---------+1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
注意
INSERT INTO遇到唯一值约束冲突时，插入数据会失败REPLACE INTO遇到唯一值约束冲突时，先将原记录删除，然后再进行插入，插入数据会成功
三、IMPORT SELECT
将数据从一个查询结果通过流式导入到现有的表中，相较于INSERT SELECT，省去反复解析SQL语句的消耗，性能更好，适用于大数据量表。
3.1 语法格式
3.2 参数说明
op_append_mode：导入模式- 未指定：结果追加到目标表中，等同于
APPEND APPEND：结果追加到目标表中，而不影响已有数据，此为默认模式REPLACE：替换表中所有数据
- 未指定：结果追加到目标表中，等同于
name_space：表名标识符，完整格式为[schema_name .]table_name，其中：schema_name：可选的模式名table_name：表名- 当省略模式名时，默认将表创建在当前模式下
columnList：列名，可选值，缺省时列名为查询中的列名，使用,逗号分隔ColumnName：列名别名
注意
columnList字段数必须和SelectStmt中的字段数保持一致IMPORT语法复制数据不支持目标表出现在查询子句中
3.3 示例
sql
-- APPEND 模式
SQL> CREATE TABLE tab_copy6(id INT,name VARCHAR(20));
SQL> INSERT INTO tab_copy6 VALUES(1,'one')(2,'two');
SQL> CREATE TABLE tab_copy7(id INT PRIMARY KEY,name VARCHAR(20));
SQL> INSERT INTO tab_copy7 VALUES(66,'abc');
SQL> IMPORT APPEND TABLE tab_copy7 FROM SELECT * FROM tab_copy6;
SQL> IMPORT APPEND TABLE tab_copy7 FROM SELECT * FROM tab_copy6;
SQL> SELECT * FROM tab_copy7;
+----+------+
| ID | NAME |
+----+------+
| 66 | abc |
| 1 | one |
| 2 | two |
| 1 | one |
| 2 | two |
+----+------+
-- REPLACE 模式
SQL> IMPORT REPLACE TABLE tab_copy7 FROM SELECT * FROM tab_copy6;
SQL> SELECT * FROM tab_copy7;
+----+------+
| ID | NAME |
+----+------+
| 1 | one |
| 2 | two |
+----+------+1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
注意
IMPORT APPEND：在目标表中追加数据，即使主键冲突也会追加成功IMPORT REPLACE：替换表中所有数据，会先删除表中原本所有数据