MySQL 查询将输出作为 CSV 打印到标准输出
我想要执行以下操作 mysql -uuser -ppass -h remote.host.tld 数据库 script.sql
其中 script.sql 包含以下内容
SELECT *
FROM webrecord_wr25mfz_20101011_175524
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
我希望 CSV 输出定向到标准输出。原因是因为使用 INTO OUTFILE 'blah.csv' 运行此查询会将文件保存在远程主机上。我希望将文件保存在本地主机上。
如果我可以将标准输出重定向到一个文件，那就太棒了。
I want to do the following mysql -uuser -ppass -h remote.host.tld database < script.sql
where script.sql contains the following
SELECT *
FROM webrecord_wr25mfz_20101011_175524
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
I want CSV output directed to standard out. The reason is because running this query with an INTO OUTFILE 'blah.csv' will save the file on the remote host. I want the file saved on the local host.
If I could just redirect the standard output to a file, that would be dandy.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
上面的答案似乎并没有完全回答原来的问题，我不确定这是否可以，但希望这可以帮助某人：
请参阅如何以 CSV 格式输出 MySQL 查询结果？ 有关如何
sed的大量评论。例如，根据原始参数，以下内容可能就足够了：这与上面的答案类似，但重定向到
stdout而不是blah.csv。但是，（虽然不确定如果您需要保留选项卡这是否有效，但有很多方法可以解决这个问题），我使用了 https://stackoverflow.com/a/2543226/2178980 要正确转义双引号并转换为逗号分隔：
sql"SELECT * FROM webrecord_wr25mfz_20101011_175524;"通过mysql（此输出将以制表符分隔）perl -lpe 's/"/\\"/g; 转换为逗号分隔； s/^|$/"/g; s/\t/","/g'2>&1将输出转到stdout代码>The answers above don't seem to fully answer the original question, and I'm not sure if this does either, but hopefully this might help someone:
See How to output MySQL query results in CSV format? for a lot of comments regarding how to
sed. For example, based on the original parameters, the following might be sufficient:This is similar to the answer above, but redirecting to
stdoutinstead ofblah.csv.However, (although not sure if this will work if you need to preserve tabs, there are many ways to address this though), I've used https://stackoverflow.com/a/2543226/2178980 to correctly escape double quotations and convert to comma-separated:
sql"SELECT * FROM webrecord_wr25mfz_20101011_175524;"viamysql(this output will be tab-separated)perl -lpe 's/"/\\"/g; s/^|$/"/g; s/\t/","/g'stdoutby appending2>&1部分是对 MySQL SELECT INTO OUTFILE to a different server 的重复问题。如果没有
into outfile则不能使用FIELDS TERMINATED BY一个（不太优雅的）替代方案是使用
--batch选项用于生成制表符分隔的输出并插入标准输出。像这样的事情：请注意
--batch转义特殊字符，因此根据您拥有的数据及其可预测性，您可能需要更改 sedIn part a duplicate question to MySQL SELECT INTO OUTFILE to a different server.
FIELDS TERMINATED BYcan't be used withoutinto outfileA (not so elegant) alternative is using the
--batchoption to produce tab separated output and sedding the stdout. Something like this:Be aware that
--batchescapes special characters so depending on the data you have and its predictability, you might need to change the sed试试这个： mysql -uuser -ppass -h remote.host.tld 数据库
脚本.sql 2> blah.csv这将重定向 stderr
Try this:
mysql -uuser -ppass -h remote.host.tld database < script.sql 2> blah.csvThis will redirect the stderr