如何将MySQL查询结果存储到本地CSV文件中？

How do I store a MySQL query result into a local CSV file? I don't have access to the remote machine.

如何将MySQL查询结果存储到本地CSV文件中？我无法访问远程计算机。

5 个解决方案

#1

-14

You can try doing :

你可以尝试做：

SELECT a,b,c
FROM table_name
INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

We use the OUTFILE clause here to store the output into a CSV file.
We enclose the fields in double-quotes to handle field values that have the comma in them and we separate the fields by comma and separate individual line using newline.

我们在这里使用OUTFILE子句将输出存储到CSV文件中。我们将字段括在双引号中以处理其中包含逗号的字段值，并使用逗号分隔字段并使用换行分隔单独的行。

#2

You're going to have to use the command line execution '-e' flag.

您将不得不使用命令行执行'-e'标志。

$> /usr/local/mysql/bin/mysql -u user -p -h remote.example.com -e "select t1.a,t1.b from db_schema.table1 t1 limit 10;" > hello.txt

Will generate a local hello.txt file in your current working directory with the output from the query.

将使用查询的输出在当前工作目录中生成本地hello.txt文件。

#3

Use the concat function of mysql

使用mysql的concat函数

mysql -u <username> -p -h <hostname> -e "select concat(userid,',',surname,',',firstname,',',midname) as user from dbname.tablename;" > user.csv

You can delete the first line which contains the column name "user".

您可以删除包含列名“user”的第一行。

#4

I am facing this problem and I've been reading some time for a solution: importing into excel, importing into access, saving as text file...

我正面临这个问题，我一直在寻找解决方案：导入到Excel，导入访问，保存为文本文件......

I think the best solution for windows is the following:

我认为Windows的最佳解决方案如下：

use the command insert...select to create a "result" table. The ideal scenario whould be to automatically create the fields of this result table, but this is not possible in mysql
使用命令insert ... select来创建“结果”表。理想的情况是自动创建此结果表的字段，但这在mysql中是不可能的
create an ODBC connection to the database
创建与数据库的ODBC连接
use access or excel to extract the data and then save or process in the way you want
使用access或excel提取数据，然后以您想要的方式保存或处理

For Unix/Linux I think that the best solution might be using this -e option tmarthal said before and process the output through a processor like awk to get a proper format (CSV, xml, whatever).

对于Unix / Linux，我认为最好的解决方案可能是使用前面所说的-e选项tmarthal，并通过像awk这样的处理器处理输出以获得正确的格式（CSV，xml等等）。

#5

-6

Run the MySQl query to generate CSV from your App like below

运行MySQl查询以从您的应用程序生成CSV，如下所示

SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

It will create the csv file in the tmp folder of the application.

它将在应用程序的tmp文件夹中创建csv文件。

Then you can add logic to send the file through headers.

然后，您可以添加逻辑以通过标头发送文件。

Make sure the database user has permissions to write into the remote file-system.

确保数据库用户具有写入远程文件系统的权限。

#1

-14