如何将 MySql 表导出/转储到包含字段名称（又名标题或列名称）的文本文件中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/262924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export / dump a MySql table into a text file including the field names (aka headers or column names)
提问by Dan Goldstein
In MySql's interpreter, it's very easy to dump a table to the screen along with its field names.
在 MySql 的解释器中，很容易将一个表连同它的字段名一起转储到屏幕上。
There seems to be no simple way to export a table to a tab-delimted or CSV outfile includingits column headers.
似乎没有简单的方法可以将表导出到制表符分隔或 CSV 输出文件，包括其列标题。
I'm trying to do this using only SQL or the Linux command line, without writing a program in another language.
我正在尝试仅使用 SQL 或 Linux 命令行来执行此操作，而不用另一种语言编写程序。
Thank you
谢谢
回答by Vinko Vrsalovic
Piping the query to the commandline client outputs a tab separated list with the column names as the first line
将查询通过管道传送到命令行客户端会输出一个制表符分隔的列表，其中列名作为第一行
$ echo "select * from surveys limit 5" | mysql -uroot -pGandalf surveys
phone param1 param2 param3 param4 p0 p1 p2 p3 audio4 code time
XXXXXXXXX 2008-07-02 11:17:23 XXXXXXXX SAT - - - - - ERROR 2008-07-02 12:18:32
XXXXXXXXX 2008-07-02 11:22:52 XXXXXXXX SAT - - - - - COLGADO 2008-07-02 12:04:29
XXXXXXXXX 2008-07-02 11:41:29 XXXXXXXX SAT - - - - - COLGADO 2008-07-02 12:07:22
XXXXXXXXX 2008-07-02 12:16:19 XXXXXXXX SAT 1 1 1 9 XXXXXXXXX_4.wav OK 2008-07-02 16:14:27
XXXXXXXXX 2008-07-02 08:21:25 XXXXXXXX SAT 1 1 1 1 XXXXXXXXX_4.wav OK 2008-07-02 12:29:40
回答by cafe876
This little script should do it:
这个小脚本应该这样做：
-- 1. choose the table and the output file here / this should be the only input
-- 1.在这里选择表格和输出文件/这应该是唯一的输入
select 'mytable' into @tableName;
select 'c://temp/test.csv' into @outputFile;
-- 2. get the column names in a format that will fit the query
-- 2. 以适合查询的格式获取列名
select group_concat(concat("'",column_name, "'")) into @columnNames from information_schema.columns
where table_name=@tableName;
-- 3. build the query
-- 3. 构建查询
SET @query = CONCAT(
"select * from
((SELECT ",@columnNames,")
UNION
(SELECT * FROM `",@tableName,"`)) as a
INTO OUTFILE '", @outputFile, "'");
-- 4. execute the query
-- 4.执行查询
PREPARE stmt FROM @query;
EXECUTE stmt;
回答by aizquier
I achieved that in this way:
我是这样实现的：
echo "select * from table"| mysql database -B -udbuser -puser_passwd | sed s/\t/,/g > query_output.csv
The -B option of mysql separates the columns by tabs, which are converted into commas using sed. Note that the headers are generated too.
mysql 的 -B 选项通过制表符分隔列，使用 sed 将其转换为逗号。请注意，标题也会生成。
回答by Dana the Sane
回答by Lifeboy
I have created a procedure to automate the exporting of the contents of a larger number of tables to .csv file by using SELECT ... INTO OUTFILE. Please refer to the following if you have need for something like this
我创建了一个过程，可以使用 .csv 文件自动将大量表的内容导出到 .csv 文件SELECT ... INTO OUTFILE。如果您有这样的需求，请参考以下内容
http://lifeboysays.wordpress.com/2012/06/23/mysql-how-to-export-data-to-csv-with-column-headers/.
http://lifeboysays.wordpress.com/2012/06/23/mysql-how-to-export-data-to-csv-with-column-headers/。
It uses the method described by cafe876, but will work for one or a whole series of tables, plus you can set the delimiter and quote character to be used.
它使用 cafe876 描述的方法，但适用于一个或整个系列的表，此外您还可以设置要使用的分隔符和引号字符。
回答by aiffin
I used the above command and modified according to my requirement.
I needed to get passwords column from the wordpress mysql database in a text file , to do that i used the following below command
我使用了上面的命令并根据我的要求进行了修改。
我需要从文本文件中的 wordpress mysql 数据库中获取密码列，为此我使用了以下命令
echo "select user_pass from wp_users"| mysql -uroot -proot wp_database > passwordList.txt
