mysql 导出csv带边框_MySQL 导出为CSV

MySQL 导出为CSV

MySQL具有将表导出到CSV文件的功能。 CSV文件格式是逗号分隔的值，我们可以使用它在各种应用程序(例如 Microsoft Excel ，Goole Docs)之间交换数据，然后打开办公室。具有 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';;

从上面开始，

行以'，'结尾: 用于指示文件中由逗号运算符终止的行。每行包含文件中每一列的数据。

文件用'"'括起来的字段: 用于指定文件字段，并用双引号引起来。

导出的文件的存储位置

存储空间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 intNOTnull AUTO_INCREMENT,

Name varchar(45) defaultNULL,

Email varchar(45) defaultNULL,

Phone varchar(15) defaultNULL,

City varchar(25) defaultNULL,

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@lidihuo.com', '49562959223', 'Texas'),

(2, 'Suzi', 'suzi@lidihuo.com', '70679834522', 'California'),

(3, 'Joseph', 'joseph@lidihuo.com', '09896765374', 'Alaska'),

(4, 'Alex', 'alex@lidihuo.com', '97335737548', 'Los Angeles'),

(5, 'Mark', 'mark@lidihuo.com', '78765645643', 'Washington'),

(6, 'Stephen', 'stephen@lidihuo.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函数将

值替换为

"不适用(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数据库一起使用的GUI工具。它允许我们在本地系统中将语句的结果集导出为CSV格式。为此，我们需要执行以下步骤:

运行语句/查询并获取其结果集。

然后，在结果面板中，单击"将记录集导出到外部文件"选项。记录集用于结果集。

最后，将显示一个新对话框。在这里，我们需要提供文件名及其格式。填写完详细信息后，点击保存按钮。下图更清楚地说明了这一点:

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