第十二章 MySQL匯出與匯入.

Presentation on theme: "第十二章 MySQL匯出與匯入."— Presentation transcript:

1 第十二章 MySQL匯出與匯入

2 章節目錄 phpMyAdmin輸出載入與SQL檔案編碼 終端機指令進行資料匯出與匯入 本章結論 重點提示 問題與討論

3 phpMyAdmin輸出載入與SQL檔案編碼
返回章節目錄

4 phpMyAdmin進行資料輸出-1/4 登入phpMyAdmin後請選取欲輸出的資料庫或資料表，接著請選取右邊視窗上方的「輸出」，就可將選取的資料庫或資料表備份出來。 phpMyAdmin的輸出視窗左邊的選項為格式的選擇，您可以選擇CodeGen、CSV 資料、MS Excel 的 CSV 格式、Microsoft Excel 2000、Microsoft Word 2000、LaTeX、Open Document Spreadsheet、Open Document Text、PDF、SQL、Texy! text、XML與YAML等十三種檔案格式，隨著您選擇的檔案格式不同，輸出視窗右半部的選項會有所不同，MySQL預設的匯出入檔案格式為SQL檔案。

5 phpMyAdmin進行資料輸出-2/4

6 phpMyAdmin進行資料輸出-3/4 當您選擇了SQL檔案格式後，右邊視窗出現SQL檔案格式的選項，您可決定匯出資料表結構或資料表內容。

7 phpMyAdmin進行資料輸出-4/4 輸出的結果可直接瀏覽器上出現，也可點選下方的「下載儲存」下載到您的電腦上。
下載的檔案預設副檔名為sql，若檔案太大也可選擇zipped（Windows環境）或gzipped（Linux環境）檔案壓縮下載。 返回phpMyAdmin輸出載入與SQL檔案編碼

8 phpMyAdmin進行資料載入 與SQL檔案編碼格式

9 phpMyAdmin進行資料載入 與SQL檔案編碼格式
匯入狀況一、指定編碼為UTF-8，但SQL檔案為ANSI碼 匯入狀況二、指定編碼為UTF-8，但SQL檔案為UTF-8碼 匯入狀況三、指定編碼為Big5，SQL檔案為ANSI碼，加入 Big5編碼資料庫 匯入資料注意事項 返回phpMyAdmin輸出載入與SQL檔案編碼

10 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-1/7
請將附贈的光碟片放入光碟機內，進行以下的動作載入資料： Step 1 請先確認MySQL Server內是否有「pcschool」資料庫，如 果沒有請建立。 Step 2 點選並進入「pcschool」資料庫。 Step 3 點選右邊視窗上方的「載入」後瀏覽選擇光碟片內的「12」 資料夾內的「student1.sql」載入。 Step 4請點選右下角的「執行」鈕執行載入的動作。 載入時會顯示錯誤訊息。而開啟「pcschool」資料庫內「student1」資料表後，請注意有幾個欄位內容變成空白。

11 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-2/7
phpMyAdmin載入student1.sql產生錯誤訊息。

12 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-3/7
phpMyAdmin載入資料後發現幾個欄位內容空白

13 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-4/7
請使用NotePad++開啟student1.sql檔案。 比對phpMyAdmin的內容，消失的資料均是中文資料，這是因為SQL檔案編碼與要匯入的編碼不同所造成的。

14 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-5/7
請留意student1資料表.「DEFAULT CHARSET」設定為utf8，但sql檔案編碼為ANSI，也就是Big5碼，所以匯入時會產生衝突。

15 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-6/7
如何順利上傳資料呢？ Step 1 資料表有主索引欄位，請將資料表內容清空或將資料表刪 除。 Step 2 利用NotePad++的「格式」功能表內「轉換至UTF-8碼 （檔首無BOM）」，將檔案轉換為UTF-8編碼格式就可以 順利匯入。

16 匯入狀況一、指定編碼為UTF-8， 但SQL檔案為ANSI碼-7/7
phpMyAdmin內可正常顯示student1資料表內容 返回phpMyAdmin進行資料 載入與SQL檔案編碼格式

