**目錄** [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 | 指定匯入的資料檔案來源為用戶端 |