快捷導航

數(shù)據(jù)庫中如何通過創(chuàng)建新表來備份操作示例代碼

更新時間：2025年07月24日 10:58:24 作者：糖醋咸魚一條

數(shù)據(jù)庫備份是指在特定時間點保存數(shù)據(jù)庫中數(shù)據(jù)的過程,備份數(shù)據(jù)通常存儲在不同的物理介質(zhì)上,比如硬盤、磁帶或云端存儲設(shè)備,以防止數(shù)據(jù)丟失或損壞,這篇文章主要介紹了數(shù)據(jù)庫中如何通過創(chuàng)建新表來備份操作的相關(guān)資料,需要的朋友可以參考下

前言

數(shù)據(jù)庫表備份一直是一個很常規(guī)的操作，每個人都有每個人的操作方法，這篇文章主要記錄了MySQL和PostgreSQL兩種數(shù)據(jù)庫中通過創(chuàng)建新表來備份的操作。

一、MySQL數(shù)據(jù)庫中備份表

1、復(fù)制表結(jié)構(gòu) + 數(shù)據(jù)

-- 創(chuàng)建新表并復(fù)制數(shù)據(jù)
CREATE TABLE 備份表名 AS SELECT * FROM 原表名;


2、僅復(fù)制表結(jié)構(gòu)

-- 只復(fù)制結(jié)構(gòu)（不包含數(shù)據(jù)）
CREATE TABLE 備份表名 LIKE 原表名;
-- 再插入數(shù)據(jù)（可選）
INSERT INTO 備份表名 SELECT * FROM 原表名;

字段含義：

CREATE TABLE 備份表名 AS
- CREATE TABLE：創(chuàng)建新表的 SQL 關(guān)鍵字。
- 備份表名：要生成的新表名稱（例如：orders_backup）。
- AS：表示新表的內(nèi)容來自后續(xù)查詢結(jié)果。
SELECT * FROM 原表名
- SELECT *：選擇原表的所有字段（* 是通配符）。
- FROM 原表名：指定要復(fù)制的原表（例如：orders）。

二、PostgreSQL數(shù)據(jù)庫中備份表

1、復(fù)制表結(jié)構(gòu) + 數(shù)據(jù)

CREATE TABLE 備份表名 AS SELECT * FROM 原表名;


2、僅復(fù)制表結(jié)構(gòu)（無數(shù)據(jù)）

CREATE TABLE 備份表名 AS SELECT * FROM 原表名
LIMIT 0; -- 僅復(fù)制結(jié)構(gòu)

字段含義

LIMIT 0
- LIMIT：限制查詢返回的記錄數(shù)量。
- 0：表示不返回任何數(shù)據(jù)，因此新表只有結(jié)構(gòu)，沒有數(shù)據(jù)。

三、如果需要完全復(fù)制表（包括索引、約束等）,則可以通過下面方式處理：

MySQL：CREATE TABLE 備份表名 LIKE 原表名; + INSERT INTO 備份表名 SELECT * FROM 原表名;
PostgreSQL：CREATE TABLE 備份表名 (LIKE 原表名 INCLUDING ALL);

1. MySQL: CREATE TABLE ... LIKE + INSERT INTO ... SELECT

-- 步驟1：復(fù)制表結(jié)構(gòu)（包括約束和索引）
CREATE TABLE 備份表名 LIKE 原表名;

-- 步驟2：復(fù)制數(shù)據(jù)
INSERT INTO 備份表名 SELECT * FROM 原表名;

字段含義

CREATE TABLE 備份表名 LIKE 原表名
- CREATE TABLE：創(chuàng)建新表的 SQL 關(guān)鍵字。
- 備份表名：新表的名稱（例如 orders_backup）。
- LIKE 原表名：基于原表的結(jié)構(gòu)創(chuàng)建新表，復(fù)制字段定義、索引、主鍵、外鍵、自增屬性等，但不復(fù)制數(shù)據(jù)
INSERT INTO 備份表名 SELECT * FROM 原表名
- INSERT INTO：向表中插入數(shù)據(jù)的 SQL 關(guān)鍵字。
- SELECT * FROM 原表名：從原表查詢所有數(shù)據(jù)并插入到新表中。
- 功能：將原表的數(shù)據(jù)完整復(fù)制到新表。

優(yōu)點

完全復(fù)制結(jié)構(gòu)：包括索引、主鍵、自增字段等。
一致性高：適合需要保留完整表定義的場景。

缺點

分步操作：需要先創(chuàng)建結(jié)構(gòu)，再插入數(shù)據(jù)。
鎖表風險：插入大量數(shù)據(jù)時可能影響原表性能。

2. PostgreSQL: CREATE TABLE ... (LIKE ... INCLUDING ALL)

-- 1.單步復(fù)制表結(jié)構(gòu)（包括索引、約束等）
CREATE TABLE 備份表名 (LIKE 原表名 INCLUDING ALL);

-- 2：復(fù)制數(shù)據(jù)
INSERT INTO 備份表名 SELECT * FROM 原表名;

字段含義

CREATE TABLE 備份表名
- 創(chuàng)建新表的標準 SQL 語法。
(LIKE 原表名 INCLUDING ALL)
- LIKE 原表名：基于原表的結(jié)構(gòu)創(chuàng)建新表。
- INCLUDING ALL：PostgreSQL 擴展語法，表示復(fù)制原表的所有屬性，包括：
  - 字段定義（名稱、類型、默認值）
  - 約束（主鍵、外鍵、唯一約束）
  - 索引
  - 注釋
  - 存儲參數(shù)（如表空間、分區(qū)規(guī)則）
  - 其他對象（如觸發(fā)器、規(guī)則）
- 若省略 INCLUDING ALL，默認僅復(fù)制字段定義（無約束或索引）。

優(yōu)點

單步完成：結(jié)構(gòu)和擴展屬性一并復(fù)制。
高度兼容：適合需要完全復(fù)制的生產(chǎn)環(huán)境。

缺點

僅限 PostgreSQL：其他數(shù)據(jù)庫不支持 INCLUDING ALL 語法。
不復(fù)制數(shù)據(jù)：需額外執(zhí)行 INSERT INTO 復(fù)制數(shù)據(jù)。

注意事項

權(quán)限要求
- 執(zhí)行 CREATE TABLE 需要數(shù)據(jù)庫的 CREATE 權(quán)限。
- 執(zhí)行 SELECT 需要原表的讀取權(quán)限。
數(shù)據(jù)一致性
- 若原表在復(fù)制過程中被修改，備份表數(shù)據(jù)可能與原表不一致。建議在事務(wù)中操作或鎖定原表。
自增字段（MySQL）
- LIKE 會復(fù)制自增屬性，但初始值可能與原表不同。需手動重置自增值：
```
ALTER TABLE 備份表名 AUTO_INCREMENT = 原表當前自增值;
```
PostgreSQL 擴展選項
- INCLUDING 可細化控制復(fù)制的對象（如 INCLUDING INDEXES、INCLUDING DEFAULTS）。
- 完整選項參考：PostgreSQL CREATE TABLE 文檔。