MySQL - SELECT * INTO OUTFILE LOCAL？

MySQL - SELECT * INTO OUTFILE LOCAL ?

提问by ThinkCode

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client.
So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE ....

MySQL 太棒了！我目前正在参与一项主要的服务器迁移，以前，我们的小型数据库曾经与客户端托管在同一台服务器上。
所以我们曾经这样做：SELECT * INTO OUTFILE .... LOAD DATA INFILE ....

Now, we moved the database to a different server and SELECT * INTO OUTFILE ....no longer works, understandable - security reasons I believe. But, interestingly LOAD DATA INFILE ....can be changed to LOAD DATA LOCAL INFILE ....and bam, it works.

现在，我们将数据库移到了不同​​的服务器上，并且SELECT * INTO OUTFILE ....不再工作，这是可以理解的 - 我相信是出于安全原因。但是，有趣的LOAD DATA INFILE ....是可以改为LOAD DATA LOCAL INFILE ....和 bam，它的工作原理。

I am not complaining nor am I expressing disgust towards MySQL. The alternative to that added 2 lines of extra code and a system call form a .sql script. All I wanted to know is why LOAD DATA LOCAL INFILEworks and why is there no such thing as SELECT INTO OUTFILE LOCAL?

我不是在抱怨，也不是对 MySQL 表示厌恶。替代方法是添加 2 行额外代码和一个系统调用，形成一个 .sql 脚本。我只想知道为什么LOAD DATA LOCAL INFILE有效，为什么没有这样的事情SELECT INTO OUTFILE LOCAL？

I did my homework, couldn't find a direct answer to my questions above. I couldn't find a feature request @ MySQL either. If someone can clear that up, that had be awesome!

我做了功课，找不到上述问题的直接答案。我也找不到@ MySQL 的功能请求。如果有人能解决这个问题，那就太棒了！

Is MariaDB capable of handling this problem?

MariaDB 有能力处理这个问题吗？

回答by jerrygarciuh

From the manual: The SELECT ... INTO OUTFILEstatement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_nameto generate the file on the client host."

来自手册：The SELECT ... INTO OUTFILE语句主要是为了让您非常快速地将表转储到服务器计算机上的文本文件中。如果要在服务器主机以外的某个客户端主机上创建结果文件，则不能使用SELECT ... INTO OUTFILE. 在这种情况下，您应该改为使用诸如mysql -e "SELECT ..." > file_name在客户端主机上生成文件的命令。”

http://dev.mysql.com/doc/refman/5.0/en/select.html

http://dev.mysql.com/doc/refman/5.0/en/select.html

An example:

一个例子：

mysql -h my.db.com -u usrname--password=pass db_name -e 'SELECT foo FROM bar' > /tmp/myfile.txt

回答by Waverly360

You can achieve what you want with the mysql console with the -s (--silent) option passed in.

您可以使用传入的 -s (--silent) 选项通过 mysql 控制台实现您想要的。

It's probably a good idea to also pass in the -r (--raw) option so that special characters don't get escaped. You can use this to pipe queries like you're wanting.

还传递 -r (--raw) 选项可能是一个好主意，这样特殊字符就不会被转义。您可以使用它来根据需要进行管道查询。

mysql -u username -h hostname -p -s -r -e "select concat('this',' ','works')"

mysql -u 用户名 -h 主机名 -p -s -r -e "select concat('this',' ','works')"

EDIT: Also, if you want to remove the column name from your output, just add another -s (mysql -ss -r etc.)

编辑：另外，如果你想从你的输出中删除列名，只需添加另一个 -s（mysql -ss -r 等）

回答by Theo

The path you give to LOAD DATA INFILEis for the filesystem on the machine where the server is running, not the machine you connect from. LOAD DATA LOCAL INFILEis for the client's machine, but it requires that the the server was started with the right settings, otherwise it's not allowed. You can read all about it here: http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html

您提供的路径LOAD DATA INFILE是用于运行服务器的机器上的文件系统，而不是您连接的机器。LOAD DATA LOCAL INFILE用于客户端的机器，但它要求服务器以正确的设置启动，否则不允许。您可以在此处阅读所有相关信息：http: //dev.mysql.com/doc/refman/5.0/en/load-data-local.html

