mysql数据导出CVS问题

发布时间:2018-09-24 07:12:13编辑:丝画阁阅读(383)


MYSQL导出数据出现The MySQL server is running with the --secure-file-priv option

今天尝试使用 into outfile导出数据的时候出现错误:
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
上网查了一下,应该是mysql设置的权限,可以使用

show variables like '%secure%';查看 secure-file-priv 当前的值是什么


/etc/init.d/my.cf

保存的文件夹需要修改权限,不然不能创建导出的文件.




解决从mysql表中多列导出数据到csv合并成一列的问题

 下面两句SQL执行后,会发现表中多列在excel中合并成了一列,因为在excel中默认用逗号,作为分隔符。
FIELDS TERMINATED BY ','; 需要加上这一句代码才可以按照表数据的列数导入到excel中。

SELECT *  FROM DB.tablename   --表名
 INTO OUTFILE 'D:/sample.csv'      --导出文件位置

 FIELDS TERMINATED BY ',';         --以逗号分隔



MYSQL导出数据文件到CSV文件,带列名

很多情况下使用mysqldump命令将MYSQL数据文件以SQL语句的形式导出到文本文件不能满足用户的需求,用户不关心SQL语句,只关心数据,这个时候可以采用CSV文件来保存用户的数据,因为LINUX下面无法直接写Excel文件(可借助第三方的库实现Excel文件的读写,后续将会有文章来描述),而CSV文件支持以Excel文件格式来打开。
导出数据表中的数据到CSV文件:select  *  from  table_name  into outfile "/tmp/xxxx.csv" fields terminated by ',' lines terminated by '\n';  linux下直接用文本编辑器打开,windows下支持用Excel文件格式来打开,打开文件发现只有数据,没有表头。
带上表头导出数据到CSV文件:select * into outfile '/tmp/xxxx.csv' fields terminated by ',' lines terminated by '\n' from (select 'col1','col2','col3','col4' union select column1,column2,column3,column4 from table_name) b; 打开导出的文件就能看到对应的表头了。


MySQL导出csv乱码问题的解决

从MySQL导出数据到 csv 文件后,有时会发现用 excel 打开该导出 csv 文件显示的是乱码。这个问题是 csv 文件本身的文本编码问题导致的,解决办法:
1. 鼠标右键点击选中的 csv 文件,在弹出的菜单中选择“编辑”,则系统会用文本方
式(记事本)打开该 csv 文件;
2. 打开 csv 文件后,进行“另存为”操作,在弹出的界面底部位置有“编码”,修改
编码方式即可: 从UTF-8改成 ANSI 。保存;

3. 再用 excel 打开后,显示汉字正常。



对于中文乱码的问题,有两种解决方案
1.user表是utf8编码,excel默认编码格式是GBK,excel直接打开时候是一堆乱码。后来想了一个办法中转,先保存成为txt格式,excel打开txt时候会提示选择用哪种编码方式打开,选择utf8解决。
2.导出时候加上CHARACTER SET gbk 即可【推荐使用】



关于数据格式化的问题,需要给导出的字段传递一些参数
1 fields terminated by ',' optionally enclosed by '"' escaped by '"'   
2 lines terminated by '\r\n'   
这个参数是根据RFC4180文档设置的,该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files,其中详细描述了CSV格式,其要点包括:
(1)字段之间以逗号分隔,数据行之间以\r\n分隔;
(2)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。

terminated by分隔符:意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符
terminated by描述字段的分隔符,默认情况下是tab字符(\t)
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠(backslash:\ )  
 
一个完整的实例如下所示:
 select id , tel,email from hr_users limit 3 into outfile '/tmp/junjun.csv' character set gbk
 fields terminated by ',' optionally enclosed by '"'
 lines terminated by '\n';



可以使用 into outfile, fields terminated by, optionally enclosed by, line terminated by语句实现导出csv

语句的格式与作用
into outfile ‘导出的目录和文件名’
指定导出的目录和文件名
fields terminated by ‘字段间分隔符’
定义字段间的分隔符
optionally enclosed by ‘字段包围符’
定义包围字段的字符(数值型字段无效)
lines terminated by ‘行间分隔符’


最终导出语句:

SELECT *
  orderimg FROM 表名 WHERE create_time>=UNIX_TIMESTAMP('2018-08-01') AND STATUS=1 ORDER BY id DESC  INTO OUTFILE '/secure-file-priv自已设置的文件夹/20180801.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';






关键字