MySQL 转储到 CSV 文本文件中，列名在顶部？

MySQL dump into CSV text files with column names at the top?

提问by datasn.io

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

可能的重复：
如何将 MySql 表导出/转储到包含字段名称（又名标题或列名称）的文本文件中

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

我使用此 SQL 代码段将表转储到 CSV 文本文件中：

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

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".

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

回答by Amos

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:

我想出了一种方法，只要您运行的是 MySQL 5 或更高版本，就必须手动输入这些名称。在这里，编写为用于在 unix 命令行上运行的 bash 脚本：

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 }' | 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...

虽然不是最优雅的解决方案，但我相信它可以被比我更了解 SQL 和/或 bash 的人压缩成一行......

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

1. 使用 MySQL 的信息模式创建一个带有列标题的空 CSV
2. 将额外的换行符附加到该空 CSV 中，以便您的数据将开始出现新行
3. 使用非常标准的“SELECT * INTO OUTFILE...”查询来创建一个充满数据的 CSV
4. 将数据文件附加到带有列标题的文件中
5. 删除（临时）数据文件

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

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

回答by Blake Miller

I think this does what you want. I'm using mysql 5.1.60, this outputs field names on the first line. This will use "\t" as the field separator, I'm not sure how to ask for a comma instead.

我认为这可以满足您的要求。我正在使用 mysql 5.1.60，这会在第一行输出字段名称。这将使用 "\t" 作为字段分隔符，我不确定如何要求使用逗号。

echo "SELECT * FROM brand;" | mysql -uXXX -pXXX databasename > brand.tsv