MySQL 将表从 Amazon RDS 导出到 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9536224/
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
Exporting table from Amazon RDS into a csv file
提问by Kenny
I have a mysql database running in Amazon RDS, and I want to know how to export an entire table to csv format. 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 any solution to this?
我有一个在 Amazon RDS 中运行的 mysql 数据库，我想知道如何将整个表导出为 csv 格式。我目前在 Windows 上使用 mysql 服务器来查询 Amazon 数据库，但是当我尝试运行导出时出现错误，可能是因为没有用于 amazon RDS 的专用文件服务器。有什么解决办法吗？
回答by Steffen Opel
Presumably you are trying to export from an Amazon RDSdatabase via a SELECT ... INTO OUTFILEquery, which yields this indeed commonly encountered issue, see e.g. export database to CSV. The respective AWS team responseconfirms 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:
据推测，您正在尝试通过查询从Amazon RDS数据库导出SELECT ... INTO OUTFILE，这会产生这个确实经常遇到的问题，请参阅例如export database to CSV。相应的AWS 团队响应确认了您的假设，即缺乏服务器访问阻止了这样的导出，并建议了另一种方法，通过在 mysql 命令行客户端中选择数据并通过管道输出重新格式化数据，以 CSV 格式导出数据作为 CSV，像这样：
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
User fpalero provides an alternativeand supposedly simpler approach, if you know and specify the fields upfront:
如果您预先知道并指定字段，用户 fpalero提供了一种替代且据称更简单的方法：
mysql -uroot -ppassword --database=dbtest
-e "select concat(field1,',',field2,',',field3) FROM tabletest" > tabletest.csv
Good luck!
祝你好运！
回答by Gab
First of all, Steffen's answer works in most cases, I up-voted it and I have myself used it for several years.
首先，Steffen 的答案在大多数情况下都有效，我对它投了赞成票，而且我自己已经使用了几年。
I recently encountered some larger and more complex outputs where "sed" was not enough and decided to come up with a simple utility to do exactly that.
我最近遇到了一些更大、更复杂的输出，其中“sed”是不够的，并决定想出一个简单的实用程序来做到这一点。
I build a module called sql2csv that can parse the output of the MySQL CLI:
我构建了一个名为 sql2csv 的模块，它可以解析 MySQL CLI 的输出：
$ mysql my_db -e "SELECT * FROM some_mysql_table"
+----+----------+-------------+---------------------+
| id | some_int | some_str | some_date |
+----+----------+-------------+---------------------+
| 1 | 12 | hello world | 2018-12-01 12:23:12 |
| 2 | 15 | hello | 2018-12-05 12:18:12 |
| 3 | 18 | world | 2018-12-08 12:17:12 |
+----+----------+-------------+---------------------+
$ mysql my_db -e "SELECT * FROM some_mysql_table" | sql2csv
id,some_int,some_str,some_date
1,12,hello world,2018-12-01 12:23:12
2,15,hello,2018-12-05 12:18:12
3,18,world,2018-12-08 12:17:12
You can also use the built in CLI:
您还可以使用内置的 CLI：
sql2csv -u root -p "secret" -d my_db --query "SELECT * FROM some_mysql_table;"
1,12,hello world,2018-12-01 12:23:12
2,15,hello,2018-12-05 12:18:12
3,18,world,2018-12-08 12:17:12
More info https://github.com/gabfl/sql2csv
回答by AndyB
Assuming MySQL in RDS, an alternative is to use batch mode which outputs TAB-separated values and escapes newlines, tabs and other special characters. I haven't yet struck a CSV import tool that can't handle TAB-separated data. So for example:
假设 RDS 中的 MySQL，另一种方法是使用批处理模式，该模式输出 TAB 分隔值并转义换行符、制表符和其他特殊字符。我还没有遇到无法处理制表符分隔数据的 CSV 导入工具。例如：
$ mysql -h myhost.rds.amazonaws.com -u user -D my_database -p --batch --quick -e "SELECT * FROM my_table" > output.csv
As noted by Halfgaar above, the --quickoption flushes immediately so avoids out-of-memory errors for large tables. To quote strings (recommended), you'll need to do a bit of extra work in your query:
正如上面的 Halfgaar 所指出的，该--quick选项会立即刷新，从而避免大表的内存不足错误。要引用字符串（推荐），您需要在查询中做一些额外的工作：
SELECT id, CONCAT('"', REPLACE(text_column, '"', '""'), '"'), float_column
FROM my_table
The REPLACEescapes any double-quote characters in the text_columnvalues. I would also suggest using iso8601 strings for datetime fields, so:
该REPLACE逃逸的任何双引号字符text_column值。我还建议对日期时间字段使用 iso8601 字符串，因此：
SELECT CONCAT('"', DATE_FORMAT(datetime_column, '%Y%m%dT%T'), '"') FROM my_table
Be aware that CONCAT returns NULL if you have a NULL column value.
请注意，如果您有一个 NULL 列值，CONCAT 将返回 NULL。
I've run this on some fairly large tables with reasonable performance. 600M rows and 23GB data took ~30 minutes when running the mysql command in the same VPC as the RDS instance.
我已经在一些性能合理的相当大的表上运行了它。在与 RDS 实例相同的 VPC 中运行 mysql 命令时，600M 行和 23GB 数据需要大约 30 分钟。
回答by Vladimir Gilevich
There is new way from AWS how to do it. Just use their DMS(Database Migration Service).
AWS 提供了一种新的方法来做到这一点。只需使用他们的 DMS（数据库迁移服务）。
Here is documentation how to export table(s) to files on S3 storage: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html
以下是如何将表导出到 S3 存储上的文件的文档：https: //docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.S3.html
You will have possibility to export in 2 formats: CSV or parquet.
您将有可能以 2 种格式导出：CSV 或镶木地板。
回答by Donato
If you use the solution marked as correct, you'll notice that it generates a header that includes the 'concat' string literal. Obviously this is not what you want. Most likely you will want the corresponding headers of your data. This query will work without any modifications, other than substituting column names and table names:
如果您使用标记为正确的解决方案，您会注意到它会生成一个包含“concat”字符串文字的标头。显然这不是你想要的。您很可能需要相应的数据标题。除了替换列名和表名外，此查询无需任何修改即可工作：
mysql -h xxx.xxx.us-east-2.rds.amazonaws.com
--database=mydb -u admin -p
-e "SELECT 'column1','column2'
UNION ALL SELECT column1,column2
FROM table_name WHERE condition = value" > dataset.csv
I just opened the results in the Numbers osx app and the output looks perfect.
我刚刚在 Numbers osx 应用程序中打开了结果，输出看起来很完美。
回答by user2700214
I'm using Yii Framework on EC2 connecting to RDS mySQL. The key is to use fputcsv(). The following works perfectly, both on my localhost as well as production.
我在连接到 RDS mySQL 的 EC2 上使用 Yii 框架。关键是使用 fputcsv()。以下工作完美，无论是在我的本地主机上还是在生产中。
$file = 'path/to/filename.csv';
$export_csv = "SELECT * FROM table";
$qry = Yii::app()->db->createCommand($export_csv)->queryAll();
$fh = fopen($file, "w+");
foreach ($qry as $row) {
fputcsv($fh, $row, ',' , '"');
}
fclose ($fh);
