MySQL - SELECT * INTO OUTFILE LOCAL ?

Question

MySQL - SELECT * INTO OUTFILE LOCAL ?

48

MySQL很棒！我目前参与了一次重要的服务器迁移，之前我们的小型数据库在同一台服务器上托管给客户端。因此，我们过去是这样做的：SELECT * INTO OUTFILE .... LOAD DATA INFILE ....。

现在，我们将数据库迁移到了另一台服务器上，SELECT * INTO OUTFILE .... 不再起作用了，这是可以理解的，我认为是出于安全考虑。但是有趣的是，LOAD DATA INFILE .... 可以改成 LOAD DATA LOCAL INFILE ....，然后它就可以工作了。

我并不抱怨，也没有对MySQL表示不满。这个替代方案只增加了两行额外的代码和一个从.sql脚本调用系统调用。我想知道的只是为什么 LOAD DATA LOCAL INFILE 起作用，为什么没有 SELECT INTO OUTFILE LOCAL 这样的东西？

我做了功课，没有找到直接回答我的问题的答案。我在MySQL中也没有找到特性请求。如果有人能澄清一下，那就太好了！

MariaDB能处理这个问题吗？

- ThinkCode

有一种替代方法是使用tee命令，它允许您将mysql的输入和输出记录到客户端上的单独文件中，但是这会记录整个mysql会话，而不是输出选择查询内容，并且没有像输出CSV样式那样的格式选项。 - CMCDragonkai

7个回答

8

你可以在使用mysql console时，传递-s (--silent)选项来实现你想要的功能。

最好也传递-r (--raw)选项，这样特殊字符就不会被转义。您可以使用此选项来传递查询。

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

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

- Waverly360

5

LOAD DATA INFILE所指定的路径是服务器运行的文件系统路径，而不是客户端连接的本地机器路径。如果要使用LOAD DATA LOCAL INFILE，需要确认服务器已经启用了相应的设置，否则将无法使用该功能。详细信息请参考：http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html 至于SELECT INTO OUTFILE，我不确定为什么没有对应的本地版本，可能在连接上执行这个操作比较棘手。虽然不能通过向服务器发送SQL语句实现该功能，但可以通过mysqldump工具获得相同的功能。

- Theo

1

LOAD DATA 可以正常工作，但是在托管 DB 的服务器上转储文件是可行的，但无法将文件转储到位于不同服务器上的客户端。 - ThinkCode

1

mysqldump和mysql -e开关选项都没有达到预期的结果。mysqldump会转储整个数据库/表，我们无法根据条件仅转储几个列。mysql -e开关会将文件转储为.txt格式，并且无法实现管道输出。 - ThinkCode

4

由于我经常遇到这个确切的问题（希望之前我错过了什么...），所以最终决定花时间写一个小型Gist以将MySQL查询导出为CSV文件，有点像https://dev59.com/EnE85IYBdhLWcg3wQxLG#28168869，但基于PHP并具有更多选项。对于我的用例非常重要，因为我需要能够微调CSV参数（分隔符、NULL值处理）并且文件实际上需要是有效的CSV，因此简单的CONCAT是不够的，因为如果值包含换行符或CSV分隔符，则不会生成有效的CSV文件。

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

通过以下方式“安装”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

下载gist的内容，检查校验和并使其可执行。

用法示例

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

- Hirnhamster

因为第81行代码，需要使用PHP 7：$actualOption = $actualOptions[$option] ?? $actualOptions[$longopt] ?? $default; - hslakhan

3

使用 Waverly360 建议的 mysql CLI -e 选项是一个好方法，但在处理大量结果时可能会出现内存溢出并被杀死（尚未找到原因）。

如果是这种情况，并且需要所有记录，则我的解决方案是：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

- Vajk Hermecz

1

关于 SELECT * INTO OUTFILE

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

- Snowcrash

OUTFILE是用于向客户端服务器写入数据的，该服务器与托管数据库的服务器不同。 - ThinkCode

5

MySQL文档（http://dev.mysql.com/doc/refman/5.0/en/select.html）定义了OUTFILE的位置。它指出： "该文件在服务器主机上创建，因此您必须具有FILE特权才能使用此语法。file_name不能是现有文件，这可以防止诸如/etc/passwd和数据库表等文件被破坏。"因此，我会检查MySQL是否有权限将文件写入服务器上的OUTFILE目录。我不知道为什么没有“LOCAL OUTFILE”版本。如果您需要在本地导出，许多人使用phpMyAdmin作为一个选项。 - Snowcrash

1

尝试将路径设置为/var/lib/mysql-files/filename.csv（MySQL 8）。通过在mysql客户端命令行中输入SHOW VARIABLES LIKE "secure_file_priv";来确定您的文件目录。请参见关于此问题的答案：(...) --secure-file-priv in MySQL，由vhu user在2015年回答。

- Having a life on a beach

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jerrygarciuh · Accepted Answer

根据官方手册: 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