**目錄**
[TOC]
# 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”子句指定一個檔案名稱,執行敘述以后回傳的資料會儲存為檔案。下列是它的語法:
[](http://box.kancloud.cn/2015-07-17_55a91b04e52d1.png)
使用“INTO OUTFILE”子句指定檔案名稱時,要特別注意資料夾的符號,不論是“UNIX”或“WINDOWS”作業系統,都要使用“/”。下列的敘述會將查詢后的結果儲存到“C:\cmdev\dept.txt”檔案中:
[](http://box.kancloud.cn/2015-07-17_55a91b1accc2d.png)
使用文字編輯軟件開啟上列范例匯入的檔案,它的內容會像這樣:
[](http://box.kancloud.cn/2015-07-17_55a91b1b6c004.png)
MySQL默認的分隔字符使用“TAB”,你可以在匯出檔案的敘述中,使用“FIELDS TERMINATED BY”子句設定新的分隔字符:
[](http://box.kancloud.cn/2015-07-17_55a91b26b2cfb.png)
使用“FIELDS ENCLOSED BY”子句可以設定包圍字段資料的字符符號:
[](http://box.kancloud.cn/2015-07-17_55a91b2872cf0.png)
匯出的資料如果遇到“NULL”值的時候,MySQL會使用“\N”儲存在檔案中:
[](http://box.kancloud.cn/2015-07-17_55a91b2a92068.png)
MySQL默認的跳脫字符符號是“\”,你可以在匯出檔案的敘述中,使用“FIELDS ESCAPED BY”子句設定新的跳脫字符符號:
[](http://box.kancloud.cn/2015-07-17_55a91b2b7c035.png)
使用“LINES STARTING BY”與“TERMINATED BY”子句可以設定每一列資料開始與結束字串:
[](http://box.kancloud.cn/2015-07-17_55a91b2fb26a6.png)
使用文字儲存資料有許多不同的格式,有一種很常見的格式稱為“comma-separated values、CSV”,它的每一筆資料的結尾使用換行字符,每一個資料都使用逗號隔開,而且前后使用雙引號包圍起來。許多應用程式都認識這種資料的格式,你可以使用下列的設定輸出一個CSV格式的資料檔案:
[](http://box.kancloud.cn/2015-07-17_55a91b32f26ad.png)
# 3 使用SQL敘述匯入資料
“LOAD DATA”敘述可以匯入資料到數據庫的某個表格中,“LOAD DATA”敘述提供許多子句,可以讓你設定資料檔案、檔案的格式,或是匯入資料的處理。下列是它的語法:
[](http://box.kancloud.cn/2015-07-17_55a91b346b124.png)
## 3.1 指定資料檔案
“LOAD DATA”敘述可以將一個包含資料的檔案,匯入到一個指定的表格中,下列是它的基本語法:
[](http://box.kancloud.cn/2015-07-17_55a91b3934d01.png)
使用“LOAD DATA”敘述匯入資料前,要明確的指定數據庫:
[](http://box.kancloud.cn/2015-07-17_55a91b444aa31.png)
如果你的資料檔案放在用戶端的電腦中,在使用“LOAD DATA”敘述時要加入“LOCAL”關鍵字。指定資料檔案時,可以包含磁盤機代號、資料夾與檔案名稱:
[](http://box.kancloud.cn/2015-07-17_55a91b45530bd.png)
指定的資料檔案如果沒有磁盤機代號,可是包含資料夾與檔案名稱,MySQL會使用目前工作中的磁盤機:
[](http://box.kancloud.cn/2015-07-17_55a91b46ead19.png)
指定的資料檔案沒有磁盤機代號,只有資料夾與檔案名稱,可是最前面沒有資料夾符號,MySQL會使用目前工作中的資料夾:
[](http://box.kancloud.cn/2015-07-17_55a91b478f986.png)
指定的資料檔案只有檔案名稱,MySQL會使用目前工作中的資料夾:
[](http://box.kancloud.cn/2015-07-17_55a91b4c78c2a.png)
如果你的資料檔案放在服務器的電腦中,在使用“LOAD DATA”敘述時就不要使用“LOCAL”關鍵字。指定資料檔案時,可以包含磁盤機代號、資料夾與檔案名稱:
[](http://box.kancloud.cn/2015-07-17_55a91b57a2e1e.png)
指定的資料檔案如果沒有磁盤機代號,可是包含資料夾與檔案名稱,MySQL會使用服務器的磁盤機:
[](http://box.kancloud.cn/2015-07-17_55a91b5b211d6.png)
指定的資料檔案沒有磁盤機代號,只有資料夾與檔案名稱,可是最前面沒有資料夾符號,MySQL會使用??
數據庫資料夾:
[](http://box.kancloud.cn/2015-07-17_55a91b7524eca.png)
指定的資料檔案只有檔案名稱,而且在“INTO TABLE”中指定數據庫名稱,MySQL會使用數據庫資料夾的數據庫名稱:
[](http://box.kancloud.cn/2015-07-17_55a91b8071f79.png)
指定的資料檔案只有檔案名稱,在執行“LOAD DATA INFILE”敘述前先使用“USE”敘述指定數據庫,而且在“INTO TABLE”中沒有指定數據庫名稱,MySQL會使用數據庫資料夾的目前使用中數據庫名稱:
[](http://box.kancloud.cn/2015-07-17_55a91b838f5c2.png)
注:使用“SHOW VARIABLES LIKE ‘datadir’”敘述,可以查詢MySQL數據庫服務器使用的數據庫資料夾。
## 3.2 設定資料格式
如果沒有另外設定的話,使用“LOAD DATA INFILE”敘述匯入的資料檔案,MySQL會使用下列的格式:
[](http://box.kancloud.cn/2015-07-17_55a91b8599e7e.png)
如果你的資料檔案格式跟上列的檔案一樣的話,使用下列的計就可以匯入資料:
[](http://box.kancloud.cn/2015-07-17_55a91b878e4e9.png)
如果要匯入資料的檔案是“CSV”格式的話,就要使用“FIELDS”與“LINES”子句設定格式:
[](http://box.kancloud.cn/2015-07-17_55a91b88dd326.png)
## 3.3 處理匯入的資料
如果匯入的資料檔案與表格完全對應的話,“LOAD DATA INFILE”敘述都可以把資料正確的匯入到數據庫中。可是以下列儲存在資料檔案中的部門資料來說:
[](http://box.kancloud.cn/2015-07-17_55a91b8b88252.png)
因為“cmdev.dept”表格有“deptno”、“dname”與“location”三個字段,所以執行下列的“LOAD DATA INFILE”敘述就會產生錯誤:
[](http://box.kancloud.cn/2015-07-17_55a91b8c13318.png)
你可以在“LOAD DATA INFILE”敘述中,指定匯入資料的數量和字段:
[](http://box.kancloud.cn/2015-07-17_55a91b8c910b5.png)
下列的“LOAD DATA INFILE”敘述指定匯入資料時會跳過第一筆,而且指定匯入的字段只有“deptno”與“dname”兩個字段:
[](http://box.kancloud.cn/2015-07-17_55a91b8ee9d6c.png)
你也可以在“LOAD DATA INFILE”敘述中加入使用者變量:
[](http://box.kancloud.cn/2015-07-17_55a91b904d528.png)
下列的敘述將“ename”與“job”兩個字段的資料先轉換大寫后,再匯入到數據庫中:
[](http://box.kancloud.cn/2015-07-17_55a91b90c1657.png)
## 3.4 索引鍵重復
在新增、修改或匯入資料到數據庫的時候,都有可能發生索引值重復的錯誤,在使用“LOAD DATA INFILE”匯入資料的時候,如果發生索引值重復的情況,你可以使用“IGNORE”或“REPLACE”來決定數據庫該作什么處理:
[](http://box.kancloud.cn/2015-07-17_55a91b923155e.png)
以部門資料表來說,部門編號已經設定為主索引鍵,所以它是不可以重復的:
[](http://box.kancloud.cn/2015-07-17_55a91b98f0efb.png)
如果資料檔儲存在MySQL服務器的電腦中,在匯入資料時沒有使用“IGNORE”或“REPLACE”,發生索引重復的情況時,數據庫會產生錯誤訊息,而且不會匯入任何資料:
[](http://box.kancloud.cn/2015-07-17_55a91b9ae4b89.png)
資料檔儲存在MySQL服務器的電腦中時,你可以使用“IGNORE”關鍵字忽略錯誤的資料,正確的資料還是匯入到數據庫中;使用“REPLACE”關鍵字請數據庫會幫你執行修改資料的動作:
[](http://box.kancloud.cn/2015-07-17_55a91bb172bb0.png)
下列的“LOAD DATA INFILE”敘述中使用“IGNORE”關鍵字匯入資料時,處理索引重復資料的效果:
[](http://box.kancloud.cn/2015-07-17_55a91bb3c178e.png)
下列的“LOAD DATA INFILE”敘述中使用“REPLACE”關鍵字匯入資料時,處理索引重復資料的效果:
[](http://box.kancloud.cn/2015-07-17_55a91bb5b7e7b.png)
資料檔儲存在用戶端的電腦中時,處理匯入資料發生索引重復的作法會不太一樣:
[](http://box.kancloud.cn/2015-07-17_55a91bb9a098b.png)
使用“REPLACE”關鍵字的時候,效果就跟資料檔儲存在MySQL服務器的電腦中時一樣:
[](http://box.kancloud.cn/2015-07-17_55a91bbf5d039.png)
## 3.5 匯入資訊
在執行匯入資料的敘述以后,你應該會想要知道有多少資料匯入到數據庫中。如果你在“MySQL Query Browser”工具中執行“LOAD DATA INFILE”敘述的話,它會告訴你總共影響了幾筆資料,包含新增與修改:
[](http://box.kancloud.cn/2015-07-17_55a91bc037d33.png)
如果你在命令提示字符中執行“LOAD DATA INFILE”敘述的話,除了影響的資料數量以外,還會告訴你比較完整的匯入資訊:
[](http://box.kancloud.cn/2015-07-17_55a91bc16a9ce.png)
在上列的資訊中:
* Records:表示從資料檔案中讀取的資料數量
* Deleted:表示在發生索引重復的情況下更新資料的數量
* Skipped:表示在發生索引重復的情況下被忽略的資料數量
* Warnings:表示資料檔案中有問題的資料數量,例如轉換Hello字串為數值
# 4 使用mysqldump程式匯出資料
MySQL提供許多不同應用的工具程式,讓你可以在命令提示字符中執行,這些工具程式都是MySQL才有的,而且它們并不是SQL敘述。你可以使用“mysqldump”工具程式匯出資料。下列是它的用法:
[](http://box.kancloud.cn/2015-07-17_55a91bc2d1736.png)
下列是“mysqldump”工具程式的基本選項:
| 選項 | 說明 |
| --- | --- |
| –host=數據庫服務器 或 -h 數據庫服務器 | 指定要連線的的數據庫服務器名稱,“-h”后面必須有空格;沒有使用這個選項的話,表示連線到本機 |
| –user=使用者帳號 或 -u 使用者帳號 | 指定連線的使用者帳號,“-u”后面必須有空格 |
| –password[=密碼] 或 -p[密碼] | 指定連線的密碼,“-p”后面不可以有空格;沒有提供密碼的話,執行程式以后會提示你輸入密碼;沒有使用這個選項的話,表示密碼為空白 |
下列的命令為“mysqldump”加入指定數據庫服務器、使用者帳號與數據庫名稱的相關資訊。在命令提示字符中執行下列的命令以后,會在螢幕中顯示“cmdev”數據庫的資訊:
[](http://box.kancloud.cn/2015-07-17_55a91bc56660f.png)
這些選項都有兩種設定方式,以使用者帳號來說:
[](http://box.kancloud.cn/2015-07-17_55a91bc8154fe.png)
下列是與匯出資料相關的選項:
| 選項 | 說明 |
| --- | --- |
| –result-file=檔案名稱 | 指定匯出資料的檔案名稱,資料夾符號必須使用“/” |
| –all-databases | 匯出數據庫服務器中所有數據庫的資料 |
| –tab=資料夾 | 指定匯出資料檔案存放的資料夾 |
下列的命令使用“–result-file”指定匯出的檔案名稱。執行后儲存盤案的位置就是你執行“mysqldump”的位置,如果在“C:/cmdev/data/out”資料夾下執行“mysqldump”,你就可以在“C:/cmdev/data/out”資料夾下找到“cmdev.sql”檔案:
[](http://box.kancloud.cn/2015-07-17_55a91bc8c344b.png)
執行上列的命令以后,開啟“C:/cmdev/data/out/cmdev.sql”檔案,里面的內容只有建立表格的敘述,并不包含儲存在表格里面的資料紀錄。
如果想要“mysqldump”工具程式也幫你匯出資料紀錄的話,就要使用下列的作法:
[](http://box.kancloud.cn/2015-07-17_55a91bca51bea.png)
“mysqldump”工具程式匯出資料紀錄檔案的格式,字段資料間使用“TAB”隔開,每一列資料以“\N”結尾。如果要控制資料檔案格式的話,可以使用下列的選項:
| 選項 | 說明 |
| --- | --- |
| –fields-terminated-by=字串 | 設定字段資料間的分隔符號 |
| –fields-enclosed-by=字符 | 設定每一個字段資料的前后字符 |
| –fields-optionally-enclosed-by=字符 |
| –fields-escaped-by=字符 | 設定跳脫字符的符號 |
| –lines-terminated-by=字串 | 設定每一行的結尾 |
# 5 使用mysqlimport程式匯入資料
你可以使用“mysqlimport”工具程式匯入資料。下列是它的用法:
[](http://box.kancloud.cn/2015-07-17_55a91bcc3e2fb.png)
在指定資料檔案的名稱時,要特別注意下列兩個重點:
* 資料檔案中不可以包含SQL敘述
* 檔案名稱會決定匯入數據庫中的哪個表格,MySQL會使用去除附加檔名后的名稱。例如“dept.dat”為“dept”表格;“dept.txt.dat”同樣為“dept”表格
下列是“mysqlimport”工具程式的基本選項,它們的用法與“mysqldump”工具程式一樣,其實大部份的MySQL工具程式都有這些選項:
| 選項 | 說明 |
| --- | --- |
| –host=數據庫服務器 或 -h 數據庫服務器 | 指定要連線的的數據庫服務器名稱,“-h”后面必須有空格;沒有使用這個選項的話,表示連線到本機 |
| –user=使用者帳號 或 -u 使用者帳號 | 指定連線的使用者帳號,“-u”后面必須有空格 |
| –password[=密碼] 或 -p[密碼] | 指定連線的密碼,“-p”后面不可以有空格;沒有提供密碼的話,執行程式以后會提示你輸入密碼;沒有使用這個選項的話,表示密碼為空白 |
如果你的資料檔案是下列格式的話:
[](http://box.kancloud.cn/2015-07-17_55a91bcd31cbb.png)
下列的命令可以把資料檔案匯入到“cmdev.dept”中:
[](http://box.kancloud.cn/2015-07-17_55a91bcdc7ec6.png)
下列的選項可以設定資料檔案的格式:
| 選項 | 說明 |
| --- | --- |
| –fields-terminated-by=字串 | 設定字段資料間的分隔符號 |
| –fields-enclosed-by=字符 | 設定每一個字段資料的前后字符 |
| –fields-optionally-enclosed-by=字符 |
| –fields-escaped-by=字符 | 設定跳脫字符的符號 |
| –lines-terminated-by=字串 | 設定每一行的結尾 |
下列的選項可以決定發生索引值重復的錯誤時,數據庫該作什么處理:
| 選項 | 說明 |
| --- | --- |
| –ignore | 忽略索引鍵重復的匯入資料 |
| –replace | 索引鍵重復時,以匯入的資料更新數據庫中的資料 |
| –local | 指定匯入的資料檔案來源為用戶端 |