SQL - OUTFILE & INFILE:資料表匯出成 TXT、將 TXT 資料匯入至資料庫(備份與復原)

2019/05/30 6,659 3 網站技術 , 伺服器 , 資料庫 , SQL

今天來整理前陣子在 🏫 學校學到的資料庫備份與復原的其中一種方式,也就是用 SQL 語法將 💽 資料庫中所有的資料表存成一個個的 TXT 純文字檔 📄,然後可以在任何一台電腦中再使用 SQL 語法將這些 TXT 檔案匯入,快速復原資料庫內所有資料表與其資料,TXT 文字檔內存放資料的格式都可以在 SQL 中做變化,例如:FIELDS TERMINATED BY ', ',這可以讓每筆資料分隔方式是用「, 」隔開,MySQL 預設是用一格 Tab 分隔。

我們統一將資料都輸出到 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads 以確保沒有寫入權限的問題,SQL 語法 OUTFILE & INFILE 其實是很高層級的指令,因為會做到寫入,所以 Windows 系統很容易讓其失效,因此選擇較為安全的目錄 📂 做示範。

這種備份與復原的方式是單純用 SQL 語法做到的,非常傳統也比較不會出錯,維護也很容易 😉,就提供給大家做參考啦!本文使用「MySQL Workbench」進行操作。本文還有借用我在學校的期末專案(finalproject),不過我是用舊的檔案做示範。

▲ 首先先用 SQL 做到備份,也就是將資料庫內所有資料表匯出成 TXT 純文字檔案。

SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/XXX.txt' FROM 資料庫名稱.資料表名稱;

語法如上,「XXX」是檔名可以自訂,後面是指定某資料庫中的哪個資料表,以下為我的示範語法:

SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.customer.txt' FROM finalproject.customer;
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.customerlist.txt' FROM finalproject.customerlist;
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.employee.txt' FROM finalproject.employee;
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.goods.txt' FROM finalproject.goods;
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.ingredient.txt' FROM finalproject.ingredient;
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.supplier.txt' FROM finalproject.suppiler;

透過六行語法就能輕鬆將資料庫內六個資料表匯出成 TXT 純文字檔囉!

▲ 實際到 📂 C:\ProgramData\MySQL\MySQL Server 8.0\Uploads 目錄中就可以看到剛剛生成的六個 TXT 純文字檔 📄,打開也可以看到資料表中完整的資料。

▲ 現在我們要運用剛剛的 TXT 純文字檔復原資料庫,為了避免電腦內已經有重複的資料庫,所以我的語法會先將舊的資料庫刪除。

DROP DATABASE IF EXISTS 資料庫名稱;
CREATE DATABASE 資料庫名稱 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE 資料庫名稱;

DROP TABLE IF EXISTS 資料表名稱;

CREATE TABLE 資料表名稱(
/* 資料表欄位資訊 */,
PRIMARY KEY (XXX)
);

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/XXX.txt' INTO TABLE 資料庫名稱.資料表名稱;

語法如上,資料庫重新建立後還要檢查資料表,重新建立資料表(包含欄位資訊),最後才能將資料匯入,請注意!欄位資訊一定要和原先的資料庫中匯出的資料表一樣唷!這樣才能復原成原先的樣子。

DROP DATABASE IF EXISTS finalproject;
CREATE DATABASE finalproject CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE finalproject;

/* 中間省略 */

DROP TABLE IF EXISTS supplier;

CREATE TABLE suppiler(
  廠商編號 VARCHAR(30)NOT NULL UNIQUE,
  廠商名稱 VARCHAR(100),
  廠商電話 VARCHAR(255),
  廠商地址 VARCHAR(255),
  PRIMARY KEY (廠商編號)
);

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/finalproject.supplier.txt' INTO TABLE finalproject.suppiler;

我的示範如上,中間幾個資料表就省略,只留「suppiler」資料表給您參考。


▲ 成功復原資料庫,打開「suppiler」資料表也和原先一模一樣,成功!👍

贊助廣告 ‧ Sponsor advertisements

留言區 / Comments

萌芽論壇