匯入與匯出資料

1 備份與回復

在你開始使用MySQL資料庫以後，MySQL會幫你儲存與管理所有的資料，依照不同的設定，會有許多的資料檔案儲存在檔案系統中，如果這些檔案不小心遺失或損壞，儲存的資料可能就全部不見了。為了預防這類的情況發生，MySQL提供許多備份資料的功能，讓你可以依照自己的需求，匯出資料庫中儲存的資料，另外保存起來。如果資料庫發生嚴重的問題，而且儲存的資料不見了，你就可以把之前備份的資料，回復到資料庫中。備份資料的工作稱為「匯出資料、exporting data」；回復資料的工作稱為「匯入資料、importing data」。

你可以使用SQL敘述或MySQL提供的用戶端程式，執行匯出與匯入的工作。匯出資料可以使用「SELECT INTO OUTFILE」敘述，或是「mysqldump」用戶端程式，它們都可以將指定的資料儲存為檔案保存起來；匯入資料可以使用「LOAD DATA INFILE」敘述，或是「mysqlimport」用戶端程式，它們都可以將指定檔案中的資料新增到資料庫中。

2 使用SQL敘述匯出資料

MySQL提供「SELECT INTO OUTFILE」敘述匯出資料，它的用法與一般查詢敘述一樣，另外使用「INTO OUTFILE」子句指定一個檔案名稱，執行敘述以後回傳的資料會儲存為檔案。下列是它的語法：

使用「INTO OUTFILE」子句指定檔案名稱時，要特別注意資料夾的符號，不論是「UNIX」或「WINDOWS」作業系統，都要使用「\/」。下列的敘述會將查詢後的結果儲存到「C:\cmdev\dept.txt」檔案中：

使用文字編輯軟體開啟上列範例匯入的檔案，它的內容會像這樣：

MySQL預設的分隔字元使用「TAB」，你可以在匯出檔案的敘述中，使用「FIELDS TERMINATED BY」子句設定新的分隔字元：

使用「FIELDS ENCLOSED BY」子句可以設定包圍欄位資料的字元符號：

匯出的資料如果遇到「NULL」值的時候，MySQL會使用「\N」儲存在檔案中：

MySQL預設的跳脫字元符號是「\」，你可以在匯出檔案的敘述中，使用「FIELDS ESCAPED BY」子句設定新的跳脫字元符號：

使用「LINES STARTING BY」與「TERMINATED BY」子句可以設定每一列資料開始與結束字串：

使用文字儲存資料有許多不同的格式，有一種很常見的格式稱為「comma-separated values、CSV」，它的每一筆資料的結尾使用換行字元，每一個資料都使用逗號隔開，而且前後使用雙引號包圍起來。許多應用程式都認識這種資料的格式，你可以使用下列的設定輸出一個CSV格式的資料檔案：

3 使用SQL敘述匯入資料

「LOAD DATA」敘述可以匯入資料到資料庫的某個表格中，「LOAD DATA」敘述提供許多子句，可以讓你設定資料檔案、檔案的格式，或是匯入資料的處理。下列是它的語法：

3.1 指定資料檔案

「LOAD DATA」敘述可以將一個包含資料的檔案，匯入到一個指定的表格中，下列是它的基本語法：

使用「LOAD DATA」敘述匯入資料前，要明確的指定資料庫：

如果你的資料檔案放在用戶端的電腦中，在使用「LOAD DATA」敘述時要加入「LOCAL」關鍵字。指定資料檔案時，可以包含磁碟機代號、資料夾與檔案名稱：

指定的資料檔案如果沒有磁碟機代號，可是包含資料夾與檔案名稱，MySQL會使用目前工作中的磁碟機：

指定的資料檔案沒有磁碟機代號，只有資料夾與檔案名稱，可是最前面沒有資料夾符號，MySQL會使用目前工作中的資料夾：

指定的資料檔案只有檔案名稱，MySQL會使用目前工作中的資料夾：

如果你的資料檔案放在伺服器的電腦中，在使用「LOAD DATA」敘述時就不要使用「LOCAL」關鍵字。指定資料檔案時，可以包含磁碟機代號、資料夾與檔案名稱：

指定的資料檔案如果沒有磁碟機代號，可是包含資料夾與檔案名稱，MySQL會使用伺服器的磁碟機：

指定的資料檔案沒有磁碟機代號，只有資料夾與檔案名稱，可是最前面沒有資料夾符號，MySQL會使用

資料庫資料夾：

指定的資料檔案只有檔案名稱，而且在「INTO TABLE」中指定資料庫名稱，MySQL會使用資料庫資料夾的資料庫名稱：

指定的資料檔案只有檔案名稱，在執行「LOAD DATA INFILE」敘述前先使用「USE」敘述指定資料庫，而且在「INTO TABLE」中沒有指定資料庫名稱，MySQL會使用資料庫資料夾的目前使用中資料庫名稱：

註：使用「SHOW VARIABLES LIKE ‘datadir’」敘述，可以查詢MySQL資料庫伺服器使用的資料庫資料夾。

3.2 設定資料格式

如果沒有另外設定的話，使用「LOAD DATA INFILE」敘述匯入的資料檔案，MySQL會使用下列的格式：

如果你的資料檔案格式跟上列的檔案一樣的話，使用下列的計就可以匯入資料：

如果要匯入資料的檔案是「CSV」格式的話，就要使用「FIELDS」與「LINES」子句設定格式：

