如何将 MySQL 查询输出保存到文件？

您是否需要将MySQL查询的结果保存到CSV或文本文件中?

这在MySQL中很容易做到。您可以使用IDE或命令行，使用内置的MySQL命令来完成此操作。

让我们来看看。

基本查询

让我们为此使用一个SELECT语句的简单示例。

SELECT id, first_name, last_name
FROM customer;

这是结果:

id	first_name	last_name
1	John	Smith
2	Mary	McAdams
3	Steve	Pitt
4	Mark	Cousins
5	Shaun	Jones
7	Amy	McDonald
8	Brad	Swan
10	Wendy	Johnson

我们可以在命令行或IDE(如MySQL Workbench)的输出中看到这些结果。

如何将它们保存到文本文件中?

我们可以复制粘贴它们，但那样很慢，而且需要手动操作。

将MySQL结果保存到一个文件

在SELECT语句中有一个内置的MySQL输出到文件特性。

我们只需在SELECT语句的末尾添加INTO OUTFILE字样，后面跟着一个文件名。

例如:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt';

这将在名为temp的文件夹中创建一个名为myoutput.txt的新文件，其中包含该查询的结果。

如果您得到这个错误(像我一样)会发生什么?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

我将在本指南后面详细解释如何解决这个问题。

现在，假设你可以运行这个语句来生成文件，它看起来像这样:

文件中的文本是:

1 John Smith
2 Mary McAdams
3 Steve Pitt
4 Mark Cousins
5 Shaun Jones
7 Amy McDonald
8 Brad Swan
10 Wendy Johnson

如您所见，字段由选项卡分隔。这是默认的行为，但可以更改。

更改参数以设置逗号分隔值

还有几个额外的参数。下面是一些最常见的:

FIELDS TERMINATED BY: 表示用于结束字段的字符。

ENCLOSED BY: 表示将用于围合每个字段的字符。

LINES TERMINATED BY :表示用于结束一行和开始新一行的字符。

例如，要选择CSV文件中的数据并将每个字段用双引号括起来:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

如果我们运行这条语句，我们可以检查文件，它看起来像这样:

文件中的文本是:

"1","John","Smith"
"2","Mary","McAdams"
"3","Steve","Pitt"
"4","Mark","Cousins"
"5","Shaun","Jones"
"7","Amy","McDonald"
"8","Brad","Swan"
"10","Wendy","Johnson"

这就是如何在MySQL中生成CSV或文本文件。您只需将INTO OUTFILE关键字添加到SELECT查询的末尾并指定一些参数。

在输出文件中包含标题

SELECT 'id', 'first_name', 'last_name'
UNION ALL
SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

这意味着你的列标题将显示在文件中:

"id","first_name","last_name"
"1","John","Smith"
"2","Mary","McAdams"
"3","Steve","Pitt"
"4","Mark","Cousins"
"5","Shaun","Jones"
"7","Amy","McDonald"
"8","Brad","Swan"
"10","Wendy","Johnson"

然而，这也存在一些问题:

数据类型

排序会有问题吗

如果查询包含ORDER BY子句，则列标题将无法正确显示。

这是因为ORDER BY子句位于查询的末尾，并将在排序中包含列标题行。这可能意味着您的列标题将在结果中的一个不是顶部的位置结束。

您可以通过将主查询放在子查询中，然后在子查询上使用UNION来解决这个问题。

例如：

SELECT *
  SELECT 'id', 'first_name', 'last_name'
  UNION ALL
    SELECT id, first_name, last_name
    FROM customer
    ORDER BY first_name ASC
) sub
INTO OUTFILE '/temp/myoutput.txt';

这应该确保列标题显示在顶部。

可能的性能问题

如果您尝试使用UNION或UNION ALL, MySQL数据库可能会尝试使用不同的执行计划来显示数据，即使您只添加了一行。

这可能意味着运行时要慢得多。 所以，在你开始使用它作为永久解决方案之前，你应该进行测试。

如果文件已经存在怎么办?

那么，如果您尝试此命令，而该文件已经存在，会发生什么情况呢?

例如，假设存在myoutput.txt文件，运行以下命令:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/temp/myoutput.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

你会收到这条消息:

Error Code: 1086. File ‘/temp/myoutput.txt’ already exists

这很明显。该文件已经存在。因此，您需要使用一个不存在的文件名。

Secure-File-Priv错误

你运行这个SELECT到OUTFILE和得到一个关于“secure-file-priv”的错误吗?

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

这本质上意味着MySQL有一组可用于输入和输出的特定目录，而我们试图写入的文件不在该目录中。

为了解决这个问题，我们需要:

1、找出具体的目录是什么

2、将这个目录路径添加到OUTFILE参数中，这样我们的文件就会在那里生成。

为了找到目录的位置，我们可以运行这两个命令中的任何一个:

SELECT @@GLOBAL.secure_file_priv;

@@GLOBAL.secure_file_priv

/usr/files/

SHOW VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	/usr/files/

本例中的输出显示值为' /usr/files/ '。

因此，您所需要做的就是将此路径添加到OUTFILE路径的开头。

你的SELECT语句看起来像这样:

SELECT id, first_name, last_name
FROM customer
INTO OUTFILE '/usr/files/temp/customer.txt'

Secure-File-Priv为NULL

如果出现上述错误，并查找secure_file_priv变量，您可能会发现它是NULL。这种情况最常发生在带有MAMP的MacOS上。

SHOW VARIABLES LIKE "secure_file_priv";

Variable_name	Value
secure_file_priv	NULL

为了解决这个问题，我们需要将这个变量添加到my.cnf文件中。

首先，通过打开MAMP并单击stop来停止MAMP服务器。

打开终端窗口。

输入以下命令:

vi ~/.my.cnf

您的屏幕应该如下所示。

按“Enter”执行命令。这将打开my.cnf文件的编辑器。

复制以下行到终端窗口:

[mysqld_safe]