如何将csv导入mysql和mysql导出csv

Question

如题所述

Answer 1

（1）：mysql 下导出为指定格式的数据的外部任意文件类型 mysql>
use dbname
Database Changed
select * from pollution into outfile 'G:\\arcgisworkspace\\zypdoc\\text.txt'; (指导出数据的，注意转义字符哦)
SELECT * FROM pollution INTO OUTFILE 'G:\\arcgisworkspace\\zypdoc\\text.csv' FIELDS TERMINATED BY '\,'; (输出格式控制)
结果为：
1,汽车尾气,200
2,建筑扬沙,180
3,汽车喷漆,160
4,燃煤,240
5,其它,80
(2) 知识补充：

其中option参数常用的5个选项
FIELDS TERMINATED BY ‘字符串’：设置字符串为字段的分割符，默认值为 \t；
FIELDS ENCLOSED BY ‘字符’：设置字符串括上char varchar text等字符型字段，默认值为 无任何符号；
FIELDS OPTIONALLY ENCLOSED BY ‘字符’：设置字符串括上字段的值，默认值为 无任何符号；
LINES STARTING BY ‘字符串’：设置每一行开头的字符，默认值为 无任何字符；
FIELDS ESCAPED BY ‘字符’：设置转义字符，默认值为 \；
LINES TERMINATED BY ‘字符串’：设置每行结束符，默认值为 \n；
如：
SELECT * FROM pollution INTO OUTFILE 'G:\\arcgisworkspace\\zypdoc\\text2.csv'
FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"'
LINES STARTING BY '\>' TERMINATED BY '\r\n';

结果为：
>1,"汽车尾气","200"
>2,"建筑扬沙","180"
>3,"汽车喷漆","160"
>4,"燃煤","240"
>5,"其它","80"
(3)：mysqldump 下导出为指定格式的数据的外部任意文件类型 C:\Program Files\MySQL\MySQL Server 5.5\bin>
mysqldump -u root -p -T G:\arcgisworkspace\zypdoc\ abc pollution "--fields-terminated-by=," (记住不要任何多余的空格，也不用转移字符;-p后面也不用写password；注意目
标目录是文件夹，文件名是表名，后缀是txt文件)