3.3 處理匯入的資料

如果匯入的資料檔案與表格完全對應的話，「LOAD DATA INFILE」敘述都可以把資料正確的匯入到資料庫中。可是以下列儲存在資料檔案中的部門資料來說：

因為「cmdev.dept」表格有「deptno」、「dname」與「location」三個欄位，所以執行下列的「LOAD DATA INFILE」敘述就會產生錯誤：

你可以在「LOAD DATA INFILE」敘述中，指定匯入資料的數量和欄位：

下列的「LOAD DATA INFILE」敘述指定匯入資料時會跳過第一筆，而且指定匯入的欄位只有「deptno」與「dname」兩個欄位：

你也可以在「LOAD DATA INFILE」敘述中加入使用者變數：

下列的敘述將「ename」與「job」兩個欄位的資料先轉換大寫後，再匯入到資料庫中：

3.4 索引鍵重複

在新增、修改或匯入資料到資料庫的時候，都有可能發生索引值重複的錯誤，在使用「LOAD DATA INFILE」匯入資料的時候，如果發生索引值重複的情況，你可以使用「IGNORE」或「REPLACE」來決定資料庫該作什麼處理：

以部門資料表來說，部門編號已經設定為主索引鍵，所以它是不可以重複的：

如果資料檔儲存在MySQL伺服器的電腦中，在匯入資料時沒有使用「IGNORE」或「REPLACE」，發生索引重複的情況時，資料庫會產生錯誤訊息，而且不會匯入任何資料：

資料檔儲存在MySQL伺服器的電腦中時，你可以使用「IGNORE」關鍵字忽略錯誤的資料，正確的資料還是匯入到資料庫中；使用「REPLACE」關鍵字請資料庫會幫你執行修改資料的動作：

下列的「LOAD DATA INFILE」敘述中使用「IGNORE」關鍵字匯入資料時，處理索引重複資料的效果：

下列的「LOAD DATA INFILE」敘述中使用「REPLACE」關鍵字匯入資料時，處理索引重複資料的效果：

資料檔儲存在用戶端的電腦中時，處理匯入資料發生索引重複的作法會不太一樣：

使用「REPLACE」關鍵字的時候，效果就跟資料檔儲存在MySQL伺服器的電腦中時一樣：

3.5 匯入資訊

在執行匯入資料的敘述以後，你應該會想要知道有多少資料匯入到資料庫中。如果你在「MySQL Query Browser」工具中執行「LOAD DATA INFILE」敘述的話，它會告訴你總共影響了幾筆資料，包含新增與修改：

如果你在命令提示字元中執行「LOAD DATA INFILE」敘述的話，除了影響的資料數量以外，還會告訴你比較完整的匯入資訊：

在上列的資訊中：

• Records： 表示從資料檔案中讀取的資料數量

• Deleted： 表示在發生索引重複的情況下更新資料的數量

• Skipped： 表示在發生索引重複的情況下被忽略的資料數量

• Warnings： 表示資料檔案中有問題的資料數量，例如轉換Hello字串為數值

4 使用mysqldump程式匯出資料

MySQL提供許多不同應用的工具程式，讓你可以在命令提示字元中執行，這些工具程式都是MySQL才有的，而且它們並不是SQL敘述。你可以使用「mysqldump」工具程式匯出資料。下列是它的用法：

下列是「mysqldump」工具程式的基本選項：

下列的命令為「mysqldump」加入指定資料庫伺服器、使用者帳號與資料庫名稱的相關資訊。在命令提示字元中執行下列的命令以後，會在螢幕中顯示「cmdev」資料庫的資訊：

這些選項都有兩種設定方式，以使用者帳號來說：

下列是與匯出資料相關的選項：

下列的命令使用「–result-file」指定匯出的檔案名稱。執行後儲存檔案的位置就是你執行「mysqldump」的位置，如果在「C:\/cmdev\/data\/out」資料夾下執行「mysqldump」，你就可以在「C:\/cmdev\/data\/out」資料夾下找到「cmdev.sql」檔案：

執行上列的命令以後，開啟「C:\/cmdev\/data\/out\/cmdev.sql」檔案，裡面的內容只有建立表格的敘述，並不包含儲存在表格裏面的資料紀錄。

如果想要「mysqldump」工具程式也幫你匯出資料紀錄的話，就要使用下列的作法：

「mysqldump」工具程式匯出資料紀錄檔案的格式，欄位資料間使用「TAB」隔開,每一列資料以「\N」結尾。如果要控制資料檔案格式的話，可以使用下列的選項：

5 使用mysqlimport程式匯入資料

你可以使用「mysqlimport」工具程式匯入資料。下列是它的用法：

在指定資料檔案的名稱時，要特別注意下列兩個重點：

• 資料檔案中不可以包含SQL敘述

• 檔案名稱會決定匯入資料庫中的哪個表格，MySQL會使用去除附加檔名後的名稱。例如「dept.dat」為「dept」表格；「dept.txt.dat」同樣為「dept」表格

下列是「mysqlimport」工具程式的基本選項，它們的用法與「mysqldump」工具程式一樣，其實大部份的MySQL工具程式都有這些選項：

如果你的資料檔案是下列格式的話：

下列的命令可以把資料檔案匯入到「cmdev.dept」中：

下列的選項可以設定資料檔案的格式：

下列的選項可以決定發生索引值重複的錯誤時，資料庫該作什麼處理：