将 Amazon RDS 中的表导出为 CSV 文件 [英] Exporting a table from Amazon RDS into a CSV file

问题描述

我有一个在 Amazon RDS 中运行的 MySQL 数据库，我想知道如何将整个表格导出为 CSV 格式.

I have a MySQL database running in Amazon RDS, and I want to know how to export an entire table to CSV format.

我目前在 Windows 上使用 MySQL 服务器来查询 Amazon 数据库，但是当我尝试运行导出时出现错误，可能是因为没有用于 amazon RDS 的专用文件服务器.有什么解决办法吗?

I currently use MySQL server on Windows to query the Amazon database, but when I try to run an export I get an error, probably because there's no dedicated file server for amazon RDS. Is there a solution to this?

推荐答案

据推测，您正在尝试从 Amazon RDS 数据库通过 SELECT ... INTO OUTFILE 查询，这会产生这个确实经常遇到的问题，参见例如将数据库导出为 CSV.相应的 AWS 团队回复 证实了您对缺乏服务器访问权限的假设防止像这样导出，并建议另一种方法，通过在 MySQL 命令行客户端中选择数据并通过管道将输出以 CSV 格式导出您的数据以将数据重新格式化为 CSV，像这样:

Presumably, you are trying to export from an Amazon RDS database via a SELECT ... INTO OUTFILE query, which yields this indeed commonly encountered issue, see e.g. export database to CSV. The respective AWS team response confirms your assumption of lacking server access preventing an export like so, and suggests an alternative approach as well via exporting your data in CSV format by selecting the data in the MySQL command line client and piping the output to reformat the data as CSV, like so:

mysql -u username -p --database=dbname --host=rdshostname --port=rdsport --batch
  -e "select * from yourtable"
  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename

用户 fpalero 提供了一种替代方法，据说更简单, 如果您知道并预先指定字段:

User fpalero provides an alternative and supposedly simpler approach, if you know and specify the fields upfront:

mysql -uroot -ppassword --database=dbtest
  -e "select concat(field1,',',field2,',',field3) FROM tabletest" > tabletest.csv

这篇关于将 Amazon RDS 中的表导出为 CSV 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！