17 匯入狀況二、指定編碼為UTF-8， 但SQL檔案為UTF-8碼-1/3
請將附贈的光碟片放入光碟機內，進行以下的動作載入資料： Step 1 請先確認MySQL Server內是否有「pcschool」資料庫，如 果沒有請建立。 Step 2 點選並進入「pcschool」資料庫。 Step 3 點選右邊視窗上方的「載入」後瀏覽選擇光碟片內的「12」 資料夾內的「student2.sql」載入。 Step 4 請點選右下角的「執行」鈕執行載入的動作。

18 匯入狀況二、指定編碼為UTF-8， 但SQL檔案為UTF-8碼-2/3
phpMyAdmin內可正常顯示student2資料表內容

19 匯入狀況二、指定編碼為UTF-8， 但SQL檔案為UTF-8碼-3/3
請使用NotePad++開啟student2.sql檔案，觀察到欲建立的student2資料表.「DEFAULT CHARSET」設定為utf8，而sql檔案編碼為UTF-8碼（檔首無BOM），所以可順利匯入資料。 返回phpMyAdmin進行資料 載入與SQL檔案編碼格式

20 匯入狀況三、指定編碼為Big5， SQL檔案為ANSI碼，加入Big5編碼資料庫-1/5
請將附贈的光碟片放入光碟機內，進行以下的動作載入資料： Step 1 請先確認MySQL Server內是否有「pcschool_big5」資 料庫，如果沒有請建立。建立資料庫時請選擇 「big5_chinese_ci」編碼校對後按下「建立」鈕。

21 匯入狀況三、指定編碼為Big5， SQL檔案為ANSI碼，加入Big5編碼資料庫-2/5
Step 2 點選並進入「pcschool_big5」資料庫。 Step 3 點選右邊視窗上方的「載入」後瀏覽選擇光碟片內的「12」資 料夾內的「student3.sql」載入，匯入資料時請選擇Big5碼編 碼。

22 匯入狀況三、指定編碼為Big5， SQL檔案為ANSI碼，加入Big5編碼資料庫-3/5
Step 4 請點選右下角的「執行」鈕執行載入的動作。 phpMyAdmin內可正常顯示student3資料表內容。

23 匯入狀況三、指定編碼為Big5， SQL檔案為ANSI碼，加入Big5編碼資料庫-4/5
匯入時若仍依照原設定的utf8編碼，匯入的中文資料將變成亂碼。

24 匯入狀況三、指定編碼為Big5， SQL檔案為ANSI碼，加入Big5編碼資料庫-5/5
請使用NotePad++開啟student3.sql檔案，觀察到欲建立的student3資 料表「DEFAULT CHARSET」設定為Big5，而sql檔案編碼為ANSI 碼。 匯入資料時設定Big5碼就可順利匯入資料。Big5編碼資料仍可在 MySQL 4.1以上資料庫內儲存，只需於匯入資料時留意編碼設定。 返回phpMyAdmin進行資料 載入與SQL檔案編碼格式

25 匯入資料注意事項 請留意檔案本身編碼，若不清楚SQL檔案的編碼，請利用NotePad++開啟SQL檔案後於「格式」功能表內察看「編譯成ANSI碼」或「編譯成UTF-8碼（檔首無BOM）」，就可判斷該檔案是Big5碼或UTF-8碼。 資料表請加上「DEFAULT CHARSET」選項指定編碼，例如指定為UTF8碼語法為「DEFAULT CHARSET=UTF8」，如果沒有指定，則會依照資料庫設定。 返回phpMyAdmin進行資料 載入與SQL檔案編碼格式

26 與phpMyAdmin匯入檔案有關的設定
phpMyAdmin匯入檔案時會因Apache與PHP的設定而導致上傳失敗，所以將依序介紹Apache與PHP的設定。 Apache的設定檔為httpd.conf，若您的主機為Windows環境，且安裝Appserv，請點選開始功能表的程式集內「Appserv」的「Configuration Server」的「Apache Edit the httpd.conf Configuration File」，或請搜尋「httpd.conf」：若您的主機為Linux環境，請輸入「vi /etc/httpd/conf/httpd.conf」。 PHP的設定檔為php.ini，若您的主機為Windows環境，且安裝Appserv，請點選開始功能表的程式集內「Appserv」的「Configuration Server」的「PHP Edit the php.ini Configuration File」，或請搜尋「php.ini」：若您的主機為Linux環境，請輸入「vi /etc/php.ini」。

