有時需要將MySQL資料庫中的資料匯出到外部儲存檔案中，MySQL資料庫中的資料可以匯出成sql文字檔案、xml檔案或者html檔案。本節將介紹資料匯出的常用方法。
11.4.1 使用SELECT…INTO OUTFILE匯出文字檔案
MySQL資料庫匯出資料時，允許使用包含匯出定義的SELECT語句進行資料的匯出操作。該檔案被建立到伺服器主機上，因此必須擁有檔案寫入許可權（FILE許可權）才能使用此語法。“SELECT...INTO OUTFILE 'filename'”形式的SELECT語句可以把被選擇的行寫入一個檔案中，並且filename不能是一個已經存在的檔案。SELECT...INTO OUTFILE語句的基本格式如下：
SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTIONS]
可以看到SELECT columnlist FROM table WHERE condition為一個查詢語句，查詢結果返回滿足指定條件的一條或多條記錄；INTO OUTFILE語句的作用就是把前面SELECT語句查詢出來的結果，匯出到名稱為“filename”的外部檔案中；[OPTIONS]為可選引數選項，OPTIONS部分的語法包括FIELDS和LINES子句，其可能的取值如下：
FIELDS TERMINATED BY 'value'：設定欄位之間的分隔字元，可以為單個或多個字元，預設情況下為製表符（\t）。
FIELDS [OPTIONALLY] ENCLOSED BY 'value'：設定欄位的包圍字元，只能為單個字元，若使用了OPTIONALLY關鍵字，則只有CHAR和VERCHAR等字元資料欄位被包圍。
FIELDS ESCAPED BY 'value'：設定如何寫入或讀取特殊字元，只能為單個字元，即設定跳脫字元，預設值為“\”。
LINES STARTING BY 'value'：設定每行資料開頭的字元，可以為單個或多個字元，預設情況下不使用任何字元。
LINES TERMINATED BY 'value'：設定每行資料結尾的字元，可以為單個或多個字元，預設值為“\n”。
FIELDS和LINES兩個子句都是自選的，但是如果兩個都被指定了，則FIELDS必須位於LINES的前面。
使用SELECT...INTO OUTFILE語句，可以非常快速地把一張錶轉儲到伺服器上。如果想要在伺服器主機之外的部分客戶主機上建立結果檔案，不能使用SELECT...INTO OUTFILE，應該使用“MySQL –e "SELECT ..." > file_name”這類的命令來生成檔案。
SELECT...INTO OUTFILE是LOAD DATA INFILE的補語，用於語句的OPTIONS部分的語法包括部分FIELDS和LINES子句，這些子句與LOAD DATA INFILE語句同時使用。
【例11.10】使用SELECT...INTO OUTFILE將test_db資料庫中的person表中的記錄匯出到文字檔案，SQL語句如下：
mysql> SELECT * FROM test_db.person INTO OUTFILE 'D:/person0.txt';
語句執行後報錯資訊如下：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
這是因為MySQL預設對匯出的目錄有許可權限制，也就是說使用命令列進行匯出的時候，需要指定目錄。那麼指定的目錄是什麼呢？
查詢指定目錄的命令如下：
show global variables like '%secure%';
執行結果如下：
+-------------------------+-----------------------------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------------------------+
|require_secure_transport | OFF |
|secure_file_priv | D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\|
+-------------------------+-----------------------------------------------+
因為secure_file_priv配置的關係，所以必須匯出到D:\ProgramData\MySQL\MySQL Server 9.0\Uploads\目錄下，該目錄就是指定目錄。如果想自定義匯出路徑，需要修改my.ini配置檔案。開啟路徑D:\ProgramData\MySQL\MySQL Server 9.0，用記事本開啟my.ini檔案，然後搜尋以下程式碼：
secure-file-priv="D:/ProgramData/MySQL/MySQL Server 9.0/Uploads"
在上述程式碼前新增#註釋掉，然後新增以下內容：
secure-file-priv="D:/"
結果如圖11.1所示。
圖11.1 設定資料表的匯出路徑
重啟MySQL伺服器後，再次使用SELECT...INTO OUTFILE將test_db資料庫中的person表中的記錄匯出到文字檔案，SQL語句如下：
mysql>SELECT * FROM test_db.person INTO OUTFILE 'D:/person0.txt'; Query OK, 1 row affected (0.01 sec)
由於指定了INTO OUTFILE子句，因此SELECT會將查詢出來的3個欄位值儲存到C:\person0.txt檔案中。開啟該檔案，內容如下：
1 Green 21 Lawyer 2 Suse 22 dancer 3 Mary 24 Musician 4 Willam 20 sports man 5 Laura 25 \N 6 Evans 27 secretary 7 Dale 22 cook 8 Edison 28 singer 9 Harry 21 magician 10 Harriet 19 pianist
預設情況下，MySQL使用製表符（\t）分隔不同的欄位，欄位沒有被其他字元包圍。另外，第5行中有一個欄位值為“\N”，表示該欄位的值為NULL。預設情況下，當遇到NULL時，會返回“\N”，代表空值，其中的反斜線（\）表示跳脫字元；如果使用ESCAPED BY選項，則N前面為指定的跳脫字元。
【例11.11】使用SELECT...INTO OUTFILE語句，將test_db資料庫person表中的記錄匯出到文字檔案，使用FIELDS選項和LINES選項，要求欄位之間使用逗號分隔，所有欄位值用雙引號引起來，定義跳脫字元為單引號“\'”，SQL語句如下：
SELECT * FROM test_db.person INTO OUTFILE "D:/person1.txt" FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\'' LINES TERMINATED BY '\r\n';
該語句將把person表中所有記錄匯入D盤目錄下的person1.txt文字檔案中。
“FIELDS TERMINATED BY ','”表示欄位之間用逗號分隔；“ENCLOSED BY '\"'”表示每個欄位用雙引號引起來；“ESCAPED BY '\'”表示將系統預設的跳脫字元替換為單引號；“LINES TERMINATED BY '\r\n'”表示每行以回車換行符結尾，保證每一條記錄佔一行。
執行成功後，在D盤下生成一個person1.txt檔案。開啟檔案，內容如下：
"1","Green","21","Lawyer" "2","Suse","22","dancer" "3","Mary","24","Musician" "4","Willam","20","sports man" "5","Laura","25",'N' "6","Evans","27","secretary" "7","Dale","22","cook" "8","Edison","28","singer" "9","Harry","21","magician" "10","Harriet","19","pianist"
可以看到，所有的欄位值都被雙引號引起來；第5條記錄中空值的表示形式為“N”，即使用單引號替換了反斜線跳脫字元。
【例11.12】使用SELECT...INTO OUTFILE語句，將test_db資料庫person表中的記錄匯出到文字檔案，使用LINES選項，要求每行記錄以字串“>”開始、以字串“<end>”結尾，SQL語句如下：
SELECT * FROM test_db.person INTO OUTFILE "D:/person2.txt" LINES STARTING BY '> ' TERMINATED BY '<end>';
語句執行成功後，在D盤下生成一個person2.txt檔案。開啟該檔案，內容如下：
> 1 Green 21 Lawyer <end>> 2 Suse 22 dancer <end>> 3 Mary 24 Musician <end>> 4 Willam 20 sports man <end>> 5 Laura 25 \N <end>> 6 Evans 27 secretary <end>> 7 Dale 22 cook <end>> 8 Edison 28 singer <end>> 9 Harry 21 magician <end>> 10 Harriet 19 pianist <end>
可以看到，雖然將所有的欄位值匯出到文字檔案中，但是所有的記錄沒有分行，出現這種情況是因為TERMINATED BY選項替換了系統預設的換行符。如果希望換行顯示，則需要修改匯出語句：
SELECT * FROM test_db.person INTO OUTFILE "D:/person3.txt" LINES STARTING BY '> ' TERMINATED BY '<end>\r\n';
執行完語句之後，換行顯示每條記錄，結果如下：
> 1 Green 21 Lawyer <end> > 2 Suse 22 dancer <end> > 3 Mary 24 Musician <end> > 4 Willam 20 sports man <end> > 5 Laura 25 \N <end> > 6 Evans 27 secretary <end> > 7 Dale 22 cook <end> > 8 Edison 28 singer <end> > 9 Harry 21 magician <end> > 10 Harriet 19 pianist <end>
11.4.2 使用mysqldump命令匯出文字檔案
除了使用SELECT… INTO OUTFILE語句匯出文字檔案之外，還可以使用mysqldump命令。11.1節開始介紹了使用mysqldump備份資料庫，該工具不僅可以將資料匯出為包含CREATE、INSERT的sql檔案，也可以匯出為純文字檔案。
mysqldump建立一個包含建立表的CREATE TABLE語句的tablename.sql檔案和一個包含其資料的tablename.txt檔案。mysqldump匯出文字檔案的基本語法格式如下：
mysqldump -T path-u root -p dbname [tables] [OPTIONS]
只有指定了-T引數才可以匯出純文字檔案；path表示匯出資料的目錄；tables為指定要匯出的表名稱，如果不指定，將匯出資料庫dbname中所有的表；[OPTIONS]為可選引數選項，這些選項需要結合-T選項使用。OPTIONS常見的取值有：
--fields-terminated-by=value：設定欄位之間的分隔字元，可以為單個或多個字元，預設情況下為製表符（\t）。
--fields-enclosed-by=value：設定欄位的包圍字元。
--fields-optionally-enclosed-by=value：設定欄位的包圍字元，只能為單個字元，只能包括CHAR和VERCHAR等字元資料欄位。
--fields-escaped-by=value：控制如何寫入或讀取特殊字元，只能為單個字元，即設定跳脫字元，預設值為\。
--lines-terminated-by=value：設定每行資料結尾的字元，可以為單個或多個字元，預設值為“\n”。
這裡的OPTIONS的設定與SELECT…INTO OUTFILE語句中的OPTIONS不同，各個取值中等號後面的value值不要用引號括起來。
【例11.13】使用mysqldump將test_db資料庫person表中的記錄匯出到文字檔案，SQL語句如下：
mysqldump -T D:\ test_db person -u root -p
語句執行成功，系統D盤目錄下面將會有兩個檔案，分別為person.sql和person.txt。person.sql包含建立person表的CREATE語句，其內容如下：
-- MySQL dump 10.13 Distrib 9.0.1, for Win64 (x86_64) -- -- Host: localhost Database: test_db -- ------------------------------------------------------ -- Server version 9.0.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; SET NAMES utf8mb4 ; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `person` -- DROP TABLE IF EXISTS `person`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `person` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(40) NOT NULL DEFAULT '', `age` int(11) NOT NULL DEFAULT '0', `info` char(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-07-25 16:40:55
備份檔案中的資訊已在11.1.1節中介紹了。
person.txt包含資料包中的資料，其內容如下：
1 Green 21 Lawyer 2 Suse 22 dancer 3 Mary 24 Musician 4 Willam 20 sports man 5 Laura 25 \N 6 Evans 27 secretary 7 Dale 22 cook 8 Edison 28 singer 9 Harry 21 magician 10 Harriet 19 pianist
【例11.14】使用mysqldump命令將test_db資料庫中的person表中的記錄匯出到文字檔案，使用FIELDS選項，要求欄位之間使用逗號“,”間隔，所有字元型別欄位值用雙引號引起來，定義跳脫字元為“?”，每行記錄以“\r\n”結尾，SQL語句如下：
mysqldump -T D:\ test_db person -u root -p --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n Enter password: ******
上面語句要在一行中輸入，語句執行成功後，系統D盤目錄下面將會有兩個檔案，分別為person.sql和person.txt。person.sql包含建立person表的CREATE語句，其內容與【例11.13】中的相同；person.txt檔案的內容與【例11.13】中的不同，顯示如下：
1,"Green",21,"Lawyer" 2,"Suse",22,"dancer" 3,"Mary",24,"Musician" 4,"Willam",20,"sports man" 5,"Laura",25,?N 6,"Evans",27,"secretary" 7,"Dale",22,"cook" 8,"Edison",28,"singer" 9,"Harry",21,"magician" 10,"Harriet",19,"pianist"
可以看到，只有字元型別的值被雙引號引起來了，而數值型別的值沒有；第5行記錄中的NULL表示為“?N”，使用“?”替代了系統預設的“\”。
11.4.3 使用mysql命令匯出文字檔案
mysql是一個功能豐富的工具命令，使用它們還可以在命令列模式下執行SQL指令，將查詢結果匯入文字檔案中。相比mysqldump，mysql工具匯出的結果具有更強的可讀性。
如果MySQL伺服器是單獨的機器，使用者是在一個客戶端上進行操作，要把資料結果匯入客戶端上。
使用mysql匯出資料文字檔案的基本語法格式如下：
mysql -u root -p --execute= "SELECT語句" dbname > filename.txt
該命令使用--execute選項，表示執行該選項後面的語句並退出，後面的語句必須用雙引號引起來；dbname為要匯出的資料庫名稱；匯出的檔案中不同列之間使用製表符分隔，第1行包含了各個欄位的名稱。
【例11.15】使用mysql命令，將test_db資料庫中的person表中的記錄匯出到文字檔案，SQL語句如下：
mysql -u root -p --execute="SELECT * FROM person;" test_db > D:\person3.txt
語句執行完畢之後，系統D盤目錄下面將會有名稱為“person3.txt”的文字檔案，其內容如下：
id name age info 1 Green 21 Lawyer 2 Suse 22 dancer 3 Mary 24 Musician 4 Willam 20 sports man 5 Laura 25 NULL 6 Evans 27 secretary 7 Dale 22 cook 8 Edison 28 singer 9 Harry 21 magician 10 Harriet 19 pianist
可以看到，person3.txt檔案中包含了每個欄位的名稱和各條記錄，該顯示格式與MySQL命令列下SELECT查詢結果的顯示格式相同。
使用mysql命令還可以指定查詢結果的顯示格式，如果某條記錄的欄位很多，可能一行不能完全顯示，可以使用--vartical引數將每條記錄分為多行顯示。
【例11.16】使用mysql命令將test_db資料庫中的person表中的記錄匯出到文字檔案，使用--vertical引數顯示結果，SQL語句如下：
mysql -u root -p --vertical --execute="SELECT * FROM person;" test_db > D:\person4.txt
語句執行之後，D:\person4.txt檔案中的內容如下：
*** 1. row *** id: 1 name: Green age: 21 info: Lawyer *** 2. row *** id: 2 name: Suse age: 22 info: dancer *** 3. row *** id: 3 name: Mary age: 24 info: Musician *** 4. row *** id: 4 name: Willam age: 20 info: sports man *** 5. row *** id: 5 name: Laura age: 25 info: NULL *** 6. row *** id: 6 name: Evans age: 27 info: secretary *** 7. row *** id: 7 name: Dale age: 22 info: cook *** 8. row *** id: 8 name: Edison age: 28 info: singer *** 9. row *** id: 9 name: Harry age: 21 info: magician *** 10. row *** id: 10 name: Harriet age: 19 info: pianist
可以看到，SELECT的查詢結果匯出到文字檔案之後，顯示格式發生了變化，如果person表中的記錄內容很長，這樣顯示會讓人更加容易閱讀。
mysql還可以將查詢結果匯出到html檔案中，使用--html選項即可。
【例11.17】使用MySQL命令將test_db資料庫中的person表中的記錄匯出到html檔案，SQL語句如下：
mysql -u root -p --html --execute="SELECT * FROM person;" test_db > D:\person5.html
語句執行成功，將在D盤建立檔案person5.html，該檔案在瀏覽器中的顯示效果如圖11.2所示。
如果要將表資料匯出到xml檔案中，可使用--xml選項。
【例11.18】使用mysql命令將test_db資料庫中的person表中的記錄匯出到xml檔案，SQL語句如下：
mysql -u root -p --xml --execute="SELECT * FROM person;" test_db >D:\person6.xml
語句執行成功，將在D盤建立檔案person6.xml，該檔案在瀏覽器中的顯示效果如圖11.3所示。
