MySQL 如何以CSV格式输出MySQL查询结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/356578/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to output MySQL query results in CSV format?
提问by MCS
Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?
有没有一种简单的方法可以从 Linux 命令行运行 MySQL 查询并以CSV格式输出结果？
Here's what I'm doing now:
这是我现在正在做的事情：
mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ
It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.
当有很多列需要用引号括起来，或者结果中有引号需要转义时，它会变得混乱。
回答by Paul Tomblin
From http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/
来自http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Using this command columns names will not be exported.
使用此命令不会导出列名称。
Also note that /var/lib/mysql-files/orders.csvwill be on the serverthat is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.
另请注意，/var/lib/mysql-files/orders.csv它将位于运行 MySQL的服务器上。运行 MySQL 进程的用户必须具有写入所选目录的权限，否则命令将失败。
If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.
如果您想从远程服务器（尤其是托管或虚拟机，如 Heroku 或 Amazon RDS）将输出写入本地计算机，则此解决方案不合适。
回答by Stan
$ mysql your_database --password=foo < my_requests.sql > out.csv
Which is tab separated. Pipe it like that to get a true CSV (thanks @therefromhere):
哪个是制表符分隔的。像这样管道以获得真正的CSV（感谢@therefromhere）：
... .sql | sed 's/\t/,/g' > out.csv
回答by serbaut
mysql --batch, -B
Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in non-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.
mysql --batch, -B
使用制表符作为列分隔符打印结果，每行换一行。使用此选项，mysql 不使用历史文件。批处理模式导致非表格输出格式和特殊字符的转义。可以使用原始模式禁用转义；请参阅 --raw 选项的说明。
This will give you a tab separated file. Since commas (or strings containing comma) are not escaped it is not straightforward to change the delimiter to comma.
这将为您提供一个制表符分隔的文件。由于逗号（或包含逗号的字符串）不会被转义，因此将分隔符更改为逗号并不简单。
回答by Tim Harding
Here's a fairly gnarly way of doing it. Found it somewhere, can't take any credit
这是一种相当粗糙的方法。在某处找到它，不能相信任何信用
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
Works pretty well. Once again though a regex proves write only.
工作得很好。虽然正则表达式再次证明只写。
Regex Explanation:
正则表达式说明：
- s/// means substitute what's between the first // with what's between the second //
- the "g" at the end is a modifier that means "all instance, not just first"
- ^ (in this context) means beginning of line
- $ (in this context) means end of line
- s/// 表示将第一个 // 之间的内容替换为第二个 // 之间的内容
- 最后的“g”是一个修饰符，意思是“所有实例，而不仅仅是第一个”
- ^（在这种情况下）表示行的开头
- $（在这种情况下）表示行尾
So, putting it all together:
所以，把它们放在一起：
s/'/\'/ replace ' with \'
s/\t/\",\"/g replace all \t (tab) with ","
s/^/\"/ at the beginning of the line place a "
s/$/\"/ at the end of the line place a "
s/\n//g replace all \n (newline) with nothing
回答by strickli
回答by hrvoj3e
This saved me a couple of times. Fast and it works!
这救了我几次。快速而且有效！
--batchPrint results using tabas the column separator, with each row on a new line.
--rawdisables character escaping (\n, \t, \0, and \)
--batch使用制表符作为列分隔符打印结果，每行换一行。
--raw禁用字符转义（\n、\t、\0 和 \）
Example:
例子：
mysql -udemo_user -p -h127.0.0.1 --port=3306 \
--default-character-set=utf8mb4 --database=demo_database \
--batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv
For completeness you could convert to csv(but be carefulbecause tabs could be inside field values - e.g. text fields)
为了完整起见，您可以转换为 csv（但要小心，因为选项卡可能位于字段值内 - 例如文本字段）
tr '\t' ',' < file.tsv > file.csv
tr '\t' ',' < file.tsv > file.csv
回答by Leland Woodbury
The OUTFILE solution given by Paul Tomblin causes a file to be written on the MySQL server itself, so this will work only if you have FILEaccess, as well as login access or other means for retrieving the file from that box.
Paul Tomblin 给出的 OUTFILE 解决方案导致将文件写入 MySQL 服务器本身，因此只有当您具有FILE访问权限以及登录访问权限或从该框中检索文件的其他方法时，这才有效。
If you don't have such access, and tab-delimited output is a reasonable substitute for CSV (e.g., if your end goal is to import to Excel), then Serbaut's solution (using mysql --batchand optionally --raw) is the way to go.
如果您没有这样的访问权限，并且制表符分隔的输出是 CSV 的合理替代品（例如，如果您的最终目标是导入到 Excel），那么 Serbaut 的解决方案（使用mysql --batch和可选--raw）就是要走的路。
回答by David Oliver
MySQL Workbenchcan export recordsets to CSV, and it seems to handle commas in fields very well. The CSV opens up in OpenOffice fine.
MySQL Workbench可以将记录集导出为 CSV，而且它似乎很好地处理了字段中的逗号。CSV 可以在 OpenOffice 中正常打开。
回答by Steve
How about:
怎么样：
mysql your_database -p < my_requests.sql | awk '{print ","}' > out.csv
回答by mc0e
All of the solutions here to date, except the MySQL workbench one, are incorrect and quite possibly unsafe (ie security issues) for at least some possible content in the mysql db.
迄今为止，这里的所有解决方案，除了 MySQL 工作台之外，对于 mysql 数据库中的至少一些可能的内容来说，都是不正确的，并且很可能是不安全的（即安全问题）。
MYSQL Workbench (and similarly PHPMyAdmin) provide a formally correct solution, but are designed for downloading the output to a user's location. They're not so useful for things like automating data export.
MYSQL Workbench（以及类似的 PHPMyAdmin）提供了一种形式上正确的解决方案，但旨在将输出下载到用户的位置。它们对于自动化数据导出之类的事情不是很有用。
It is not possible to generate reliably correct csv from the output of mysql -B -e 'SELECT ...'because that cannot encode carriage returns and white space in fields. The '-s' flag to mysql does do backslash escaping, and might lead to a correct solution. However, using a scripting language (one with decent internal data structures that is, not bash), and libraries where the encoding issues have already been carefully worked out is far safer.
不可能从输出中生成可靠正确的 csv，mysql -B -e 'SELECT ...'因为它无法对字段中的回车和空格进行编码。mysql 的“-s”标志确实会进行反斜杠转义，并且可能会导致正确的解决方案。但是，使用脚本语言（具有良好的内部数据结构，而不是 bash）和已经仔细解决编码问题的库要安全得多。
I thought about writing a script for this, but as soon as I thought about what I'd call it, it occurred to me to search for pre-existing work by the same name. While I haven't gone over it thoroughly, the solution at https://github.com/robmiller/mysql2csvlooks promising. Depending on your application, the yaml approach to specifying the SQL commands might or might not appeal though. I'm also not thrilled with the requirement for a more recent version of ruby than comes as standard with my Ubuntu 12.04 laptop or Debian Squeeze servers. Yes I know I could use RVM, but I'd rather not maintain that for such a simple purpose.
我想为此写一个脚本，但一想到我会怎么称呼它，我就想到要搜索同名的预先存在的作品。虽然我还没有彻底了解它，但https://github.com/robmiller/mysql2csv 上的解决方案看起来很有希望。根据您的应用程序，指定 SQL 命令的 yaml 方法可能会也可能不会吸引人。我也对需要比我的 Ubuntu 12.04 笔记本电脑或 Debian Squeeze 服务器标准的更新版本的 ruby 更不兴奋。是的，我知道我可以使用 RVM，但我不想为了这么简单的目的而维护它。
Hopefully someone will point out a suitable tool, that's had a bit of testing. Otherwise I'll probably update this when I find or write one.
希望有人会指出一个合适的工具，这已经过一些测试。否则，当我找到或写一个时，我可能会更新它。
