MySQL 使用 SELECT INTO OUTFILE 时包含标题？

Include headers when using SELECT INTO OUTFILE?

提问by Brett

Is it possible to include the headers somehow when using the MySQL INTO OUTFILE?

使用 MySQL 时是否可以以某种方式包含标题INTO OUTFILE？

回答by Joe Stefanelli

You'd have to hard code those headers yourself. Something like:

您必须自己对这些标头进行硬编码。就像是：

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE '/path/outfile'

回答by matt

The solution provided by Joe Steanelli works, but making a list of columns is inconvenient when dozens or hundreds of columns are involved. Here's how to get column list of table my_tablein my_schema.

Joe Steanelli 提供的解决方案有效，但是当涉及数十或数百列时，制作列列表不方便。以下是如何在my_schema 中获取表my_table 的列列表。

-- override GROUP_CONCAT limit of 1024 characters to avoid a truncated result
set session group_concat_max_len = 1000000;

select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND TABLE_SCHEMA = 'my_schema'
order BY ORDINAL_POSITION

Now you can copy & paste the resulting row as first statement in Joe's method.

现在您可以复制并粘贴结果行作为 Joe 方法中的第一条语句。

回答by evilguc

For complex select with ORDER BY I use the following:

对于带有 ORDER BY 的复杂选择，我使用以下内容：

SELECT * FROM (
    SELECT 'Column name #1', 'Column name #2', 'Column name ##'
    UNION ALL
    (
        // complex SELECT statement with WHERE, ORDER BY, GROUP BY etc.
    )
) resulting_set
INTO OUTFILE '/path/to/file';

回答by Vrag

You can use prepared statement with lucek's answer and export dynamically table with columns name in CSV :

您可以将准备好的语句与 lucek 的答案一起使用，并使用 CSV 中的列名动态导出表：