As for SELECT INTO OUTFILEI'm not sure why there is not a local version, besides it probably being tricky to do over the connection. You can get the same functionality through the mysqldumptool, but not through sending SQL to the server.

至于SELECT INTO OUTFILE我不知道为什么没有本地版本，除了通过连接进行操作可能很棘手。您可以通过该mysqldump工具获得相同的功能，但不能通过向服务器发送 SQL。

回答by Vajk Hermecz

Using mysql CLI with -e option as Waverly360 suggests is a good one, but that might go out of memory and get killed on large results. (Havent find the reason behind it). If that is the case, and you need all records, my solution is: mysqldump + mysqldump2csv:

像 Waverly360 建议的那样使用带有 -e 选项的 mysql CLI 是一个不错的选择，但是这可能会耗尽内存并在大结果上被杀死。（还没有找到背后的原因）。如果是这种情况，并且您需要所有记录，我的解决方案是：mysqldump + mysqldump2csv：

wget https://raw.githubusercontent.com/jamesmishra/mysqldump-to-csv/master/mysqldump_to_csv.py
mysqldump -u username -p --host=hostname database table | python mysqldump_to_csv.py > table.csv

回答by Snowcrash

Re: SELECT * INTO OUTFILE

回复：SELECT * INTO OUTFILE

Check if MySQL has permissions to write a file to the OUTFILE directory on the server.

检查 MySQL 是否有权将文件写入服务器上的 OUTFILE 目录。

回答by Hirnhamster

Since I find myself rather regularly looking for this exact problem (in the hopes I missed something before...), I finally decided to take the time and write up a small gist to export MySQL queries as CSV files, kinda like https://stackoverflow.com/a/28168869but based on PHP and with a couple of more options. This was important for my use case, because I need to be able to fine-tune the CSV parameters (delimiter, NULL value handling) AND the files need to be actually valid CSV, so that a simple CONCATis not sufficient since it doesn't generate valid CSV files if the values contain line breaks or the CSV delimiter.

因为我发现自己经常寻找这个确切的问题（希望我之前错过了一些东西......），我最终决定花时间写一个小要点，将 MySQL 查询导出为 CSV 文件，有点像https:/ /stackoverflow.com/a/28168869但基于 PHP 和更多选项。这对我的用例很重要，因为我需要能够微调 CSV 参数（分隔符、NULL 值处理）并且文件需要是实际有效的 CSV，所以简单CONCAT是不够的，因为它没有如果值包含换行符或 CSV 分隔符，则生成有效的 CSV 文件。

Caution: Requires PHP to be installed on the server!(Can be checked via php -v)

注意：需要在服务器上安装 PHP！（可以通过php -v）

"Install" mysql2csvvia

“安装”mysql2csv通过

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

(download content of the gist, check checksum and make it executable)

（下载要点内容，检查校验和并使其可执行）

Usage example

使用示例

./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

generates file /tmp/result.csvwith content

生成/tmp/result.csv包含内容的文件

foo,bar
1,2

help for reference

帮助参考

./mysql2csv --help
Helper command to export data for an arbitrary mysql query into a CSV file.
Especially helpful if the use of "SELECT ... INTO OUTFILE" is not an option, e.g.
because the mysql server is running on a remote host.

Usage example:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

cat /tmp/result.csv

Options:
        -q,--query=name [required]
                The query string to extract data from mysql.
        -h,--host=name
                (Default: 127.0.0.1) The hostname of the mysql server.
        -D,--database=name
                The default database.
        -P,--port=name
                (Default: 3306) The port of the mysql server.
        -u,--user=name
                The username to connect to the mysql server.
        -p,--password=name
                The password to connect to the mysql server.
        -F,--file=name
                (Default: php://stdout) The filename to export the query result to ('php://stdout' prints to console).
        -L,--delimiter=name
                (Default: ,) The CSV delimiter.
        -C,--enclosure=name
                (Default: ") The CSV enclosure (that is used to enclose values that contain special characters).
        -E,--escape=name
                (Default: \) The CSV escape character.
        -N,--null=name
                (Default: \N) The value that is used to replace NULL values in the CSV file.
        -H,--header=name
                (Default: 1) If '0', the resulting CSV file does not contain headers.
        --help
                Prints the help for this command.