使用 SELECT INTO OUTFILE 时是否包含标头？

在使用 MySQLINTO OUTFILE时，是否可能以某种方式包含标头？

218011 次浏览
小开
最佳答案

你必须自己硬编码这些标题，比如:

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

我认为如果你使用 UNION，它会起作用:

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

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

小开

Joe Steanelli 提供的解决方案是可行的，但是当涉及到几十或几百个列时，制作列列表是不方便的。下面介绍如何获取 我的模式中表 我的桌子的列表。

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

小开

您可以使用带有 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;

谢天谢地。

小开

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

只是需要一些按语句顺序排序的技巧——我们使用 case 语句，并用一些其他值替换头部值，这些值保证在列表中排序第一(显然这取决于字段的类型以及您是在排序 ASC 还是 DESC)

假设您有三个字段，name (varchar)、 is _ active (bool)、 date _ something _ goes (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
小开

对于 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';
小开 
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'
小开

如果您熟悉 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 来重新创建表，那么您需要删除该列。

小开

下面是一种从列名称中动态获取标题的方法。

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

我是用 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);
小开

因此，如果 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';

最后几行代码输出 csv。

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

小开

这将允许您拥有已排序的列和/或限制

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

我只需要进行两个查询，首先获得具有列名的查询输出(限制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
小开

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

  • 到目前为止，我发现最好的替代方法是使用一个像样的工具(我使用 HeidSQL)。
    将您的请求，选择网格，只需右键单击并导出到一个文件。它得到了清洁出口的所有必要选择，应该可以满足大多数需求。

  • 基于同样的想法，user3037511的方法工作得很好，可以是 很容易自动化。
    只需使用一些命令行 启动您的请求就可以获得头部。您可以使用 SELECT INTO OUTFILE 获得数据... 或者通过无限制地运行查询获得数据，这是您可以选择的。

    注意，输出重定向到一个文件在 Linux 和 Windows 上都非常有用。


由于一些限制(这里没有“头选项”、 AWS-RDS 上没有头选项、缺少权限等等) ，我想强调一下 在80% 的情况下，当我想使用 SELECT FROM INFILE 或 SELECT INTFILE 时，我最终会使用其他东西

因此，我并不完全回答行动的 有个问题... ... 但它应该回答他的 需求:)
编辑: 实际上回答他的问题: < strong > no
从2017-09-07开始，如果你坚持使用 SELECT INTO OUTFILE 命令 : | ，你就不能包含标题

小开

我在 NodeJS 中对大型表执行 mysql 查询时遇到了类似的问题。我在 CSV 文件中包含头文件所遵循的方法如下

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

        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

Execution of each step was controlled using promises in NodeJS.

小开

我数据库里的一个例子 表名 传感器带有 < strong > 列(id、 time、 unit)

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
小开

我想补充一下 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)

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

小开

仅仅使用 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。

小开

我在这些问题上一点运气都没有，所以在找到解决方案后，我想把它添加到先前的答案中。Python = = 3.8.6 MySQL = = 8.0.19

(请原谅我没有这样的格式。请有人清理。)

请注意以下几点:

首先，返回列名的查询不允许使用标点符号。在“ schema _ name”和“ table _ name”周围使用“反勾或省略”引号将抛出“未知列”错误。

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

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

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

第三，必须缓冲光标，否则“惰性”事件不会取得您所需要的结果。对于非常大的表，内存可能是一个问题。也许分块能解决这个问题。

cur = db.cursor(buffered=True)

最后，所有类型的 UNION 尝试都产生了错误。通过将整个混乱局面压缩到一个 dicts 列表中，使用 csv 写入 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)
小开

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

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

如果您正在使用 MySQL 工作台:

  1. 从 SCHEMAS 选项卡中选择所有列-> 右击-> 复制到 剪贴板-> 名称

  2. 粘贴到任何文本编辑器中，并将“’”替换为“’”

  3. 将其复制回来，并在 UNION 查询中使用它(如接受的 答案) :

    SELECT [Paste your text here]
UNION ALL
SELECT *
FROM table_name
INTO OUTFILE 'file_path'
小开

使用 python 的解决方案，但是如果已经使用其他工具，则不需要安装 python 包来读取 sql 文件。 如果你不熟悉 python，你可以在 colab 笔记本上运行 python 代码，所有必需的软件包都已经安装好了。它自动化 Matt 和 Joe 的解决方案。

首先执行这个 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 导出所有带有标题的表，而不必写或复制粘贴所有表和列的名称。


提交答案