使用SELECT…INTO OUTFILE匯出文字檔案

brucexia發表於2024-12-09
​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所示。

使用SELECT…INTO OUTFILE匯出文字檔案​

重啟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"
"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"

可以看到，雖然將所有的欄位值匯出到文字檔案中，但是所有的記錄沒有分行，出現這種情況是因為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>

相關文章