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

Question

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

Answer 1

您必须自己对这些标头进行硬编码。像这样的东西：

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

Answer 2

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

现在您可以复制&将结果行粘贴为 Joe 方法中的第一个语句。

Answer 3

对于使用 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';

Answer 4

这将允许您订购列和/或限制

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

Answer 5

您可以将准备好的语句与 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;

感谢 lucek。

Answer 6

我只需进行 2 个查询，第一个是获取带有列名称的查询输出（限制 1）（无硬编码，连接、排序依据、自定义列名称等没有问题），第二个是进行查询本身，并将文件合并到一个 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

Answer 7

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

1. 使用 OUTFILE 查询准备不带标题的文件

    SELECT * INTO OUTFILE [FILE_NAME] 字段以“,”结尾（可选） 
       BY '\"' 行以 '\n' 结尾，来自 [TABLE_NAME]
   

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

    选择 GROUP_CONCAT(CONCAT(\"\",COLUMN_NAME,\"\")) 作为 col_names 
       INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [TABLE_NAME] AND TABLE_SCHEMA 
       = [DATABASE_NAME] 按 ORDINAL_POSITION 排序
   

3. 使用 prepend-file npm package

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

Answer 8

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

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

这是我使用 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')

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

Answer 9

我认为如果您使用 UNION 它将起作用：

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

我不知道直接使用 INTO OUTFILE 语法指定标头的方法。

Answer 10

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

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

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

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

这让我想强调，80% 的情况下，当我想使用 SELECT FROM INFILE 或 SELECT INTO OUTFILE 时，由于某些限制（此处缺少AWS-RDS 上的“标头选项”、缺少的权限等等。）

因此，我没有完全回答操作员的问题...但它应该回答他的 >需求 :)

编辑：并实际回答他的问题：不

截至 2017 年 9 月 7 日，如果您坚持使用 SELECT INTO OUTFILE 命令，则无法包含标头：|

Answer 11

最简单的方法是自己对列进行硬编码，以更好地控制输出文件：

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

Answer 12

实际上，即使使用 ORDER BY 也可以使其工作。

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

假设您有三个字段：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

Answer 13

这是一种从列名动态获取标题标题的方法。

/* 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;

Answer 14

我的数据库中的一个例子
表名称传感器和列（id、时间、单位）

select ('id') as id, ('time') as time, ('unit') as unit
UNION ALL
SELECT * INTO OUTFILE 'C:/Users/User/Downloads/data.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM sensor

Answer 15

如果您使用 MySQL Workbench：

1. 从“SCHEMAS”选项卡中选择所有列 ->右键单击->复制到
   剪贴板->姓名

2. 将其粘贴到任何文本编辑器中，然后将“`”替换为“'”

3. 将其复制回来并在您的 UNION 查询中使用它（如接受的
   答案）：

   SELECT [在此处粘贴您的文本]
   联合所有
   选择 *
   FROM 表名
   INTO OUTFILE '文件路径'
   

Answer 16

受到 Rick James 的数据透视表示例的启发。

SET @CSVTABLE = 'myTableName',
    @CSVBASE = 'databaseName',
    @CSVFILE = '/tmp/filename.csv';

SET @sql = (SELECT CONCAT("SELECT ", GROUP_CONCAT(CONCAT('"', COLUMN_NAME, '"')), " UNION SELECT * FROM ", @CSVBASE, ".", @CSVTABLE) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@CSVTABLE AND TABLE_SCHEMA=@CSVBASE);
prepare stmt from CONCAT(@sql, " INTO OUTFILE '", @CSVFILE, "' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'  LINES TERMINATED BY '\\n';");
execute stmt;

它从 INFORMATION_SCHEMA.COLUMNS 表中获取列列表，并使用 GROUP_CONCAT 准备包含带有列名称的字符串列表的 SELECT 语句。

接下来添加UNION 和SELECT * FROM指定的database.table - 这将创建查询文本，该文本将在结果中输出列名和列值。

现在，使用之前创建的查询（存储在 @sql 变量中）准备语句，CSV 输出特定的“事物”将附加到查询中，最后使用 execute stmt 执行语句

Answer 17

我正在用 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);

Answer 18

因此，如果 my_table 中的所有列都是字符数据类型，我们可以将最热门的答案（由 Joe、matt 和 illguc 提供）组合在一起，以自动添加标题在一个“简单”的 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';

最后几行输出 csv。

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

Answer 19

我想补充 Sangam Belose 提供的答案。这是他的代码：

select ('id') as id, ('time') as time, ('unit') as unit
UNION ALL
SELECT * INTO OUTFILE 'C:/Users/User/Downloads/data.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM sensor

但是，如果您没有在变量中设置“secure_file_priv”，则它可能无法工作。为此，请通过以下方式检查该变量上设置的文件夹：

SHOW VARIABLES LIKE "secure_file_priv"

输出应如下所示：

mysql> show variables like "%secure_file_priv%";
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

您可以更改此变量或更改查询以将文件输出到显示的默认路径。

Answer 20

仅 MySQL 不足以简单地完成此任务。下面是一个 PHP 脚本，它将把列和数据输出到 CSV。

在顶部附近输入您的数据库名称和表。

<?php

set_time_limit( 24192000 );
ini_set( 'memory_limit', '-1' );
setlocale( LC_CTYPE, 'en_US.UTF-8' );
mb_regex_encoding( 'UTF-8' );

$dbn = 'DB_NAME';
$tbls = array(
'TABLE1',
'TABLE2',
'TABLE3'
);

$db = new PDO( 'mysql:host=localhost;dbname=' . $dbn . ';charset=UTF8', 'root', 'pass' );

foreach( $tbls as $tbl )
{
    echo $tbl . "\n";
    $path = '/var/lib/mysql/' . $tbl . '.csv';

    $colStr = '';
    $cols = $db->query( 'SELECT COLUMN_NAME AS `column` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "' . $tbl . '" AND TABLE_SCHEMA = "' . $dbn . '"' )->fetchAll( PDO::FETCH_COLUMN );
    foreach( $cols as $col )
    {
        if( $colStr ) $colStr .= ', ';
        $colStr .= '"' . $col . '"';
    }

    $db->query(
    'SELECT *
    FROM
    (
        SELECT ' . $colStr . '
        UNION ALL
        SELECT * FROM ' . $tbl . '
    ) AS sub
    INTO OUTFILE "' . $path . '"
    FIELDS TERMINATED BY ","
    ENCLOSED BY "\""
    LINES TERMINATED BY "\n"'
    );

    exec( 'gzip ' . $path );

    print_r( $db->errorInfo() );
}

?>

您需要将其作为您想要输出到的目录。 MySQL需要具有写入目录的能力。

$path = '/var/lib/mysql/' . $tbl . '.csv';

您可以在查询中编辑 CSV 导出选项：

INTO OUTFILE "' . $path . '"
FIELDS TERMINATED BY ","
ENCLOSED BY "\""
LINES TERMINATED BY "\n"'

最后有一个对 GZip CSV 的 exec 调用。

Answer 21

我对这些都没有运气，所以在找到解决方案后，我想将其添加到之前的答案中。 Python==3.8.6 MySQL==8.0.19

（请原谅我没有对 foo 进行如此格式化。请有人清理一下。）

请注意以下几点：

首先，返回列名的查询不能使用标点符号。在“schema_name”和“table_name”周围使用“反引号”或省略“引号”将引发“未知列”错误。

WHERE TABLE_SCHEMA = 'schema' AND TABLE_NAME = 'table'

其次，列标题名称作为单实体元组返回，所有列名称连接在一个带引号的字符串中。转换为引用列表很容易，但并不直观（至少对我来说）。

headers_list = headers_result[0].split(",")

第三，游标必须被缓冲，否则“懒惰”的东西将无法在您需要时获取您的结果。对于非常大的表，内存可能是一个问题。也许分块可以解决这个问题。

cur = db.cursor(buffered=True)

最后，所有类型的 UNION 尝试都给我带来了错误。通过将整个混乱压缩到字典列表中，使用 csv.DictWriter 写入 csv 变得微不足道。

headers_sql = """   
SELECT 
GROUP_CONCAT(CONCAT(COLUMN_NAME) ORDER BY ORDINAL_POSITION)   
FROM INFORMATION_SCHEMA.COLUMNS   
WHERE TABLE_SCHEMA = 'schema' AND TABLE_NAME = 'table';   
"""" 

cur = db.cursor(buffered=True) 
cur.execute(header_sql) 
headers_result = cur.fetchone() 
headers_list = headers_result[0].split(",")

rows_sql = """   SELECT * FROM schema.table;   """" 

data = cur.execute(rows_sql) 
data_rows = cur.fetchall() 
data_as_list_of_dicts = [dict(zip(headers_list, row)) for row in data_rows]

with open(csv_destination_file, 'w', encoding='utf-8') as destination_file_opened: 
    dict_writer = csv.DictWriter(destination_file_opened, fieldnames=headers_list) 
    dict_writer.writeheader()   for dict in dict_list:
        dict_writer.writerow(dict)

Answer 22

使用 python 的解决方案，但如果您已经使用其他工具，则无需安装 python 包来读取 sql 文件。
如果您不熟悉 python，您可以在 colab 笔记本中运行 python 代码，所有必需的软件包都已安装。它使马特和乔的解决方案自动化。

首先执行此 SQL 脚本以获取包含所有表名称的 csv：

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='your_schema'
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/tables.csv';

然后将tables.csv 移动到合适的目录，并在替换“path_to_tables”和“your_schema”后执行此 python 代码。它将生成一个 sql 脚本来导出所有表标题：

import pandas as pd
import os

tables = pd.read_csv('tables.csv',header = None)[0]
text_file = open("export_headers.sql", "w")
schema = 'your_schema'

sql_output_path = 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/'
for table in tables :
    path = os.path.join(sql_output_path,'{}_header.csv'.format(table))
    string = "(select GROUP_CONCAT(COLUMN_NAME)\nfrom INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = '{}'\nAND TABLE_SCHEMA = '{}'\norder BY ORDINAL_POSITION)\nINTO OUTFILE '{}';".format(table,schema,path)
    n = text_file.write(string)
    n = text_file.write('\n\n')
text_file.close()

然后执行此 python 代码，该代码将生成一个 sql 脚本来导出所有表的值：

text_file = open("export_values.sql", "w")
for table in tables :
    path = os.path.join(sql_output_path,'{}.csv'.format(table))
    string = "SELECT * FROM {}.{}\nINTO OUTFILE '{}';".format(schema,table,path)
    n = text_file.write(string)
    n = text_file.write('\n\n')
text_file.close()

执行两个生成的 sql 脚本并将标题 csv 和值 csv 移动到您选择的目录中。

然后执行最后一个 python 代码：

#Respectively the path to the headers csvs, the values csv and the path where you want to put the csvs with headers and values combined
headers_path, values_path, tables_path = '', '', '' 

for table in tables :
    header = pd.read_csv(os.path.join(headers_path,'{}_header.csv'.format(table)))
    df = pd.read_csv(os.path.join(values_path,'{}.csv'.format(table)),names = header.columns,sep = '\t')
    df.to_csv(os.path.join(tables_path,'{}.csv'.format(table)),index = False)

然后您将所有表导出为带有标题的 csv，而无需编写或复制粘贴所有表和列名称。

Answer 23

之后您始终可以使用 Bash 添加列：

columns=$(mysql -u root -D database_name -e "SELECT * from users LIMIT 1" | head -1 | tr -s '[:blank:]' ',')
// Get the column names from the DB and replace tabs with commas to make them CSV
sed -i "1s/^/$columns\n/" "$database_name.csv"
// Add the columns to a new line at the beginning of the CSV file

对于大型 CSV 文件，这可能需要一段时间，并且需要两倍于文件大小的可用空间，以便 sed 创建其自动备份文件，但它完成工作。

Answer 24

您可以按照文档使用格式文本标题 -
https://docs.aws.amazon.com/ AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.SaveIntoS3.html

我使用类似的东西：

SELECT * FROM table
INTO OUTFILE S3 's3://bucket/folder'
设置文本标题格式
以“,”结尾的字段
可选择用“”括起来
以 '\n' 结尾的行
覆盖

尝试并让我知道

Answer 25

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'