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 ; ------行以 结束
上面的 导出文件夹 需要手动创建，否则会报错：ERROR 1 (HY000): Cant create/write to file mpDKX.csv (Errcode: 2)
select * from test_info into outfile /tmp/test.csv fields terminated by , optionally enclosed by " escaped by " lines terminated by ;
注意：
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 ;
#insert
load data infile /tmp/test.csv into table test_info fields terminated by , optionally enclosed by " escaped by " lines terminated by ;
#replace
load data infile /tmp/test.csv repalce into table test_info fields terminated by , optionally enclosed by " escaped by " lines terminated by ;
场景：表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/ /","/g;s/^/"/;s/$/"/;s/ //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 ; ------行以 结束