27 與phpMyAdmin匯入檔案有關的設定
httpd.conf請留意「Timeout」的設定，逾時設定的單位為秒，預設值為「Timeout 300」，也就是Server傳送或接收時等待的秒數，若等待時間超過設定時則出現逾時的訊息。 若使用者瀏覽網站時反應經常出現逾時訊息，可檢視主機對外的頻寬是否足夠，若頻寬無法升級，則有以下兩個建議： 1.可將「Timeout」設定拉長，但「Timeout」設定拉長後也意味 Server會花較多時間等待每一個使用者端的回應。 2.將網頁拆解成多個網頁，讓使用者可以看到網頁而不用長時間等 待，以避免使用者以為主機有問題而不再等待關閉視窗。 3.若預期這個網頁載入時間會很久，請於網頁上做明顯的提示告知。

28 與phpMyAdmin匯入檔案有關的設定
php.ini內有三個設定請各位留意 1.若允許網頁上傳檔案，「file_uploads」必須為On。 2.上傳的暫時性目錄，若不設定則為系統預設暫存目錄。 3.上傳最大檔案大小，請於「upload_max_filesize」設定。不建 議設定太大，因檔案上傳時仍會受到Apache的「Timeout」設 定影響。 返回phpMyAdmin輸出載入與SQL檔案編碼

29 終端機指令進行資料匯出與匯入 請於phpMyAdmin匯入list.sql至pcschool資料庫內，終端機指令將資 料匯出時將會用到此資料表。 phpMyAdmin雖可備份各種資料庫，但有幾個問題： 1.只能手動方式備份，無法作排程備份。 2.而且匯入資料時會有檔案大小限制。 MySQL有提供其他匯出與匯入的指令嗎？ mysqldump是MySQL的匯出指令，而mysql是匯入的指令： 1.不論是Windows或Linux環境都有這兩個指令。 2.執行mysqldump或mysql指令時不需登入至MySQL終端機操作環 境，所以可搭配Windows或Linux相關工具，做定時的備份或還 原。

30 終端機指令進行資料匯出與匯入 mysqldump做資料表資料匯出 mysqldump只匯出資料表結構或內容 mysql匯入資料
Big5碼資料匯入與匯出 SQL語法內沒有指定編碼 返回章節目錄

31 mysqldump做資料表資料匯出-1/3 mysqldump是資料庫內資料匯出的語法，基本的語法結構為
mysqldump -u帳號 –p密碼 [參數] 資料庫名稱 [資料表名稱] > SQL 檔案 [參數]及[資料表名稱] 是非必要的項目。 請參考書本12-14頁的語法，匯出「pcschool」這一個資料庫內所有 資料表欄位與內容。 mysqldump –uroot -pphpmysql pcschool >pcschool.sql 匯出的資料請利用NotePad++開啟，可察看匯出內容是否符合需 求。

32 mysqldump做資料表資料匯出-2/3 上述匯出資料的方式，資料表內容不會分行論述，日後維護頗不 方便的，因此您可以加上「--skip-opt」參數，讓資料都能逐筆分 行列出。 請參考書本12-15頁的語法，匯出「pcschool」這一個資料庫內所有 資料表欄位與內容，並讓資料能逐筆分行列出。 mysqldump -uroot -pphpmysql --skip-opt pcschool >pcschool2.sql 若只需要匯出一個資料表，請在資料庫名稱後面以空格方式作間隔 加上資料表名稱。 請參考書本12-16頁的語法，匯出「pcschool」這一個資料庫內「student2」資料表。 mysqldump -uroot -pphpmysql pcschool student2 >pcschool3.sql

33 mysqldump做資料表資料匯出-3/3 雖然只匯出指定資料表的欄位與內容，但資料表內容仍只寫在一行 敘述內，請加上「--skip-opt」參數，讓資料都能逐筆分行列出。 請參考書本12-16頁的語法，匯出「pcschool」這一個資料庫內「student2」資料表，並能分行列出。 mysqldump -uroot -pphpmysql --skip-opt pcschool student2 >pcschool4.sql 若要匯出多個資料表，請在資料庫名稱後面以空格方式作間隔加上資料表名稱即可。 請參考書本12-17頁的語法，匯出「pcschool」這一個資料庫內「student2」與「list」兩個資料表，並能分行列出。 mysqldump -uroot -pphpmysql pcschool student2 list >pcschool5.sql 返回終端機指令進行資料匯出與匯入

