MySQL – SELECT * INTO OUTFILE LOCAL ?

Question

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

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

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

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

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

Answer 1

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

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

示例：

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

Answer 2

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

同时传入 -r (--raw) 选项可能是个好主意，这样特殊字符就不会被转义。您可以使用它来像您想要的那样管道查询。

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

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

Answer 3

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

至于 SELECT INTO OUTFILE 我不确定为什么没有本地版本，除此之外，通过连接进行操作可能会很棘手。您可以通过 mysqldump 工具获得相同的功能，但不能通过将 SQL 发送到服务器来获得。

Answer 4

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

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

“安装”mysql2csvvia

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

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

< strong>使用示例

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

生成文件/tmp/result.csv，其中内容

foo,bar
1,2

参考帮助

./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.

Answer 5

按照 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

Answer 6

尝试将路径设置为 /var/lib/mysql-files/filename.csv (MySQL 8)。通过在 mysql 客户端命令行中键入 SHOW VARIABLES LIKE "secure_file_priv"; 确定哪个文件目录是您的。

请参阅此处的答案：(...) MySQL 中的 --secure-file-priv 由 vhu 用户 于 2015 年回答

Answer 7

Re: SELECT * INTO OUTFILE

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