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

问题描述

可能重复：

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

SELECT * FROM brand INTO OUTFILEe：/brand.csv
终止于'，'ENCLOSED BY''
\ n;

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

解决方案

我想出了一个办法，只要你运行MySQL 5或更高版本，就必须手动输入这些名称。这是一个bash脚本，在unix命令行运行：

  DBNAME =< database_name> 
 TABLE =< table_name> 
 
 FNAME = / path / to / output / dir / $（date +％Y.％m。％d） -  $ DBNAME.csv 
 
＃空文件并使用information_schema设置列名称
 mysql -u< username> -p< password> $ 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< password> $ 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）删除临时文件
 rm  - rf /var/mysql/tempfile.csv 
  

虽然不是最优雅的解决方案，可以由知道SQL和/或bash比我好一点的人压缩成单行...

它是什么：

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


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


3. 使用标准的SELECT * INTO OUTFILE ...查询创建包含数据的CSV





p>祝你好运，如果你清理它，发布你的结果！



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屋！