mysql into file 列名_mysql - 使用SELECT INTO OUTFILE时是否包含标题? - 堆栈内存溢出...

===============>>#1 票数:147 已采纳

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

SELECT 'ColName1', 'ColName2', 'ColName3'

UNION ALL

SELECT ColName1, ColName2, ColName3

FROM YourTable

INTO OUTFILE '/path/outfile'

===============>>#2 票数:77

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

===============>>#3 票数:13

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

===============>>#4 票数:6

我只简单地进行2个查询,首先获取具有列名的查询输出(限制1)(无硬编码,Join,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 票数: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。

===============>>#6 票数:5

这将使您能够订购列和/或限制

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 票数:5

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

使用OUTFILE查询准备没有标题的文件SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\"' LINES TERMINATED BY '\\n' FROM [TABLE_NAME]

获取第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

使用prepend-file npm软件包将列标题附加到在步骤1中创建的文件中

每个步骤的执行都使用NodeJS中的promises进行控制。

===============>>#8 票数:3

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

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

这是我使用R的方法,需要RMySQL库:

db

query

dataset

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

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

===============>>#9 票数:2

因此, 如果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很大,这可能会很慢。

===============>>#10 票数:1

由于“ include-headers”功能似乎尚未内置,并且大多数“解决方案”此处都需要手动输入列名称,并且/或者甚至不考虑联接,因​​此我建议解决问题 。

到目前为止,我发现最好的替代方法是使用一个不错的工具(我使用HeidiSQL )。

提出您的要求,选择网格,只需右键单击并导出到文件即可。 它为清洁出口提供了所有必要的选择,应满足大多数需求。

按照同样的想法,user3037511的方法工作良好,并且可以轻松实现自动化 。

只需使用一些命令行启动您的请求即可获取标头。 您可以使用SELECT INTO OUTFILE ...来获取数据,也可以不受限制地运行查询来选择。

请注意,在Linux和Windows上,将输出重定向到文件的操作都像一个超级按钮。

这使我想强调一下,当我想使用SELECT FROM INFILE或SELECT INTO OUTFILE时 ,有80%的时间由于某些限制(在这里,缺少“标题选项”, AWS-RDS,缺少的权利,等等。)

因此,我无法完全回答操作者的问题 ...但是它应该可以满足他的需求 :) 编辑:并实际回答他的问题: 否 从2017年9月7日开始,如果您坚持使用SELECT INTO OUTFILE命令,则不能包含标题 :

===============>>#11 票数:1

来自数据库表名称传感器的一个带有列(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

===============>>#12 票数:0

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

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

假设您有三个字段,名称(varchar),is_active(布尔),date_something_happens(日期),并且要对后两个降序进行排序:

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

===============>>#13 票数:0

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

/* 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 票数:0

我当时用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);

===============>>#15 票数:0

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

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

===============>>#16 票数:0

仅MySQL并不能简单地做到这一点。 下面是一个PHP脚本,它将列和数据输出到CSV。

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

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

最后,有一个exec调用来对CSV进行GZip。

===============>>#17 票数:0

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

select 'header 1', 'header 2', ...

union

select col1, col2, ... from ...

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

===============>>#18 票数:-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'

ask by Brett translate from so

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页
评论

打赏作者

愷629

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值