Skip to main content

MySQL将表导出到CSV

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

将MySQL数据库中的数据转换为CSV文件格式将非常有用,因为您可以按照自己的方式分析和格式化数据。

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

导出数据之前,必须确保:

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

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 'C:/tmp/cancelled_orders.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

该声明创建了一个名为“取消订单”的CSV文件。包含结果集的C:\tmp文件夹中的csv。

CSV文件包含结果集中的行。每一行都由一系列回车符和由“\r\n”子句终止的行指定的换行符终止。每一行包含结果集中每一行的值。

每个值都用双引号括起来,用“.”子句括起来的字段表示。这可以防止可能包含逗号(,)的值被解释为字段分隔符。用双引号括起值时,值内的逗号不会被识别为字段分隔符。

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

通常需要将数据导出到CSV文件中,该文件的名称包含创建该文件的时间戳。要做到这一点,需要使用MySQL prepared语句。

以下命令将整个orders表导出到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;

让我们更详细地检查上面的命令。

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

导出带有列标题的数据

如果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');

如查询所示,您需要包括每一列的列标题。

处理空值

如果结果集中的值包含空值,目标文件将包含“N”而不是空值。要解决此问题,您需要将空值替换为另一个值,例如,不适用(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';

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

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

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

MySQL Export Table to CSV

MySQL Workbench导出的CSV文件支持列标题、空值和其他重要功能。