MySQL转储到CSV文本文件，列名列在顶部？ [英] MySQL dump into CSV text files with column names at the top?

问题描述

可能重复：

如何将MySql表导出/转储到包含字段名称（也称为标题或列名称）的文本文件中

我使用这个SQL代码段将表转储到CSV文本文件中： / p>

SELECT * FROM品牌INTO OUTFILEe：/brand.csv
FIELDS TERMINATED BY'，'ENCLOSED BY'''
LINES TERMINATED BY \\\
;

但是，这种方法不会在CSV文件的开头添加列名，我的问题是如何选择所有的列/字段名字，就像phpMyAdmin在导出表时选择在第一行中放置字段名称一样。

解决方案

我想出了一个方法，只要你是runnin就必须手动输入这些名字g MySQL 5或更高版本，这是一个用于在unix命令行上运行的bash脚本：

  DBNAME =< ; DATABASE_NAME> 
 TABLE =< table_name> 
 
 FNAME = / path / to / output / dir / $（date +％Y.％m。％d） -  $ DBNAME.csv 
 
＃（1）空文件并使用information_schema 
设置列名称mysql -u< username> -p<密码> $ DBNAME -B -eSELECT COLUMN_NAME FROM information_schema.COLUMNS C WHERE table_name ='$ TABLE'; | awk'{print $ 1}'| grep -iv ^ COLUMN_NAME $ | sed / s // g; s / $ // g'| tr'\''，'> $ FNAME 
 
＃（2）附加换行符来标记数据开始与列标题
 echo>> $ FNAME 
 
＃（3）将数据从DB转储到/var/mysql/tempfile.csv 
 mysql -u< username> -p<密码> $ DBNAME -B -eSELECT * INTO OUTFILE'/var/mysql/tempfile.csv'FIELDS TERMINATED BY'，'OPTIONALLY ENCLOSED BY'\FROM $ TABLE;
 
＃（ 4）合并数据文件和文件w /列名
 cat /var/mysql/tempfile.csv> $ FNAME 
 
＃（5）删除tempfile 
 rm  - rf /var/mysql/tempfile.csv 
  

虽然不是最优雅的解决方案，我相信可以通过某人知道SQL和/或bash比我更好的压缩成一行。

它的作用是：

1. 使用MySQL的信息模式创建一个空的CSV w /列标题


2. 在空的CSV中附加一个换行符，数据将开始出现新行。


3. 使用漂亮的标准SELECT * INTO OUTFILE ...查询创建一个CSV数据。


4. 将数据文件附加到文件w /列标题


5. 删除（临时）数据文件

好l uck，如果你清理干净，发布你的结果！

Possible Duplicate:
How to export / dump a MySql table into a text file including the field names (aka headers or column names)

I use this SQL snippet to dump a table into CSV text files:

SELECT * FROM brand INTO OUTFILE "e:/brand.csv" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY "\n";

However this approach doesn't add the column names at the beginning of the CSV file. My question is how to select all the column / field names as well, just like what phpMyAdmin does when you export the table and select "Put fields names in the first row".

解决方案

I figured out a way around having to manually enter those names as long as you're running MySQL 5 or higher. Here it is, written as a bash script for running on a unix command line:

DBNAME=<database_name>
TABLE=<table_name>

FNAME=/path/to/output/dir/$(date +%Y.%m.%d)-$DBNAME.csv

#(1)creates empty file and sets up column names using the information_schema
mysql -u <username> -p<password> $DBNAME -B -e "SELECT COLUMN_NAME FROM information_schema.COLUMNS C WHERE table_name = '$TABLE';" | awk '{print $1}' | grep -iv ^COLUMN_NAME$ | sed 's/^/"/g;s/$/"/g' | tr '\n' ',' > $FNAME

#(2)appends newline to mark beginning of data vs. column titles
echo "" >> $FNAME

#(3)dumps data from DB into /var/mysql/tempfile.csv
mysql -u <username> -p<password> $DBNAME -B -e "SELECT * INTO OUTFILE '/var/mysql/tempfile.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' FROM $TABLE;"

#(4)merges data file and file w/ column names
cat /var/mysql/tempfile.csv >> $FNAME

#(5)deletes tempfile
rm -rf /var/mysql/tempfile.csv

While not the most graceful solution, i'm sure it can be compressed into a single line by someone who knows SQL and/or bash a little better than me...

What it does is:

1. uses MySQL's information schema to create an empty CSV w/ column headers
2. appends an extra newline to that empty CSV so your data will begin appearing a new line
3. uses a pretty standard "SELECT * INTO OUTFILE..." query to create a CSV full of data
4. appends the data file onto the file w/ column headers
5. deletes the (temporary) data file

Good luck, and if you clean it up, post your results!

这篇关于MySQL转储到CSV文本文件，列名列在顶部？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！