34 mysqldump只匯出資料表結構或內容-1/2
若只想匯出資料表結構，請加入「--no-data」參數。 請參考書本12-18頁的語法，匯出「pcschool」這一個資料庫內 所有資料表的結構。 mysqldump -uroot -pphpmysql --no-data pcschool >pcschool6.sql 若只想匯出特定的資料表結構，除加入「--no-data」參數外，也請在資料庫名稱之後加上資料表名稱。 請參考書本12-18頁的語法，匯出「pcschool」這一個資料庫內「list」資料表的結構。 mysqldump -uroot -pphpmysql --no-data pcschool list>pcschool7.sql

35 mysqldump只匯出資料表結構或內容-2/2
若只要匯出資料表的內容，請加入「--no-create-info」參數。 請參考書本12-19頁的語法，匯出「pcschool」這一個資料庫內所有 資料表的內容。 mysqldump -uroot -pphpmysql --no-create-info pcschool >pcschool8.sql 而上述參數只匯出資料表內容，而每一筆資料並未進行分行，所以 還可加上「--skip-opt」參數，讓資料都能逐筆分行列出。 請參考書本12-19頁的語法，匯出「pcschool」這一個資料庫內所有 資料表的內容，且每一筆資料進行分行。 mysqldump -uroot -pphpmysql --skip-opt --no-create-info pcschool >pcschool9.sql 「--no-create-info」與「--skip-opt」兩個參數彼此執行的結果並沒有衝突，所以兩個參數的順序不影響執行的結果。 返回終端機指令進行資料匯出與匯入

36 mysql匯入資料-1/3 mysql是資料庫內資料匯入的語法，基本的語法結構為
mysql -uroot -pphpmysql 資料庫名稱<SQL檔案 匯入資料時需指定資料庫名稱，所以匯入前必須經由phpMyAdmin網頁或終端機登入的方式建立資料庫。 建立資料庫的語法為 create database 資料庫名稱 ;

37 mysql匯入資料-2/3 現在以終端機登入的方式登入系統並建立名為counter資料庫。
登入後輸入「create database counter ;」就可建立counter資料庫。

38 mysql匯入資料-3/3 請察看柴「12」資料夾內的「record2009.sql」檔案，這個SQL檔 案有31MB之多，無法透過phpMyAdmin的方式匯入，只能使用終 端機的方式將檔案匯入到系統內， 請輸入以下的指令就可將資料匯入到counter 資料庫內： mysql -uroot -pphpmysql counter <record2009.sql 匯入後請利用phpMyAdmin察看，這個資料表內有328,246筆資料。 返回終端機指令進行資料匯出與匯入

39 Big5碼資料匯入與匯出-1/2 本書雖以UTF8為主要的編碼執行環境，但仍不可避免手邊仍有很多 專案是以「Big5」碼編輯。
若您的資料表或SQL檔案內已經指定編碼為「Big5」編碼，資料匯 出入並不會變成亂碼。 但如果MySQL為4.0版或更舊的版本，因資料表並未指定編碼，或者 SQL檔案內沒有指定編碼，那匯出入時請加上參數，以避免資料成 為亂碼：「--default-character-set=big5」。 我們於「phpMyAdmin進行資料載入與SQL檔案編碼格式 」節建立 了「pcschool_big5」資料庫，並建立student3資料表，這個資料表 已經有指定編碼為「Big5」編碼，因此當您以一般的指令進行匯 出，檔案內容並未變成亂碼。

40 Big5碼資料匯入與匯出-2/2 請參考書本12-21頁語法，指定Big5碼匯出「pcschool_big5」資料 庫。
mysqldump -uroot -pphpmysql --default-character-set=big5 pcschool_big5>student3a.sql 請參考書本12-21頁語法，指定Big5碼將student3a.sql匯入至 「pcschool_big5」資料庫。 mysql -uroot -pphpmysql --default-character-set=big5 pcschool_big5 <student3a.sql 返回終端機指令進行資料匯出與匯入

