在 MySQL 中找不到 outfile 创建的文件 [英] Can't find the file created by outfile in MySQL

问题描述

我正在使用以下查询来创建 CSV 文件

I am using the following query to create a CSV file

SELECT email INTO OUTFILE "mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "
"
FROM users;

但是当我搜索 filezilla 时，我无法在任何地方找到 mydata.csv 文件.

But i am unable to find the mydata.csv file anywhere when i am searching through filezilla.

知道这个文件的存储位置吗?

Any idea where this file is getting stored?

查询运行成功，没有任何错误！有什么帮助吗?

The query runs successfully without any errors! Any Help?

推荐答案

MySQL 可能正在将文件写入自己的数据目录，例如 /var/lib/mysql/.要指定路径，请使用完整路径.

MySQL may be writing the file into its own data directory, like /var/lib/mysql/<databasename> for example. To specify the path, use a full path.

但是，它必须是运行 MySQL 服务器守护程序的用户帐户可写的目录.出于这个原因，我会经常使用 /tmp:

However, it must be a directory that is writable by the user account the MySQL server daemon is running under. For that reason, I'll often use /tmp:

指定要写入的路径，如下所示:

Specify the path you want to write to as in:

INTO OUTFILE '/tmp/mydata.csv'

请注意，MySQL 会将文件写入 MySQL 服务器，而不是您的客户端计算机上.因此远程连接将在远程服务器上创建输出文件.另请参阅SELECT INTO OUTFILE local ?了解更多详情和解决方法.

And note that MySQL will write the file on the MySQL server, not on your client machine. Therefore remote connections will create output files on the remote server. See also SELECT INTO OUTFILE local ? for more details and workarounds.

关于在运行 systemd 的 Linux 系统上写入 /tmp 的注意事项:

最初发布此内容几年后，我发现自己无法通过

Some years after originally posting this, I found myself unable to locate a file written to /tmp via

...INTO OUTFILE '/tmp/outfile.csv'

在运行 Fedora Linux 和 systemd 的 MariaDB 5.5 服务器上.不是将文件直接写入指定的 /tmp/outfile.csv，而是在 /tmp 中的 systemd 目录下创建该目录和文件:

on a MariaDB 5.5 server running Fedora Linux with systemd. Instead of writing the file directly to /tmp/outfile.csv as specified, that directory and file were created beneath a systemd directory in /tmp:

/tmp/systemd-mariadb.service-XXXXXXX/tmp/outfile.csv

虽然文件 outfile.csv 本身和 tmp/ 子目录都是可写的，但 systemd 服务目录本身有 700 个权限并且是 root 拥有的，需要 sudo 访问权限以检索其中的文件.

While the file outfile.csv itself and the tmp/ subdirectory were both created world-writable, the systemd service directory itself has 700 permissions and is root-owned, requiring sudo access to retrieve the file within it.

不是将 MariaDB 中的绝对路径指定为 /tmp/outfile.csv 并将其相对指定为 outfile.csv，而是按预期将文件写入 MariaDB 的数据中当前所选数据库的目录.

Rather than specifying the absolute path in MariaDB as /tmp/outfile.csv and specifying it relatively as outfile.csv, the file was written as expected into MariaDB's data directory for the currently selected database.

这篇关于在 MySQL 中找不到 outfile 创建的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！