MySQL將表匯出為CSV - tw511教學網

在本教學中，您將學習如何將MySQL表匯出到CSV檔案的各種方法/技術。

CSV代表逗號分隔值。您經常使用CSV檔案格式在Microsoft Excel，Open Office，Google Docs等應用程式之間交換資料。

以CSV檔案格式從MySQL資料庫中獲取資料將非常有用，因為您可以按照所需的方式分析和格式化資料。

MySQL提供了一種將查詢結果匯出到位於資料庫伺服器中的CSV檔案的簡單方法。

在匯出資料之前，必須確保：

MySQL伺服器的進程對包含目標CSV檔案的目標檔案夾具有寫存取許可權。
要匯出的目標CSV檔案不能存在。

以下查詢從orders表中查詢選擇已取消的訂單：

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled';

要將此結果集匯出為CSV檔案，請按如下方式向上述查詢新增一些子句：

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'F:/worksp/mysql/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

該語句在F:/worksp/mysql/目錄下建立一個包含結果集，名稱為cancelled_orders.csv的CSV檔案。

CSV檔案包含結果集中的行集合。每行由一個回車序列和由LINES TERMINATED BY '\r\n'子句指定的換行字元終止。檔案中的每行包含表的結果集的每一行記錄。

每個值由FIELDS ENCLOSED BY '"'子句指示的雙引號括起來。這樣可以防止可能包含逗號(，)的值被解釋為欄位分隔符。當用雙引號括住這些值時，該值中的逗號不會被識別為欄位分隔符。

將資料匯出到檔案名包含時間戳的CSV檔案

我們經常需要將資料匯出到CSV檔案中，該檔案的名稱包含建立檔案的時間戳。為此，您需要使用MySQL準備語句。

以下命令將整個orders表匯出為將時間戳作為檔案名的一部分的CSV檔案。

SET @TS = DATE_FORMAT(NOW(),'_%Y%m%d_%H%i%s');

SET @FOLDER = 'F:/worksp/mysql/';
SET @PREFIX = 'orders';
SET @EXT    = '.csv';

SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT,
    "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'",
    "  LINES TERMINATED BY '\r\n';");

PREPARE statement FROM @CMD;

EXECUTE statement;

下面，讓我們來詳細講解上面的命令。

首先，構造了一個具有當前時間戳的查詢作為檔案名的一部分。
其次，使用PREPARE語句FROM命令準備執行語句。
第三，使用EXECUTE命令執行語句。

可以通過事件包裝命令，並根據需要定期安排事件的執行。

使用列標題匯出資料

如果CSV檔案包含第一行作為列標題，那麼該檔案更容易理解，這是非常方便的。

要新增列標題，需要使用UNION語句如下：

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'F:/worksp/mysql/orders_union_title.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

如查詢所示，需要包括每列的列標題。

處理NULL值

如果結果集中的值包含NULL值，則目標檔案將使用「N/A」來代替資料中的NULL值。要解決此問題，您需要將NULL值替換為另一個值，例如不適用(N/A)，方法是使用IFNULL函式，如下：

SELECT 
    orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
FROM
    orders INTO OUTFILE 'F:/worksp/mysql/orders_null2na.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n';

我們用N/A字串替換了shippingDate列中的NULL值。 CSV檔案將顯示N/A而不是NULL值。