mysql导出数据到csv的方式

注：

MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这个原因其实很简单，是因为在安装MySQL的时候限制了导入与导出的目录权限

只能在规定的目录下才能导入

我们需要通过下面命令查看 secure-file-priv 当前的值是什么

mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.00 sec)

mysql>

一、导出到csv

通过mysql客户端shell连接到服务器，选择使用的数据库，输入sql代码： 

select * from test_info   

into outfile '/tmp/test.csv'   

fields terminated by ','　　　 ------字段间以,号分隔

optionally enclosed by '"'　　------字段用"号括起

escaped by '"'   　　　　　　------字段中使用的转义符为"

lines terminated by '\r\n';　　------行以\r\n结束

上面的 导出文件夹 需要手动创建，否则会报错：ERROR 1 (HY000): Can't create/write to file '\tmp\DKX.csv' (Errcode: 2)

select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

 

 注意：

　　where gscode = 'BS监控'  有中文，导不出数据。

　　解决方法：

　　　　where gscode like 'BS%'

　　

 

　　

二、csv文件导入

load data infile '/tmp/test.csv'   

into table test_info    

fields terminated by ','  

optionally enclosed by '"' 

escaped by '"'   

lines terminated by '\r\n'; 

 

#insert

load data infile '/tmp/test.csv' into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

#replace

load data infile '/tmp/test.csv' repalce into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

场景：表1中的数据需要导入表2（表1、2结构相同，都有自增id字段）。表1中的自增id字段不要导出，让其在数据导入表2时自动生成，避免可能出现重复的自增id。

　　导出：

　　　　

 

　　导入：

　　　　

 

　　　　注意：

　　　　　　如果是远程连接的数据库，导出、导入时可能报错：ERROR 1045 (28000): Access denied for user 'quantuser'@'%' (using password: YES)

　　　　　　

 

　　　　　　

　　　　解决方法：

　　　　　　导出：(-N 不导出标题行)

　　　　　　mysql -h host -u user --password=pwd dbname  -N -e "select * from table" > D:/tmp/test.csv

　　　　　　

 

　　　　　　导入：

　　　　　　登录，连接上数据库之后

　　　　　　用 load data local  infile 'XXX.csv' （如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。(LOCAL在MySQL3.22.6或以后版本中可用。））

　　　　　　

 

三、把从mongodb 中导出的csv文件，导入到 mysql

　　导出：

　　　　

　　

　　导入mysql:

　　　　

 

效果：

　　

　　

　　注意：

　　　　导入、导出的两个mysql 的数据库属性要一致，否则导入数据之后，中文字段是乱码的。

　　　　

四、常用

mysql -h ip -uroot -p  -Bse "SELECT * FROM _TABLE" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > _FILE_NAME.csv

select * from _TABLE into outfile '_FILE_NAME.csv' fields terminated by ',' optionally enclosed by '\"' escaped by '\"';"
 

fields terminated by ','　　　 ------字段间以,号分隔

optionally enclosed by '"'　　------字段用"号括起

escaped by '"'   　　　　　　------字段中使用的转义符为"

lines terminated by '\r\n';　　------行以\r\n结束