9、MySQL数据导出csv格式

查询

SELECT * FROM table001 into outfile '/tmp/test.csv' 
CHARACTER SET gbk 
fields terminated by ',' 
optionally enclosed by '\"';

方法二：Mysqldump（本地导出）

由于mysqldump的实现方法，其根本还是into outfile，故导出的文件也只能到本地

mysqldump -h 172.16.81.236 -uusername -ppassword 
-t -T/tmp/waring.csv nms_db table001  
--fields-enclosed-by=\" --fields-terminated-by=,

方法三：mysql（远程导出）推荐

推荐使用这个方法： mysql和sed结合的方法，将查出的文件内容使用sed进行相应的转化， 提升时间效率还能远程操作

mysql -uusername -ppassword -h 172.16.81.236 
–D my_db  
--default-character-set=gbk  
-e 'select * from server_warning_unrepaired'  | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/file.csv

数据导入

load data infile 'C:\\Users\\UserName\\Desktop\\test.csv' 
into table `table` fields terminated by ',' 
optionally enclosed by '"' 
escaped by '"' 
lines terminated by '\n';

mysql导入csv

select * from `table`
load data infile 'C:\\Users\\UserName\\Desktop\\test.csv' 
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\n';

如果乱码，可用相关编辑器打开.csv文件，另存为utf-8的csv