MySQL Export Table to CSV

摘要：在本教程中，您将学习如何将 MySQL 表导出到 CSV 文件的各种技术。

CSV 代表逗号分隔值。您经常使用 CSV 文件格式在 Microsoft Excel、Open Office、Google Docs 等应用程序之间交换数据。

从 MySQL 数据库中获取 CSV 文件格式的数据将很有用，因为您可以按照您想要的方式分析和格式化数据。

MySQL 提供了一种简单的方法将查询结果导出到驻留在数据库服务器中的 CSV 文件中。

导出数据之前，您必须确保：

• MySQL 服务器的进程对包含目标 CSV 文件的目标文件夹具有写入权限。
• 目标 CSV 文件不得存在。

以下查询从orders表中选择已取消的订单：

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled';Code language: SQL (Structured Query Language) (sql)

要将此结果集导出到 CSV 文件中，请向上面的查询添加一些子句，如下所示：

SELECT 
    orderNumber, status, orderDate, requiredDate, comments
FROM
    orders
WHERE
    status = 'Cancelled' 
INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';Code language: SQL (Structured Query Language) (sql)

该语句在包含结果集的C:\tmp文件夹中创建了一个名为cancelled_orders.csv的 CSV 文件。

CSV 文件包含结果集中的行。每行都由LINES TERMINATED BY '\r\n'子句指定的回车符和换行符序列终止。每行包含结果集中该行的每一列的值。

每个值都用双引号括起来，由FIELDS ENCLOSED BY '”'子句指示。这可以防止可能包含逗号 (,) 的值被解释为字段分隔符。当用双引号将值括起来时，值内的逗号不会被识别为字段分隔符。

将数据导出到文件名包含时间戳的 CSV 文件

您经常需要将数据导出到 CSV 文件中，该文件的名称包含创建该文件的时间戳。为此，您需要使用MySQL 准备好的语句。

以下命令将整个订单表导出到 CSV 文件中，并将时间戳作为文件名的一部分。

SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s');

SET @FOLDER = 'c:/tmp/';
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;Code language: SQL (Structured Query Language) (sql)

让我们更详细地研究一下上面的命令。

• 首先，我们构建了一个查询，其中当前时间戳作为文件名的一部分。
• 其次，我们使用PREPARE statements FROM命令准备要执行的语句。
• 第三，我们使用EXECUTE命令执行该语句。

您可以通过事件包装该命令，并根据需要安排该事件定期运行。

导出带有列标题的数据

如果 CSV 文件包含第一行作为列标题，这样文件会更容易理解，这会很方便。

要添加列标题，您需要使用UNION语句，如下所示：

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');Code language: SQL (Structured Query Language) (sql)

正如查询所示，您需要包含每列的列标题。

处理 NULL 值

如果结果集中的值包含NULL值，则目标文件将包含"N而不是NULL 。要解决此问题，您需要使用另一个值替换NULL值，例如不适用 ( N/A ) IFNULL 函数如下查询：

SELECT 
    orderNumber, orderDate, IFNULL(shippedDate, 'N/A')
FROM
    orders INTO OUTFILE 'C:/tmp/orders2.csv' 
    FIELDS ENCLOSED BY '"' 
    TERMINATED BY ';' 
    ESCAPED BY '"' LINES 
    TERMINATED BY '\r\n';Code language: SQL (Structured Query Language) (sql)

我们将shippedDate列中的NULL值替换为N/A字符串。 CSV 文件显示N/A而不是NULL值。

使用 MySQL Workbench 将数据导出到 CSV 文件

如果您无法访问数据库服务器来获取导出的 CSV 文件，您可以使用 MySQL Workbench 将查询结果集导出到本地计算机中的 CSV 文件，如下所示：

• 首先，执行查询获取其结果集。
• 其次，从结果面板中单击“将记录集导出到外部文件”。结果集也称为记录集。
• 第三，显示一个新对话框。它要求您提供文件名和文件格式。输入文件名，选择 CSV 作为文件格式，然后单击“保存”按钮。

MySQL Workbench 导出的 CSV 文件支持列标题、 NULL值和其他强大功能。