将MySQL表导出到CSV文件 [英] Exporting a MySQL table into a CSV file
本文介绍了将MySQL表导出到CSV文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个必须作为CSV文件取出的MySQL表.我使用的查询是
I have a MySQL table which has to be taken out as a CSV file. The query I used is
SELECT "ID","NAME","SALARY","SAL1","SAL2","SAL3","SAL4","SAL5","SAL6","SAL7","SAL8","SAL9","SAL10","SAL11","SAL12","SAL13","SAL14","SAL15","SAL16","SAL17","SAL18","SAL19","SAL20","SAL21","SAL22","SAL23","SAL24","SAL25","SAL26"
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
此查询有效,但是如果我有200个列名怎么办? 有没有可以手动输入的方法吗?
This query works, but what if I have 200 column names? Is there a way to do it without manually typing it?
推荐答案
此命令几乎可以为您提供所需的内容,甚至可以与远程服务器一起使用.唯一需要注意的是,它会生成一个TSV文件(字段之间用制表符分隔).
This command almost gives you what you want, and it even works with a remote server. The only caveat is that it generates a TSV file (fields are separated by a tab).
mysql mydb -e "select * from mytable" -B > mytable.tsv
But you could convert it to CSV using sed, as suggested in this answer:
mysql mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv
这篇关于将MySQL表导出到CSV文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文