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文件。
5 个解决方案
#1
74
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 OUTFILE clause on your SELECT statement:
实现这一点的典型方法是导出到CSV，然后将CSV加载到Excel中。您可以使用任何MySQL命令行工具来实现这一点，方法是在SELECT语句中包含INTO OUTFILE子句:
SELECT ... FROM ... WHERE ...
INTO OUTFILE 'file.csv'
FIELDS TERMINATED BY ','
See this link for 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
#2
6
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');
#3
5
Use the below query:
使用以下查询:
SELECT * FROM document INTO OUTFILE 'c:/order-1.csv' FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';
#4
1
In my case, I need to dump the sql result into a file on the client side. This is the most typical situation. You don't have access to the server or you don't want to write your result to the server. The problem is that you have a complicated query that last for several lines; you cannot use the command line to dump the result to a file easily.
在我的例子中，我需要将sql结果转储到客户端上的文件中。这是最典型的情况。您没有访问服务器的权限，或者不希望将结果写入服务器。问题是，您有一个复杂的查询，可以持续数行;不能使用命令行将结果轻松地转储到文件中。
mysql -h hostname -d databasename -u username -ppwd -e "mysql simple sql statement that last for less than a line" > outputfile_on_the.client
you can put your complicated query into a file: longquery_file.sql the execute the command
您可以将复杂的查询放入一个文件:longquery_file中。sql执行命令
mysql -h hn -d dn -u un -ppwd < longquery_file.sql > 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分离器0x09)的制表符字符将在输出文件中写成'\t'。0x09字符是ASCII选项卡。但是这个问题并不是我们将sql结果转储到文件中的特殊方式。可能和我的传呼机有关。当你找到这个问题的答案时，请告诉我。我将更新这篇文章。
#5
0
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.
这是一个老问题，但它仍然是谷歌上的第一个结果。最快的方法是将MySQL直接链接到Excel中，使用ODBC查询或MySQL。在对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 WHERE clauses in the query expressions. The flexibility of this method also allows you to use the data in more complex ways.
ODBC查询—这对设置来说有点复杂，但是它更加灵活。例如，Excel外接程序的MySQL不允许在查询表达式中使用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的Excel -如果你不需要做任何复杂的查询，或者你需要快速简单地完成一些事情，使用这个插件。您可以在数据库中创建视图来解决一些查询限制。
#1
74
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 OUTFILE clause on your SELECT statement:
实现这一点的典型方法是导出到CSV，然后将CSV加载到Excel中。您可以使用任何MySQL命令行工具来实现这一点，方法是在SELECT语句中包含INTO OUTFILE子句:
SELECT ... FROM ... WHERE ...
INTO OUTFILE 'file.csv'
FIELDS TERMINATED BY ','
See this link for 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
#2
6
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');
#3
5
Use the below query:
使用以下查询:
SELECT * FROM document INTO OUTFILE 'c:/order-1.csv' FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\n';
#4
1
In my case, I need to dump the sql result into a file on the client side. This is the most typical situation. You don't have access to the server or you don't want to write your result to the server. The problem is that you have a complicated query that last for several lines; you cannot use the command line to dump the result to a file easily.
在我的例子中，我需要将sql结果转储到客户端上的文件中。这是最典型的情况。您没有访问服务器的权限，或者不希望将结果写入服务器。问题是，您有一个复杂的查询，可以持续数行;不能使用命令行将结果轻松地转储到文件中。
mysql -h hostname -d databasename -u username -ppwd -e "mysql simple sql statement that last for less than a line" > outputfile_on_the.client
you can put your complicated query into a file: longquery_file.sql the execute the command
您可以将复杂的查询放入一个文件:longquery_file中。sql执行命令
mysql -h hn -d dn -u un -ppwd < longquery_file.sql > 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分离器0x09)的制表符字符将在输出文件中写成'\t'。0x09字符是ASCII选项卡。但是这个问题并不是我们将sql结果转储到文件中的特殊方式。可能和我的传呼机有关。当你找到这个问题的答案时，请告诉我。我将更新这篇文章。
#5
0
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.
这是一个老问题，但它仍然是谷歌上的第一个结果。最快的方法是将MySQL直接链接到Excel中，使用ODBC查询或MySQL。在对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 WHERE clauses in the query expressions. The flexibility of this method also allows you to use the data in more complex ways.
ODBC查询—这对设置来说有点复杂，但是它更加灵活。例如，Excel外接程序的MySQL不允许在查询表达式中使用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的Excel -如果你不需要做任何复杂的查询，或者你需要快速简单地完成一些事情，使用这个插件。您可以在数据库中创建视图来解决一些查询限制。