BASH MySQL 查询到逗号分隔文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1452336/
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
BASH MySQL Query to Comma Separated File
提问by BassKozz
I have the following bash script:
我有以下 bash 脚本：
#!/bin/sh
MYSQLHOST="mysql.remote-host.com"
MYSQLDB="mysqldb"
MYSQLTABLE="table"
MYSQLUSER="user"
MYSQLPASS="pass"
MYSQLDUMP="Report/report.csv"
LOG="Report/report.log"
MYSQLOPTS="--user=${MYSQLUSER} --password=${MYSQLPASS} --host=${MYSQLHOST} ${MYSQLDB}"
echo "Report Begin: $(date)"
echo "MySQL Dump Begin: $(date)" >> ${LOG}
echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP}
echo "MySQL Dump End: $(date)" >> ${LOG}
echo "Report Successful: $(date)"
This ouputs my MySQL Query into a "TAB" separated file report.csv. However, I need to have it output to a "COMMA" separated file. I realize I could create another script to convert this file from TAB to COMMA separated, however, I'd rather save the step if I can. So how can I have MySQL dump the file in comma separated format?
这将我的 MySQL 查询输出到“ TAB”分隔的文件report.csv 中。但是，我需要将它输出到“逗号”分隔的文件中。我意识到我可以创建另一个脚本来将此文件从 TAB 转换为逗号分隔，但是，如果可以，我宁愿保存该步骤。那么我怎样才能让 MySQL 以逗号分隔的格式转储文件呢？
EDIT: I did find this solution: How do you output MySQL query results in csv format (to the screen, not to a file)?
编辑：我确实找到了这个解决方案：你如何以 csv 格式（到屏幕，而不是文件）输出 MySQL 查询结果？
However I can't seem to get it to work:
但是我似乎无法让它工作：
echo "SELECT CONCAT_WS(',', ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options) FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} > ${MYSQLDUMP}
Doesn't work :(
不起作用:(
采纳答案by BassKozz
Found a simple perl 1-liner that seems to do the trick:
发现一个简单的 perl 1-liner 似乎可以解决问题：
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F '
- Perl 脚本
So the line in my script looks like this:
所以我的脚本中的行看起来像这样：
echo "SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | mysql ${MYSQLOPTS} | perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' > ${MYSQLDUMP}
Thanks for all the help, Especially from Bill Karwin, I wanted to add a VoteUp to your answer but I don't have enough reputation :( Once I get more rep, I'll circle back and vote your answer up. Thanks again.
感谢所有的帮助，尤其是 Bill Karwin，我想为您的答案添加一个投票，但我没有足够的声望 :( 一旦我获得更多代表，我会回圈并投票支持您的答案。再次感谢。
回答by David Rabinowitz
Run the following:
运行以下命令：
echo "SELECT CONCAT_WS(',', ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options) FROM ${MYSQLTABLE} WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at" | \
mysql ${MYSQLOPTS} | tr '\t' ',' > ${MYSQLDUMP}
The trcommand replaces one char with another.
该tr命令将一个字符替换为另一个字符。
回答by Bill Karwin
Here's an exampleSQL query that outputs to a comma-separated file:
这是输出到逗号分隔文件的示例SQL 查询：
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
Re comments: Fair enough, the INTO OUTFILEsyntax writes to a file on the database server host, not the host where the shell script and the mysql client is running.
回复评论：很公平，INTO OUTFILE语法写入数据库服务器主机上的文件，而不是运行 shell 脚本和 mysql 客户端的主机。
The mysql client doesn't have a csv output mode, it only has the bars-and-dashes tabular format (with the --tableor -tflags), tab-separated format (with the --batchor -Bflags), HTML format (--htmlor -H) and XML (--xmlor -X).
mysql 客户端没有 csv 输出模式，它只有条形和破折号表格格式（带有--table或-t标志）、制表符分隔格式（带有--batch或-B标志）、HTML 格式（--html或-H）和 XML（--xml或-X）。
If you have content in your database that contains tabs, then either convert tabs to something else using the REPLACE()SQL function, or else output in one of the other formats and try to convert that to csv:
如果您的数据库中有包含选项卡的内容，则可以使用REPLACE()SQL 函数将选项卡转换为其他内容，或者以其他格式之一输出并尝试将其转换为 csv：
mysql ... --table --skip-column-names |
sed -e 's/ *| */,/g' \
-e 's/^| *//' \
-e 's/ *|$//' \
-e '/^+--/d'
回答by paxdiablo
You don't need another script to do that. Just change:
你不需要另一个脚本来做到这一点。只是改变：
> ${MYSQLDUMP}
at the end of your line to read:
在你行的末尾阅读：
| sed 's/\x09/,/g' > ${MYSQLDUMP}
But you need to be careful that your data doesn't have commas in it or they'll be mis-recognized by whatever uses the CSV file (but you may have that problem with tabs already).
但是您需要注意您的数据中没有逗号，否则它们会被使用 CSV 文件的任何人错误识别（但您可能已经遇到了选项卡的问题）。
回答by pfyon
Why not use
为什么不使用
SELECT ${MYSQLTABLE}.created_at, ${MYSQLTABLE}.product_options INTO outfile '${MYSQLDUMP}' FROM ${MYSQLTABLE} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' WHERE ${MYSQLTABLE}.product_id=1 ORDER BY ${MYSQLTABLE}.created_at
