MySQL教程
 MySQL 导入CSV文件到表 MySQL 子查询 

MySQL 导出表数据到CSV文件

MySQL 具有将表导出到 CSV 文件的功能。CSV 文件格式是一个逗号分隔值，我们用于在Microsoft Excel、Goole Docs 和 Open Office等各种应用程序之间交换数据。以 CSV 文件格式保存 MySQL 数据很有用，它允许我们以我们想要的方式分析和格式化它们。它是一个纯文本文件，可以帮助我们非常轻松地导出数据。

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

我们还可以将此语法与 values 语句一起使用，将数据直接导出到文件中。以下语句更清楚地解释了它：

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 XY。

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

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

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 语句以 CSV 格式导出 MySQL 数据

要将表数据导出为 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 是一个 GUI 工具，可以在不使用命令行工具的情况下使用 MySQL 数据库。它允许我们在本地系统中将语句的结果集导出为 CSV 格式。为此，我们需要执行以下步骤：

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

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

 MySQL 导入CSV文件到表 MySQL 子查询 

热门文章

优秀文章

全部教程