問題描述
我有一個在 Amazon RDS 中運(yùn)行的 MySQL 數(shù)據(jù)庫，我想知道如何將整個表格導(dǎo)出為 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 服務(wù)器來查詢 Amazon 數(shù)據(jù)庫，但是當(dāng)我嘗試運(yùn)行導(dǎo)出時出現(xiàn)錯誤，可能是因?yàn)闆]有用于 amazon RDS 的專用文件服務(wù)器.有沒有解決辦法?
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?
推薦答案
據(jù)推測，您正在嘗試從 Amazon RDS 數(shù)據(jù)庫通過 SELECT ... INTO OUTFILE 查詢，這會產(chǎn)生這個確實(shí)經(jīng)常遇到的問題，參見例如將數(shù)據(jù)庫導(dǎo)出為 CSV.相應(yīng)的 AWS 團(tuán)隊(duì)回復(fù)證實(shí)了您對缺乏服務(wù)器訪問權(quán)限的假設(shè)防止像這樣導(dǎo)出，并建議一種替代方法，通過在 MySQL 命令行客戶端中選擇數(shù)據(jù)并通過管道將輸出以 CSV 格式導(dǎo)出您的數(shù)據(jù)以將數(shù)據(jù)重新格式化為 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 提供了一種替代方法，據(jù)說更簡單，如果您知道并預(yù)先指定字段:
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
這篇關(guān)于將 Amazon RDS 中的表導(dǎo)出為 CSV 文件的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！