41 SQL語法內沒有指定編碼-1/7 若SQL檔案資料表結構語法內沒有「DEFAULT CHARSET=utf8」 或「DEFAULT CHARSET=big5」設定，那匯入資料時會有什麼結 果呢？ 請以NotePad++開啟「12」資料夾內「student4.sql」，您會看到 這個sql檔案內第08行並未設定編碼。 student4.sql檔為ANSI碼，請分別匯入pcschool_big5與pcschool資 料庫內。

42 SQL語法內沒有指定編碼-2/7 執行「mysql -uroot -pphpmysql pcschool_big5 <student4.sql」 將資料匯入至Big5編碼資料庫內，中文會變成亂碼。

43 SQL語法內沒有指定編碼-3/7 執行「mysql -uroot -pphpmysql pcschool <student4.sql」 將資料匯入至UTF-8編碼資料庫內，中文則會變成空白。

44 SQL語法內沒有指定編碼-4/7 請以NotePad++開啟「12」資料夾內「student5.sql」，您會看到 這個sql檔案內第08行並未設定編碼。 student5.sql檔為UTF-8碼，請分別匯入pcschool_big5與pcschool 資料庫內。

45 SQL語法內沒有指定編碼-5/7 執行「mysql -uroot -pphpmysql pcschool <student5.sql」將資料匯 入至UTF-8編碼資料庫內，中文可正常顯示。

46 SQL語法內沒有指定編碼-6/7 執行「mysql -uroot -pphpmysql pcschool_big5 <student5.sql」將 資料匯入至Big5編碼資料庫內，中文也可正常顯示。

47 SQL語法內沒有指定編碼-7/7 為何SQL檔案資料表結構沒有設定「DEFAULT CHARSET=big5」 匯入資料會變成亂碼或空白，而設定「DEFAULT CHARSET=utf8」則仍可順利匯入呢？ 因為MySQL Server預設的編碼為UTF-8，所以若沒有預設編碼則 為utf-8，若資料為Big5編碼，請務必於SQL檔案資料表結構內設定「DEFAULT CHARSET=big5」。 返回終端機指令進行資料匯出與匯入

48 本章結論 這一章跟您介紹了如何進行資料的匯出與匯入， 您可搭配伺服器主 機進行週期性資料備份。
這一章跟您介紹了如何進行資料的匯出與匯入， 您可搭配伺服器主 機進行週期性資料備份。 Microsoft Access具有親切的操作介面、可製作報表、可製作簡易的 視窗操作介面及可與Microsoft Word、Microsoft Excel做資料連結或 處理，對個人與小型工作室內部使用來說是很方便，下一章將介紹 資料庫查詢語法及如何與Access溝通。 返回章節目錄

49 重點提示 匯出入Big5碼資料時請加上「--default-character-set=big5」參數
SQL檔案指定編碼與檔案本身編碼必須一致，否則無法匯入資料。 phpMyAdmin匯入檔案會受到php.ini、httpd.conf內參數設定影響。 匯出資料的基本語法為： mysqldump -u帳號 –p密碼 [參數] 資料庫名稱 [資料表名稱] > SQL檔 案 匯出資料若資料能逐筆分行列出，請加上「--skip-opt」參數。 匯出資料若只匯出結構，請加上「--no-data」參數。 匯出資料若只有匯出內容，請加上「--no-create-info」參數 mysql是資料庫內資料匯入的語法，基本的語法結構為 mysql -uroot -pphpmysql 資料庫名稱<SQL檔案 匯出入Big5碼資料時請加上「--default-character-set=big5」參數 ANSI碼SQL檔案沒有指定編碼下匯入Big5編碼資料庫中文變成亂碼 ANSI碼SQL檔案沒指定編碼下匯入UTF-8編碼資料庫中文變成空白

50 問題與討論 1.若希望網站等待客戶端回應的時間延長至600秒，請問該如何設 定？
1.若希望網站等待客戶端回應的時間延長至600秒，請問該如何設 定？ 2.若希望檔案上傳的檔案大小調整為10MB，請問該如何設定？ 3.請匯出pcschool資料庫內list資料表，且資料會逐筆分行列出。 4.請匯出pcschool資料庫內「student2」與「list」兩個資料表結構 5.請匯出pcschool資料庫內「student2」與「list」兩個資料表內容 6.請建立一個新的Big5編碼資料庫，並請匯入student3a.sql。 7.ANSI碼SQL檔案沒有指定編碼下匯入Big5編碼或UTF-8編碼資料 庫會有什麼變化？