mysql表导出到csv文件,将MySQL表导出到CSV文件

最新推荐文章于 2022-06-21 12:55:02 发布

weixin_39939668

最新推荐文章于 2022-06-21 12:55:02 发布

阅读量57

点赞数

文章标签： mysql表导出到csv文件

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';

This query works, but what if I have 200 column names?

Is there a way to do it without manually typing it?

解决方案

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