前言

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

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

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ù)庫(kù)中備份表

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)點(diǎn)

• 完全復(fù)制結(jié)構(gòu)：包括索引、主鍵、自增字段等。

• 一致性高：適合需要保留完整表定義的場(chǎng)景。

缺點(diǎn)

• 分步操作：需要先創(chuàng)建結(jié)構(gòu)，再插入數(shù)據(jù)。

• 鎖表風(fēng)險(xiǎn)：插入大量數(shù)據(jù)時(shí)可能影響原表性能。

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)建新表的標(biāo)準(zhǔn) SQL 語(yǔ)法。

• (LIKE 原表名 INCLUDING ALL)

  • LIKE 原表名：基于原表的結(jié)構(gòu)創(chuàng)建新表。

  • INCLUDING ALL：PostgreSQL 擴(kuò)展語(yǔ)法，表示復(fù)制原表的所有屬性，包括：

    • 字段定義（名稱、類型、默認(rèn)值）

    • 約束（主鍵、外鍵、唯一約束）

    • 索引

    • 注釋

    • 存儲(chǔ)參數(shù)（如表空間、分區(qū)規(guī)則）

    • 其他對(duì)象（如觸發(fā)器、規(guī)則）

  • 若省略 INCLUDING ALL，默認(rèn)僅復(fù)制字段定義（無約束或索引）。

優(yōu)點(diǎn)

• 單步完成：結(jié)構(gòu)和擴(kuò)展屬性一并復(fù)制。

• 高度兼容：適合需要完全復(fù)制的生產(chǎn)環(huán)境。

缺點(diǎn)

• 僅限 PostgreSQL：其他數(shù)據(jù)庫(kù)不支持 INCLUDING ALL 語(yǔ)法。

• 不復(fù)制數(shù)據(jù)：需額外執(zhí)行 INSERT INTO 復(fù)制數(shù)據(jù)。

注意事項(xiàng)

1. 權(quán)限要求

   • 執(zhí)行 CREATE TABLE 需要數(shù)據(jù)庫(kù)的 CREATE 權(quán)限。

   • 執(zhí)行 SELECT 需要原表的讀取權(quán)限。

2. 數(shù)據(jù)一致性

   • 若原表在復(fù)制過程中被修改，備份表數(shù)據(jù)可能與原表不一致。建議在事務(wù)中操作或鎖定原表。

3. 自增字段（MySQL）

   • LIKE 會(huì)復(fù)制自增屬性，但初始值可能與原表不同。需手動(dòng)重置自增值：

     ALTER TABLE 備份表名 AUTO_INCREMENT = 原表當(dāng)前自增值;

4. PostgreSQL 擴(kuò)展選項(xiàng)

   • INCLUDING 可細(xì)化控制復(fù)制的對(duì)象（如 INCLUDING INDEXES、INCLUDING DEFAULTS）。

   • 完整選項(xiàng)參考：PostgreSQL CREATE TABLE 文檔。

總結(jié) 

到此這篇關(guān)于數(shù)據(jù)庫(kù)中如何通過創(chuàng)建新表來備份操作的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫(kù)創(chuàng)建新表備份內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論