MySQL将表导出为CSV格式

MySQL具有将表导出为CSV文件的功能。CSV文件格式是一种逗号分隔值，我们用它来在各种应用程序之间交换数据，比如 Microsoft Excel 、Google Docs和Open Office。有 MySQL数据的CSV文件 格式可以让我们按照我们想要的方式分析和格式化它们。它是一个纯文本文件，可以让我们轻松地导出数据。

MySQL 提供了一种将任何表导出到数据库服务器中的CSV文件的简单方法。在导出MySQL数据之前，我们必须确保以下几点：

• MySQL服务器的进程具有对包含CSV文件的指定(目标)文件夹的读/写访问权限。
• 指定的CSV文件在系统中不存在。

要将表导出为CSV文件，我们将使用 SELECT INTO….OUTFILE 语句。这个语句是 LOAD DATA 命令的补充，它用于从表中写入数据，然后将其导出到服务器主机上的指定文件格式中。这是为了确保我们有文件权限来使用这个语法。

SELECT column_lists
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv'   
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\r\n';  

我们也可以使用这种语法以及值语句将数据直接导出到文件中。下面的语句更清楚地解释了这一点：

SELECT * FROM (VALUES ROW(1,2,3,4),ROW(5,6),ROW(7,8)) AS table1
    INTO OUTFILE '/tmp/selected_values.txt';

如果我们想要导出 所有的表列 ，我们将使用以下语法。使用这个语句，行的排序和数量将由 ORDER BY和 LIMIT 子句来控制。

TABLE table_name ORDER BY lname LIMIT 1000
INTO OUTFILE '/path/filename.txt'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';;

从上面，

LINES TERMINATED BY ‘,’ ：它用于指示以逗号操作符结尾的文件中的行的行。每行包含文件中每个列的数据。

FIELDS ENCLOSED BY ‘”‘ ：它用于指定文件中由双引号标记的字段。它防止包含逗号分隔符的值。如果值包含在双引号中，则不将逗号识别为分隔符。

导出文件的存储位置

MySQL中每个导出文件的存储位置存储在默认变量 secure_file_priv 中。我们可以执行以下命令来获取导出文件的默认路径。

mysql> SHOW VARIABLES LIKE "secure_file_priv";

执行后，将会得到以下结果，我们可以看到这个路径： C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ 作为默认文件路径。这条路径将在运行导出命令时使用。

如果我们想要更改在 secure_file_priv 变量中指定的CSV文件的默认导出位置，我们需要编辑 my.ini 配置文件。在Windows平台上，该文件位于以下路径： C：\ ProgramData \ MySQL \ MySQL Server X.Y 。

如果我们想要导出MySQL数据，首先，我们需要创建一个至少有一个 数据库 和 表 的 table 。我们将使用这个表作为示例。

我们可以通过在我们使用的编辑器中执行以下代码来创建一个 数据库和表 ：

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE employee_detail (
  ID int NOT NULL AUTO_INCREMENT,
  Name varchar(45) DEFAULT NULL,
  Email varchar(45) DEFAULT NULL,
  Phone varchar(15) DEFAULT NULL,
  City varchar(25) DEFAULT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY unique_email (Email),
  UNIQUE KEY index_name_phone (Name,Phone)
)

INSERT INTO employee_detail ( Id, Name, Email, Phone, City)   
VALUES (1, 'Peter', 'peter@javatpoint.com', '49562959223', 'Texas'),   
(2, 'Suzi', 'suzi@javatpoint.com', '70679834522', 'California'),   
(3, 'Joseph', 'joseph@javatpoint.com', '09896765374', 'Alaska'),  
(4, 'Alex', 'alex@javatpoint.com', '97335737548', 'Los Angeles'),  
(5, 'Mark', 'mark@javatpoint.com', '78765645643', 'Washington'),  
(6, 'Stephen', 'stephen@javatpoint.com', '986345793248', 'New York');  

如果我们执行 SELECT 语句，我们将看到以下输出：

使用SELECT INTO … OUTFILE语句将MySQL数据导出为CSV格式

要将表数据导出到CSV文件中，我们需要执行以下查询：

SELECT Id, Name, Email, Phone, City FROM employee_detail
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

我们将得到以下输出，其中我们可以看到有六行受到影响。这是因为指定的表格只包含六行。

如果我们再次执行相同的语句，MySQL会产生一个错误消息，可以在下面的输出中看到：

错误信息告诉我们，在指定的位置已经存在指定的文件名。因此，如果我们导出具有相同名称和位置的新CSV文件，它将无法创建。我们可以通过删除指定位置上的现有文件或将文件名重命名来解决这个问题，以在同一位置创建它。

我们可以通过以下方式导航到给定路径来验证是否已在指定位置创建了CSV文件：

当我们打开这个文件时，它会呈现如下的图像：

在这张图片中，我们可以看到数字字段使用引号括起来了。我们可以通过在ENCLOSED BY之前添加 OPTIONALLY 来改变这种样式：

SELECT Id, Name, Email, Phone, City FROM employee_detail
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

导出带有列标题的数据

有时候我们希望导出的数据带有使文件更方便的列标题。如果CSV文件的第一行包含列标题，导出的文件会更易于理解。我们可以使用 UNION ALL 语句来添加列标题，如下所示：

SELECT 'Id', 'Name', 'Email', 'Phone', 'City'
UNION ALL
SELECT Id, Name, Email, Phone, City FROM employee_detail
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' 
FIELDS TERMINATED BY ';'
ENCLOSED BY '"' 
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

在这个查询中，我们可以看到我们为每个列名都添加了标题。我们可以通过导航到指定的 URL 来验证输出，其中第一行包含每个列的标题：

以CSV格式导出MySQL表

MySQL OUTFILE还允许我们在不指定任何列名的情况下导出表。我们可以使用以下语法将表导出为CSV文件格式：

TABLE employee_detail ORDER BY City LIMIT 1000
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv'
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

如果我们执行上述语句，我们的命令行工具将生成以下结果。它意味着指定的表包含六行，这些行被导出到 employee_backup.csv 文件中。

处理空值

有时结果集中的字段可能有NULL值，这时目标文件（导出文件类型）中将包含N而不是NULL。我们可以通过使用IFNULL函数将NULL值替换为”不适用（N/A）”来修复此问题。下面的语句更清楚地解释了这一点：

SELECT Name, Email, Phone, IFNULL(Phone, 'N/A') FROM employee_detail
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee_backup.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

使用MySQL Workbench将表格导出为CSV格式

如果我们不想访问数据库服务器来导出CSV文件，MySQL提供了另一种方法，即使用MySQL Workbench。 Workbench是一个图形用户界面工具，用于在不使用命令行工具的情况下处理MySQL数据库。它允许我们将语句的结果集导出到我们本地系统的CSV格式中。要做到这一点，我们需要按照以下步骤操作：

• 运行语句/查询并获取其结果集。
• 然后，在结果面板中点击 “将结果集导出到外部文件” 选项。其中结果集用于结果集。
• 最后，将显示一个新的对话框。在这里，我们需要提供一个文件名及其格式。填写完详细信息后，点击 保存 按钮。以下图片更清晰地解释了这一过程：

现在，我们可以通过导航到指定路径来验证结果。

本文链接：http://so.lmcjl.com/news/15774/