如何在mysql中导出csv文件到我的电脑 [英] How do I export csv file to my computer in mysql

问题描述

我正在尝试以csv格式将表从远程服务器导出到桌面计算机。我有这个代码：

  select * from order 
 into outfile'C：\Users\Sleep Shop \\ Desktop\MySQL脚本/ outfile.csv'
 FIELDS TERMINATED BY'，'
 ENCLOSED BY''
 ESCAPED BY'\\'
 LINES TERMINATED BY' \\\
'; 
  

但是我收到这个错误：

 失败：无法创建/写入文件'/ var / lib / mysql / C：\Users\Sleep Shop\Desktop\MySQL Scripts / outfile .csv'（Errcode：2）
  

我在想有一些根本的东西我不明白关于这个过程，可能是在远程服务器上做的表，有人可以帮忙吗？

更新到问题：
我用这个代码告诉在服务器上点击创建文件：

select * from orders
into outfile'/var/www/test/outfile.csv'
''
'''
' \'
LINES TERMINATED BY'\\\
';

它创建文件，但不包含任何记录，我收到此错误：

失败：字段分隔符参数不是预期的;

解决方案

更改这样的查询：

 code> select * from`order` 
 into outfile'export.csv'
 FIELDS TERMINATED BY'，'
 OPTIONALLY ENCLOSED BY''''
 ESCAPED BY'\\ \\\'
 LINES TERMINATED BY'\r\\\
'; 
  

然后您将在远程服务器的目录中找到该文件：/var/lib/mysql/export.csv（或可能/var/lib/mysql/data/your-db-name/export.csv）

通过SSH连接到您的服务器（使用putty ），并将文件传输到PC或将文件移动到接受FTP访问的目录，您可以使用FTP客户端（即filezilla，winSCP）进行下载。或者您可以使用phpMyAdmin并单击表格，然后单击导出选项卡，然后您会看到一个选项，从格式dr选择CSV如果您的表格太大（取决于phpMyAdmin的设置或PHP脚本可运行多长时间的设置），则可能无法正常工作。

I am trying to export a table from a remote server to my desktop computer in csv format. I have this code:

select * from order
into outfile 'C:\Users\Sleep Shop\Desktop\MySQL Scripts/outfile.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

but I get this error:

failed : Can't create/write to file '/var/lib/mysql/C:\Users\Sleep Shop\Desktop\MySQL Scripts/outfile.csv' (Errcode: 2)

I'm thinking there is something fundamental I don't understand about this procedure, probably something to do the table being at a remote server. Can anyone help?

UPDATE TO PROBLEM: I used this code to tell a spot on the server to create the file:

select * from orders into outfile '/var/www/test/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\n';

It creates the file but it contains no records and I get this error:

failed : Field separator argument is not what is expected;

解决方案

Change the query like this:

select * from `order`
into outfile 'export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Then you will find the file in your remote server's directory here: /var/lib/mysql/export.csv (or possibly /var/lib/mysql/data/your-db-name/export.csv)

Connect to your server via SSH (use putty) and transfer the file to your PC or move the file to a directory that accepts FTP access and you can download it using an FTP client (ie. filezilla, winSCP).

Or you can use phpMyAdmin and click on the table, then click the "export" tab, and then you will see an option to select "CSV" from the format dropdown. This may not work if your table is too large (depends on phpMyAdmin's settings or PHP's settings on how long a script can run).

这篇关于如何在mysql中导出csv文件到我的电脑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！