在使用SELECT INTO OUTFILE时包含头文件吗?

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

在使用MySQL到OUTFILE时，是否可以以某种方式包含头文件?

14 个解决方案

#1

118

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'

#2

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_table in 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方法中的第一个语句。

#3

For complex select with ORDER BY I use the following:

对于顺序复杂的选择，我使用以下方法:

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';

#4

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)(没有硬编码，不存在连接、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

#5

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.

谢谢lucek。

#6

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';

#7

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重新创建表，就需要删除该列。

#8

So, if all the columns in my_table are 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_table is very large.

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

#9

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-header '功能似乎还没有内置，这里的大多数“解决方案”都需要手动输入列名，或者甚至不考虑联接，我建议解决这个问题。

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.

到目前为止，我发现的最佳选择是使用一个合适的工具(我使用的是HeidiSQL)。放置您的请求，选择网格，只需右键单击并导出到文件。它为清洁出口提供了所有必要的选择，ans应该能够满足大多数需求。
In the same idea, user3037511's approach works fine, and can be automated easily.
Just launch your request with some command line to get your headers. You may get the data with a SELECT INTO OUTFILE... or by running your query without the limit, yours to choose.

在同样的思想中，user3037511的方法工作得很好，并且很容易实现自动化。只需使用命令行启动请求，就可以获得头部。你可以用选择文件获取数据……或者运行你的查询没有限制，你的选择。

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

注意，在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 else due to some limitations (here, the absence of a 'headers options', on an AWS-RDS, the missing rights, and so on.)

这使我想要突出显示80%的时间，当我想要从INFILE中选择SELECT或选择OUTFILE时，我将使用其他的一些限制(这里，在AWS-RDS中没有“header选项”，缺少的权限，等等)。

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 :|

因此，我并没有确切地回答op的问题……但是它应该能够满足他的需要)EDIT:和实际上回答他的问题:没有，在2017-09-07，如果你坚持SELECT INTO OUTFILE命令:|，你就不能包含头文件

#10

I think if you use a UNION it will work:

我认为，如果你使用一个工会，它会起作用:

select 'header 1', 'header 2', ...
union
select col1, col2, ... from ...

I don't know of a way to specify the headers with the INTO OUTFILE syntax directly.

我不知道如何用INTO OUTFILE语法直接指定头文件。

#11

Actually you can make it work even with an ORDER BY.

实际上，即使你下了订单，你也能让它正常工作。

Just needs some trickery in the order by statement - we use a case statement and replace the header value with some other value that is guaranteed to sort first in the list (obviously this is dependant on the type of field and whether you are sorting ASC or DESC)

只需要在order by语句中使用一些技巧——我们使用case语句并将头值替换为其他一些保证在列表中优先排序的值(显然这取决于字段的类型以及是否对ASC或DESC进行排序)

Let's say you have three fields, name (varchar), is_active (bool), date_something_happens (date), and you want to sort the second two descending:

假设你有三个字段，name (varchar)， is_active (bool)， date_something_happens (date)，你想要对第二个两个降序排序:

select 
        'name'
      , 'is_active' as is_active
      , date_something_happens as 'date_something_happens'

 union all

 select name, is_active, date_something_happens

 from
    my_table

 order by
     (case is_active when 'is_active' then 0 else is_active end) desc
   , (case date when 'date' then '9999-12-30' else date end) desc

#12

I was writing my code in PHP, and I had a bit of trouble using concat and union functions, and also did not use SQL variables, any ways I got it to work, here is my code:

我在用PHP编写代码，使用concat和union函数时遇到了一些麻烦，也没有使用SQL变量，不管我用什么方法，我的代码如下:

//first I connected to the information_scheme DB

$headercon=mysqli_connect("localhost", "USERNAME", "PASSWORD", "information_schema");

//took the healders out in a string (I could not get the concat function to work, so I wrote a loop for it)

    $headers = '';
    $sql = "SELECT column_name AS columns FROM `COLUMNS` WHERE table_schema = 'YOUR_DB_NAME' AND table_name = 'YOUR_TABLE_NAME'";
    $result = $headercon->query($sql);
    while($row = $result->fetch_row())
    {
        $headers = $headers . "'" . $row[0] . "', ";
    }
$headers = substr("$headers", 0, -2);

// connect to the DB of interest

$con=mysqli_connect("localhost", "USERNAME", "PASSWORD", "YOUR_DB_NAME");

// export the results to csv
$sql4 = "SELECT $headers UNION SELECT * FROM YOUR_TABLE_NAME WHERE ... INTO OUTFILE '/output.csv' FIELDS TERMINATED BY ','";
$result4 = $con->query($sql4);

#13

-1

Here is a way to get the header titles from the column names dynamically.

这里有一种方法可以动态地从列名称中获取标题标题。

/* Change table_name and database_name */
SET @table_name = 'table_name';
SET @table_schema = 'database_name';
SET @default_group_concat_max_len = (SELECT @@group_concat_max_len);

/* Sets Group Concat Max Limit larger for tables with a lot of columns */
SET SESSION group_concat_max_len = 1000000;

SET @col_names = (
  SELECT GROUP_CONCAT(QUOTE(`column_name`)) AS columns
  FROM information_schema.columns
  WHERE table_schema = @table_schema
  AND table_name = @table_name);

SET @cols = CONCAT('(SELECT ', @col_names, ')');

SET @query = CONCAT('(SELECT * FROM ', @table_schema, '.', @table_name,
  ' INTO OUTFILE \'/tmp/your_csv_file.csv\'
  FIELDS ENCLOSED BY \'\\\'\' TERMINATED BY \'\t\' ESCAPED BY \'\'
  LINES TERMINATED BY \'\n\')');

/* Concatenates column names to query */
SET @sql = CONCAT(@cols, ' UNION ALL ', @query);

/* Resets Group Contact Max Limit back to original value */
SET SESSION group_concat_max_len = @default_group_concat_max_len;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#14

-2

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE 'c:\\datasheet.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

#1

118