将 Mysql 查询的结果导出到 excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10295228/
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
Exporting results of a Mysql query to excel?
提问by Priya
My requirement is to store the entire results of the query
我的要求是存储查询的全部结果
SELECT * FROM document
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)
to an Excel file.
到 Excel 文件。
回答by Roland Bouman
The typical way to achieve this is to export to CSV and then load the CSV into Excel.
You can using any MySQL command line tool to do this by including the INTO OUTFILEclause on your SELECTstatement:
实现此目的的典型方法是导出为 CSV，然后将 CSV 加载到 Excel 中。
您可以使用任何 MySQL 命令行工具通过INTO OUTFILE在SELECT语句中包含子句来执行此操作：
SELECT ... FROM ... WHERE ...
INTO OUTFILE 'file.csv'
FIELDS TERMINATED BY ','
See this linkfor detailed options.
有关详细选项，请参阅此链接。
Alternatively, you can use mysqldump to store dump into a separated value format using the --tab option, see this link.
或者，您可以使用 mysqldump 使用 --tab 选项将转储存储为分隔值格式，请参阅此链接。
mysqldump -u<user> -p<password> -h<host> --where=jtaskResult=2429 --tab=<file.csv> <database> TaskResult
回答by Daniel Adenew
Good Example can be when incase of writing it after the end of your query if you have joins or where close :
好的例子可以是在查询结束后编写它时，如果您有连接或关闭位置：
select 'idPago','fecha','lead','idAlumno','idTipoPago','idGpo'
union all
(select id_control_pagos, fecha, lead, id_alumno, id_concepto_pago, id_Gpo,id_Taller,
id_docente, Pagoimporte, NoFactura, FacturaImporte, Mensualidad_No, FormaPago,
Observaciones from control_pagos
into outfile 'c:\data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n');
回答by Aditya Dwivedi
Use the below query:
使用以下查询：
SELECT * FROM document INTO OUTFILE 'c:/order-1.csv' FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';
回答by Kemin Zhou
In my case, I need to dump the sql result into a file on the client side. This is the most typical use case to off load data from the database. In many situations, you don't have access to the server or don't want to write your result to the server.
就我而言，我需要将 sql 结果转储到客户端的文件中。这是从数据库中卸载数据的最典型用例。在许多情况下，您无权访问服务器或不想将结果写入服务器。
mysql -h hostname -u username -ppwd -e "mysql simple sql statement that last for less than a line" DATABASE_NAME > outputfile_on_the.client
The problem comes when you have a complicated query that last for several lines; you cannot use the command line to dump the result to a file easily. In such cases, you can put your complicated query into a file, such as longquery_file.sql, then execute the command.
当您有一个持续多行的复杂查询时，就会出现问题；您不能使用命令行轻松地将结果转储到文件中。在这种情况下，您可以将复杂的查询放入一个文件中，例如 longquery_file.sql，然后执行该命令。
mysql -h hn -u un -ppwd < longquery_file.sql DBNAME > output.txt
This worked for me. The only difficulty with me is the tab character; sometimes I use for group_cancat(foo SEPARATOR 0x09) will be written as '\t' in the output file. The 0x09 character is ASCII TAB. But this problem is not particular to the way we dump sql results to file. It may be related to my pager. Let me know when you find an answer to this problem. I will update this post.
这对我有用。我唯一的困难是制表符；有时我使用的 group_cancat(foo SEPARATOR 0x09) 会在输出文件中写为 '\t' 。0x09 字符是 ASCII TAB。但是这个问题并不是我们将sql结果转储到文件的方式所特有的。可能和我的寻呼机有关。当您找到此问题的答案时，请告诉我。我会更新这个帖子。
回答by Anthony
This is an old question, but it's still one of the first results on Google. The fastest way to do this is to link MySQL directly to Excel using ODBC queries or MySQL For Excel. The latter was mentioned in a comment to the OP, but I felt it really deserved its own answer because exporting to CSV is not the most efficient way to achieve this.
这是一个古老的问题，但它仍然是 Google 上的第一个结果。最快的方法是使用 ODBC 查询或 MySQL For Excel 将 MySQL 直接链接到 Excel。在对 OP 的评论中提到了后者，但我觉得它确实值得自己回答，因为导出到 CSV 并不是实现这一目标的最有效方法。
ODBC Queries- This is a little bit more complicated to setup, but it's a lot more flexible. For example, the MySQL For Excel add-in doesn't allow you to use WHEREclauses in the query expressions. The flexibility of this method also allows you to use the data in more complex ways.
ODBC 查询- 这设置起来有点复杂，但它更灵活。例如，MySQL For Excel 加载项不允许您WHERE在查询表达式中使用子句。这种方法的灵活性还允许您以更复杂的方式使用数据。
MySQL For Excel- Use this add-in if you don't need to do anything complex with the query or if you need to get something accomplished quickly and easily. You can make views in your database to workaround some of the query limitations.
MySQL For Excel- 如果您不需要对查询执行任何复杂的操作，或者如果您需要快速轻松地完成某些操作，请使用此加载项。您可以在数据库中创建视图以解决一些查询限制。