--If your table has too many columns
SET GLOBAL group_concat_max_len = 100000000;
--Prepared statement
SET @SQL = ( select CONCAT('SELECT * INTO OUTFILE \'YOUR_PATH\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'\' LINES TERMINATED BY \'\n\' FROM (SELECT ', GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")),' UNION select * from YOUR_TABLE) as tmp') from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YOUR_TABLE' AND TABLE_SCHEMA = 'YOUR_SCHEMA' order BY ORDINAL_POSITION );
--Execute it
PREPARE stmt FROM @SQL;
EXECUTE stmt;

Thank lucek.

谢谢卢克。

回答by user3037511

I simply make 2 queries, first to get query output (limit 1) with column names (no hardcode, no problems with Joins, Order by, custom column names, etc), and second to make query itself, and combine files into one CSV file:

我简单地进行了 2 个查询，首先是获取带有列名的查询输出（限制为 1）（没有硬编码，Joins、Order by、自定义列名等没有问题），然后进行查询，然后将文件组合成一个 CSV文件：

CSVHEAD=`/usr/bin/mysql $CONNECTION_STRING -e "$QUERY limit 1;"|head -n1|xargs|sed -e "s/ /'\;'/g"`
echo "\'$CSVHEAD\'" > $TMP/head.txt
/usr/bin/mysql $CONNECTION_STRING -e "$QUERY into outfile '${TMP}/data.txt' fields terminated by ';' optionally enclosed by '\"' escaped by '' lines terminated by '\r\n';"
cat $TMP/head.txt $TMP/data.txt > $TMP/data.csv

回答by rahul shukla

I faced similar problem while executing mysql query on large tables in NodeJS. The approach which I followed to include headers in my CSV file is as follows

我在 NodeJS 中的大表上执行 mysql 查询时遇到了类似的问题。我在 CSV 文件中包含标题的方法如下

1. Use OUTFILE query to prepare file without headers

       SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED 
       BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME]
   

2. Fetch column headers for the table used in point 1

       select GROUP_CONCAT(CONCAT(\"\",COLUMN_NAME,\"\")) as col_names from 
       INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [TABLE_NAME] AND TABLE_SCHEMA 
       = [DATABASE_NAME] ORDER BY ORDINAL_POSITION
   

3. Append the column headers to the file created in step 1 using prepend-file npm package

1. 使用 OUTFILE 查询准备没有标题的文件

       SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED 
       BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME]
   

2. 获取第 1 点中使用的表的列标题

       select GROUP_CONCAT(CONCAT(\"\",COLUMN_NAME,\"\")) as col_names from 
       INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [TABLE_NAME] AND TABLE_SCHEMA 
       = [DATABASE_NAME] ORDER BY ORDINAL_POSITION
   

3. 使用prepend-filenpm 包将列标题附加到在步骤 1 中创建的文件中

Execution of each step was controlled using promises in NodeJS.

每个步骤的执行都使用 NodeJS 中的 promise 来控制。

回答by Donald Wagner

This will alow you to have ordered columns and/or a limit

这将使您能够拥有有序的列和/或限制

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT * from (SELECT ColName1, ColName2, ColName3
    FROM YourTable order by ColName1 limit 3) a
    INTO OUTFILE '/path/outfile';

回答by Joining Dots

This is an alternative cheat if you are familiar with Python or R, and your table can fit into memory.

如果您熟悉 Python 或 R，并且您的表可以放入内存，那么这是另一种作弊方法。

Import the SQL table into Python or R and then export from there as a CSV and you'll get the column names as well as the data.

将 SQL 表导入 Python 或 R，然后从那里导出为 CSV，您将获得列名和数据。

Here's how I do it using R, requires the RMySQL library:

这是我使用 R 的方法，需要 RMySQL 库：

db <- dbConnect(MySQL(), user='user', password='password', dbname='myschema', host='localhost')

query <- dbSendQuery(db, "select * from mytable")
dataset <- fetch(query, n=-1)

write.csv(dataset, 'mytable_backup.csv')

It's a bit of a cheat but I found this was a quick workaround when my number of columns was too long to use the concat method above. Note: R will add a 'row.names' column at the start of the CSV so you'll want to drop that if you do need to rely on the CSV to recreate the table.

这有点作弊，但当我的列数太长而无法使用上面的 concat 方法时，我发现这是一个快速的解决方法。注意：R 将在 CSV 的开头添加一个“row.names”列，因此如果您确实需要依赖 CSV 来重新创建表，则需要删除它。

回答by TheBamf

So, if all the columns in my_tableare a character data type, we can combine the top answers (by Joe, matt and evilguc) together, to get the header added automatically in one 'simple' SQL query, e.g.

因此，如果所有列my_table都是字符数据类型，我们可以将最佳答案（由 Joe、matt 和 evilguc 提供）组合在一起，以在一个“简单”的 SQL 查询中自动添加标题，例如

select * from (
  (select column_name
    from information_schema.columns
    where table_name = 'my_table'
    and table_schema = 'my_schema'
    order by ordinal_position)
  union all
  (select *  // potentially complex SELECT statement with WHERE, ORDER BY, GROUP BY etc.
  from my_table)) as tbl
into outfile '/path/outfile'
fields terminated by ',' optionally enclosed by '"' escaped by '\'
lines terminated by '\n';

where the last couple of lines make the output csv.

最后几行生成输出 csv。

Note that this may be slow if my_tableis very large.

请注意，如果my_table非常大，这可能会很慢。

回答by Balmipour

Since the 'include-headers' functionality doesn't seem to be build-in yet, and most "solutions" here need to type the columns names manually, and/or don't even take joins into account, I'd recommand to get around the problem.

由于“include-headers”功能似乎还没有内置，而且这里的大多数“解决方案”都需要手动输入列名，和/或甚至不考虑连接，我建议解决问题。

• The best alternative I found so far is using a decent tool (I use HeidiSQL).
  Put your request, select the grid, just right click and export to a file. It got all necessary options for a clean export, ans should handle most needs.

• In the same idea, user3037511's approach works fine, and can be automated easily.
  Just launch your request with some command lineto get your headers. You may get the data with a SELECT INTO OUTFILE... or by running your query without the limit, yours to choose.

  Note that output redirect to a file works like a charm on both Linux AND Windows.

• 到目前为止我发现的最好的替代方法是使用一个不错的工具（我使用HeidiSQL）。
  提出您的要求，选择网格，只需右键单击并导出到文件。它获得了干净导出的所有必要选项，应该可以满足大多数需求。

• 在同样的想法中，user3037511 的方法工作正常，并且可以轻松实现自动化。
  只需使用一些命令行启动您的请求即可获取标头。您可以使用 SELECT INTO OUTFILE... 或通过无限制地运行查询来获取数据，您可以选择。

  请注意，重定向到文件的输出在 Linux 和 Windows 上都像一个魅力。

This makes me want to highlight that 80% of the time, when I want to use SELECT FROM INFILE or SELECT INTO OUTFILE, I end-up using something elsedue to some limitations (here, the absence of a 'headers options', on an AWS-RDS, the missing rights, and so on.)

这让我想强调80% 的时间，当我想使用 SELECT FROM INFILE 或 SELECT INTO OUTFILE 时，由于某些限制，我最终会使用其他东西（这里，没有“标题选项”，在AWS-RDS、缺少的权限等。）

Hence, I don't exactly answer to the op's question... but it should answer his needs:)
EDIT : and to actually answer his question : no
As of 2017-09-07, you just can't include headers if you stick with the SELECT INTO OUTFILE command:|

因此，我完全不回答运算的问题...但它应该回答他的需求:)
编辑：和实际回答他的问题：没有
的2017年9月7日当，你就不能包括标题，如果你坚持使用 SELECT INTO OUTFILE 命令：|