GBase 8a常见操作 – 复制一张表

本文介绍GBase 8a数据库日常操作,将表A复制一份表B,也就是备份一份。其中方案1可以复制完整表结构和数据,方案2只复制数据和部分表结构,请根据实际需求进行选择。

复制表方案1

建一张新表

通过create table B like A创建一张结构完全一样的表。 如下以 student 为模板,创建一个student_bak1的表。

gbase> show create table student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE "student" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> create table student_bak1 like student;
Query OK, 0 rows affected (Elapsed: 00:00:00.12)

gbase> show create table student_bak1;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                     |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_bak1 | CREATE TABLE "student_bak1" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

复制数据

通过inset into B select from A 方法,向新建的表复制数据。

gbase> select * from student;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.01)

gbase> insert into student_bak1 select * from student;
Query OK, 5 rows affected (Elapsed: 00:00:00.08)
Records: 5  Duplicates: 0  Warnings: 0

gbase> select * from student_bak1;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.01)

复制表方案2

通过create table B as select * from A直接复制表,但本方法只复制了数据,不会复制原始表的一些索引,备注之类的属性。

如下例子我们创建一个hash索引,然后通过create table student_bak2 as select * from student来复制表,从结果可以看到,数据完全一样,但备份表里没有原始表的索引。

所以本方案只适合复制数据,不适合复制表结构。

gbase> create index idx_sno on student(sno) using hash global;
Query OK, 0 rows affected (Elapsed: 00:00:01.27)
Records: 0  Duplicates: 0  Warnings: 0

gbase> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                           |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE "student" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL,
  KEY "idx_sno" ("sno") USING HASH GLOBAL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> create table student_bak2 as select * from student;
Query OK, 5 rows affected (Elapsed: 00:00:00.19)

gbase> select * from student_bak2;
+----------+--------+------+------+
| sno      | sname  | age  | sex  |
+----------+--------+------+------+
| 20210001 | 张三   |   18 |    1 |
| 20210002 | 李四   |   19 |    1 |
| 20210003 | 王五   |   18 |    0 |
| 20210004 | 赵六   |   18 |    0 |
| 20210099 | 张三   |   18 |    0 |
+----------+--------+------+------+
5 rows in set (Elapsed: 00:00:00.01)

gbase> show create table student_bak2;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                     |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_bak2 | CREATE TABLE "student_bak2" (
  "sno" varchar(20) DEFAULT NULL,
  "sname" varchar(20) DEFAULT NULL,
  "age" int(11) DEFAULT NULL,
  "sex" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase>

发表评论

您的电子邮箱地址不会被公开。