从 MySQL 到 CSV 文件的列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20404986/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Column names from MySQL to CSV file
提问by Andrew Glass
i've a table in a database i would like to use as a contacts table to import into outlook.
我在数据库中有一个表，我想用作联系人表以导入到 Outlook 中。
what i would like the import to do is this:
我希望导入做的是：
姓名 | 电子邮件andy | [email protected] <--name and email entered
安迪| [email protected] <--输入姓名和电子邮件
at the moment i have this code:
目前我有这个代码：
(SELECT 'Name', 'Email')
union all
(select E_Name, Email
INTO OUTFILE '/xampp/tmp/Sample.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\'
LINES TERMINATED BY '\n'
FROM email WHERE 1)
this creates an excel file like this:
这将创建一个像这样的excel文件：
A | B <-- excel column names
一个 | B <-- excel 列名
Name | Email
姓名 | 电子邮件
andy | [email protected]
i know if i pull the information using odbc connection it pulls the information the way i require, however i want it so the csv file is created with this information already in it, thus removing the need to do the odbc method.
我知道如果我使用 odbc 连接提取信息，它会以我需要的方式提取信息，但是我想要它，因此创建的 csv 文件中已经包含此信息，从而无需执行 odbc 方法。
回答by Andrew Glass
I found a solution to my problem. would slap myself.
我找到了解决我的问题的方法。会打我自己。
SELECT *
INTO OUTFILE '/xampp/tmp/Sample.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
FROM
(
SELECT 'Name' AS `E_Name`, 'Email' AS `Email`
UNION ALL
SELECT `E_Name`, `Email`
FROM `email`
) `sub_query`
before the new line i wasnt doing a return. once i added this the csv can be imported into outlook no problem. Thanks for the help though all. ill leave this open incase anyone else has the same problem.
在新线路之前，我没有做退货。一旦我添加了这个 csv 可以导入到 Outlook 中没问题。谢谢大家的帮助。如果其他人有同样的问题，我会保持开放状态。
*face palm*
*面掌*
回答by Barranka
I find this situation often. The easiest way I know is by using sed, that is a command-line utility for Unix.
我经常发现这种情况。我知道的最简单的方法是使用sed，这是 Unix 的命令行实用程序。
Since you are working on Excel, I am assuming that you don't have a *nix system (Linux, BSD, etc). So I recommend you install Cygwin (you can get it here).
由于您正在使用 Excel，因此我假设您没有 *nix 系统（Linux、BSD 等）。所以我建议你安装 Cygwin（你可以在这里得到它）。
Then, open a Cygwin bash shell and write this in the command prompt:
然后，打开 Cygwin bash shell 并在命令提示符中写入：
mysql -h yourHost -u yourUser -pYourPassword yourDatabase -e"select e_name, email from email" | sed 's/\t/,/g' > yourOutput.csv
Let me explain each piece:
让我解释一下每个部分：
mysql -h yourHost -u yourUser -pYourPassword yourDatabase -e"select..."opens a session in MySQL, connects toyourDatabaseand executes the query (the-eflag is used to execute a query). By default, the output is directed to the console as tab-separated values.- The pipe character
|is used to redirect the output of the first command to the second command, instead to direct the output to the console. sed 's/\t/,/g''substitutes every tab character (\t) with a comma> yourOutput.csvredirects the result of the command to a file, calledyourOutput.csv. You can specify a path of course.
mysql -h yourHost -u yourUser -pYourPassword yourDatabase -e"select..."在 MySQL 中打开一个会话，连接到yourDatabase并执行查询（该-e标志用于执行查询）。默认情况下，输出作为制表符分隔值定向到控制台。- 管道字符
|用于将第一个命令的输出重定向到第二个命令，而不是将输出定向到控制台。 sed 's/\t/,/g''\t用逗号替换每个制表符 ( )> yourOutput.csv将命令的结果重定向到一个名为yourOutput.csv. 您当然可以指定路径。
More information about sedand redirecting: http://lowfatlinux.com
有关sed和重定向的更多信息：http: //lowfatlinux.com
I know this can be cumbersome, but if you do this frequently, this will save you quite some time and headaches (the first time is the hardest, after a while it turns out to be very straightforward)
我知道这可能很麻烦，但如果你经常这样做，这会为你节省相当多的时间和麻烦（第一次是最难的，过一段时间就会变得非常简单）
Hope this helps
希望这可以帮助
About your comment:
关于你的评论：
will this take the headers from mysql and make them headers in excel the way the odbc connection would?
这会从 mysql 中获取头文件，并像 odbc 连接那样将它们作为 excel 头文件吗？
In layman termns: an ODBC connection makes a table or view visible to the client program, so the client can use the data contained in the table or view. That means, of course, that the column names are visible to the client.
通俗地说：ODBC 连接使客户端程序可以看到表或视图，因此客户端可以使用表或视图中包含的数据。当然，这意味着列名对客户端可见。
The solution I propose above is more like "exporting" the result of a selectstatement to a file.
我上面提出的解决方案更像是将select语句的结果“导出”到文件中。
If you don't want to install Cygwin, I have some news for you: The >redirection operator works on Windows' Command window... but I don't know of a Windows command line utility to replace characters. If you don't mind dealing with tabs instead of commas, this will work:
如果您不想安装 Cygwin，我有一些消息要告诉您：>重定向运算符适用于 Windows 的命令窗口...但我不知道 Windows 命令行实用程序可以替换字符。如果您不介意处理制表符而不是逗号，这将起作用：
mysql -h yourHost, -u yourUser -pyourPassword yourDatabase -e"select..." > yourOutputFile.txt
This will export the result of the query to the yourOutputFile.txtfile, as tab-separated values (which can be read by excel). As I mentioned in my comment, if you use aliases for your column names, these will be the column headers that will be exported.
这会将查询结果导出到yourOutputFile.txt文件中，作为制表符分隔值（可以由 excel 读取）。正如我在评论中提到的，如果您为列名使用别名，这些将是将被导出的列标题。
回答by shadow0359
Below query does union all on group concat(to dynamically fetch field names) and actual data.
下面的查询在组 concat（动态获取字段名称）和实际数据上合并所有。
-- group concat default is 1024, to avoid field names getting truncated we increase this value
SET SESSION group_concat_max_len = 10000;
SELECT
CONCAT('SELECT * INTO OUTFILE \'/home/myuser/myfile.csv\' 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;
回答by amaster
Well from my understand of INSERT INTOyour query format is wrong as it is only writing the second insert into the output file.
好吧，根据我对INSERT INTO的理解，您的查询格式是错误的，因为它只是将第二个插入内容写入输出文件。
Try this (tested)
试试这个（已测试）
SELECT *
INTO OUTFILE '/xampp/tmp/Sample.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\'
LINES TERMINATED BY '\n'
FROM
(
SELECT 'Name' AS `E_Name`, 'Email' AS `Email`
UNION ALL
SELECT `E_Name`, `Email`
FROM `email`
WHERE 1
) `sub_query`
Note:Not sure why you need the WHERE clause this can probably be taken out. The key point is to union the queries in a sub-query and then write that out to the file.
注意：不确定为什么需要 WHERE 子句，这可能可以删除。关键点是在子查询中联合查询，然后将其写入文件。
