MySQL 超新手入門(19)匯入與匯出資料 by Michael | CodeData
top

MySQL 超新手入門(19)匯入與匯出資料

分享:

專欄作者新書出版:Android App程式開發剖析 第三版(適用Android 8 Oreo與Android Studio 3)

MySQL 超新手入門(18)錯誤處理與查詢 << 前情

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」子句指定一個檔案名稱,執行敘述以後回傳的資料會儲存為檔案。下列是它的語法:

mysql_19_snap_01

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

mysql_19_snap_02

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

mysql_19_snap_03

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

mysql_19_snap_04

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

mysql_19_snap_05

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

mysql_19_snap_06

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

mysql_19_snap_07

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

mysql_19_snap_08

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

mysql_19_snap_09

3 使用SQL敘述匯入資料

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

mysql_19_snap_10

3.1 指定資料檔案

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

mysql_19_snap_11

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

mysql_19_snap_12

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

mysql_19_snap_13

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

mysql_19_snap_14

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

mysql_19_snap_15

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

mysql_19_snap_16

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

mysql_19_snap_17

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

mysql_19_snap_18

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

mysql_19_snap_19

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

mysql_19_snap_20

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

mysql_19_snap_21

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

3.2 設定資料格式

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

mysql_19_snap_22

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

mysql_19_snap_23

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

mysql_19_snap_24

3.3 處理匯入的資料

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

mysql_19_snap_25

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

mysql_19_snap_26

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

mysql_19_snap_27

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

mysql_19_snap_28

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

mysql_19_snap_29

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

mysql_19_snap_30

3.4 索引鍵重複

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

mysql_19_snap_31

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

mysql_19_snap_32

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

mysql_19_snap_33

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

mysql_19_snap_34

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

mysql_19_snap_35

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

mysql_19_snap_36

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

mysql_19_snap_37

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

mysql_19_snap_38

3.5 匯入資訊

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

mysql_19_snap_39

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

mysql_19_snap_40

在上列的資訊中:

  • Records:表示從資料檔案中讀取的資料數量
  • Deleted:表示在發生索引重複的情況下更新資料的數量
  • Skipped:表示在發生索引重複的情況下被忽略的資料數量
  • Warnings:表示資料檔案中有問題的資料數量,例如轉換Hello字串為數值

4 使用mysqldump程式匯出資料

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

mysql_19_snap_41

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

選項 說明
–host=資料庫伺服器 指定要連線的的資料庫伺服器名稱,「-h」後面必須有空格;沒有使用這個選項的話,表示連線到本機
-h 資料庫伺服器
–user=使用者帳號 指定連線的使用者帳號,「-u」後面必須有空格
-u 使用者帳號
–password[=密碼] 指定連線的密碼,「-p」後面不可以有空格;沒有提供密碼的話,執行程式以後會提示你輸入密碼;沒有使用這個選項的話,表示密碼為空白
-p[密碼]

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

mysql_19_snap_42

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

mysql_19_snap_43

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

選項 說明
–result-file=檔案名稱 指定匯出資料的檔案名稱,資料夾符號必須使用「/」
–all-databases 匯出資料庫伺服器中所有資料庫的資料
–tab=資料夾 指定匯出資料檔案存放的資料夾

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

mysql_19_snap_44

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

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

mysql_19_snap_45

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

選項 說明
–fields-terminated-by=字串 設定欄位資料間的分隔符號
–fields-enclosed-by=字元 設定每一個欄位資料的前後字元
–fields-optionally-enclosed-by=字元
–fields-escaped-by=字元 設定跳脫字元的符號
–lines-terminated-by=字串 設定每一行的結尾

5 使用mysqlimport程式匯入資料

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

mysql_19_snap_46

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

  • 資料檔案中不可以包含SQL敘述
  • 檔案名稱會決定匯入資料庫中的哪個表格,MySQL會使用去除附加檔名後的名稱。例如「dept.dat」為「dept」表格;「dept.txt.dat」同樣為「dept」表格

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

選項 說明
–host=資料庫伺服器 指定要連線的的資料庫伺服器名稱,「-h」後面必須有空格;沒有使用這個選項的話,表示連線到本機
-h 資料庫伺服器
–user=使用者帳號 指定連線的使用者帳號,「-u」後面必須有空格
-u 使用者帳號
–password[=密碼] 指定連線的密碼,「-p」後面不可以有空格;沒有提供密碼的話,執行程式以後會提示你輸入密碼;沒有使用這個選項的話,表示密碼為空白
-p[密碼]

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

mysql_19_snap_47

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

mysql_19_snap_48

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

選項 說明
–fields-terminated-by=字串 設定欄位資料間的分隔符號
–fields-enclosed-by=字元 設定每一個欄位資料的前後字元
–fields-optionally-enclosed-by=字元
–fields-escaped-by=字元 設定跳脫字元的符號
–lines-terminated-by=字串 設定每一行的結尾

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

選項 說明
–ignore 忽略索引鍵重複的匯入資料
–replace 索引鍵重複時,以匯入的資料更新資料庫中的資料
–local 指定匯入的資料檔案來源為用戶端

後續 >> MySQL 超新手入門(20)效率

分享:
按讚!加入 CodeData Facebook 粉絲群

相關文章

留言

留言請先。還沒帳號註冊也可以使用FacebookGoogle+登錄留言

chocklate6031109/09

請問有可能直接透過mysql主動讀取excel的特定值作為搜尋條件嗎?
EX: where 某欄位 = 數值 數值可以藉由透過主動讀取excel搜尋而非人為輸入 還是必須下載別的軟體 ?
感謝你打這麼詳細的文章

Michael, Fan Zhang08/09

下面是我得到的错误,有办法处理吗,谢谢!

11:09:26 select * into outfile '/Users/mikezhang/Documents/SQL_Coding_Files/outfl.txt' from dept Error Code: 1. Can't create/write to file '/Users/mikezhang/Documents/SQL_Coding_Files/outfl.txt' (Errcode: 13 - Permission denied) 0.00045 sec

熱門論壇文章

熱門技術文章