MySQL 导出表到 CSV

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

摘要：在本教程中，您将学习如何将 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 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

该语句在包含结果集 cancelled_orders.csv 的 C:\tmp 文件夹中创建了一个 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;

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

  • 首先，我们用当前时间戳作为文件名的一部分构造了一个查询。
  • 其次，我们使用 PREPARE statement 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');

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

处理 NULL 值

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

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';

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

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

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

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

MySQL 导出表到 CSV

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