数据库中如何通过创建新表来备份操作示例代码_Java

前言

数据库表备份一直是一个很常规的操作，每个人都有每个人的操作方法，这篇文章主要记录了mysql和postgresql两种数据库中通过创建新表来备份的操作。

一、mysql数据库中备份表

1、复制表结构 + 数据

-- 创建新表并复制数据
create table 备份表名 as select * from 原表名;


2、仅复制表结构

-- 只复制结构（不包含数据）
create table 备份表名 like 原表名;
-- 再插入数据（可选）
insert into 备份表名 select * from 原表名;

字段含义：

create table 备份表名 as
- create table：创建新表的 sql 关键字。
- 备份表名：要生成的新表名称（例如：orders_backup）。
- as：表示新表的内容来自后续查询结果。
select * from 原表名
- select *：选择原表的所有字段（* 是通配符）。
- from 原表名：指定要复制的原表（例如：orders）。

二、postgresql数据库中备份表

1、复制表结构 + 数据

create table 备份表名 as select * from 原表名;


2、仅复制表结构（无数据）

create table 备份表名 as select * from 原表名
limit 0; -- 仅复制结构

字段含义

limit 0
- limit：限制查询返回的记录数量。
- 0：表示不返回任何数据，因此新表只有结构，没有数据。

三、如果需要完全复制表（包括索引、约束等）,则可以通过下面方式处理：

mysql：create table 备份表名 like 原表名; + insert into 备份表名 select * from 原表名;
postgresql：create table 备份表名 (like 原表名 including all);

1. mysql: create table ... like + insert into ... select

-- 步骤1：复制表结构（包括约束和索引）
create table 备份表名 like 原表名;

-- 步骤2：复制数据
insert into 备份表名 select * from 原表名;

字段含义

create table 备份表名 like 原表名
- create table：创建新表的 sql 关键字。
- 备份表名：新表的名称（例如 orders_backup）。
- like 原表名：基于原表的结构创建新表，复制字段定义、索引、主键、外键、自增属性等，但不复制数据
insert into 备份表名 select * from 原表名
- insert into：向表中插入数据的 sql 关键字。
- select * from 原表名：从原表查询所有数据并插入到新表中。
- 功能：将原表的数据完整复制到新表。

优点

完全复制结构：包括索引、主键、自增字段等。
一致性高：适合需要保留完整表定义的场景。

缺点

分步操作：需要先创建结构，再插入数据。
锁表风险：插入大量数据时可能影响原表性能。

2. postgresql: create table ... (like ... including all)

-- 1.单步复制表结构（包括索引、约束等）
create table 备份表名 (like 原表名 including all);

-- 2：复制数据
insert into 备份表名 select * from 原表名;

字段含义

create table 备份表名
- 创建新表的标准 sql 语法。
(like 原表名 including all)
- like 原表名：基于原表的结构创建新表。
- including all：postgresql 扩展语法，表示复制原表的所有属性，包括：
  - 字段定义（名称、类型、默认值）
  - 约束（主键、外键、唯一约束）
  - 索引
  - 注释
  - 存储参数（如表空间、分区规则）
  - 其他对象（如触发器、规则）
- 若省略 including all，默认仅复制字段定义（无约束或索引）。

优点

单步完成：结构和扩展属性一并复制。
高度兼容：适合需要完全复制的生产环境。

缺点

仅限 postgresql：其他数据库不支持 including all 语法。
不复制数据：需额外执行 insert into 复制数据。

注意事项

权限要求
- 执行 create table 需要数据库的 create 权限。
- 执行 select 需要原表的读取权限。
数据一致性
- 若原表在复制过程中被修改，备份表数据可能与原表不一致。建议在事务中操作或锁定原表。
自增字段（mysql）
- like 会复制自增属性，但初始值可能与原表不同。需手动重置自增值：
```
alter table 备份表名 auto_increment = 原表当前自增值;
```
postgresql 扩展选项
- including 可细化控制复制的对象（如 including indexes、including defaults）。
- 完整选项参考：postgresql create table 文档。