MySQL:动态导出带有标头的CSV文件 [英] MySQL: Dynamically export CSV file with headers

问题描述

反正有没有导出带有列标题的MySQL表中的数据?

Is there anyway to export data in MySQL table with column headers?

我找到了一种通过将标头硬编码到查询中来实现此目的的方法，但是如果表中有大约60甚至100列，那是不可能的.

I find a way to do this by hard-coding the headers into query but if there are about 60 or even 100 columns in a table then it is impossible.

我尝试了以下查询，但由于查询的第一部分返回表中所有标头的串联字符串，因此无法获得结果.它没有给我想要的结果:

I tried the query below but I can't get the result as the first part of the query return a concatenated string of all headers in the table. It doesn't give me a desired result:

(select concat(group_concat(COLUMN_NAME separator ','), "\n")
    from information_schema.COLUMNS
    where table_name = '<table name>'
    and table_schema = '<DB name>'
    order by ORDINAL_POSITION) 
union all
(select * from <table name> into outfile "E:\\test.csv" fields terminated by "," lines terminated by "\n");

推荐答案

(SELECT 'Order Number','Order Date','Status')
UNION 
(SELECT orderNumber,orderDate, status
FROM orders
INTO OUTFILE 'C:/tmp/orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

这篇关于MySQL:动态导出带有标头的CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！