mysql outfile infile导入导出数据_MySQL

bitsCN.com

mysql outfile infile导入导出数据

 

导出

[plain] 

SELECT * INTO OUTFILE '/tmp/jason.txt' FIELDS TERMINATED BY ',' FROM test.jason;  

 

或者

[plain] 

SELECT * INTO OUTFILE '/tmp/jason1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '/n' FROM test.jason1;  

输出：

        "2013-04-09 00:06:56","100000198108800","EXPORT_INFO","BUY_ITEM_INNER",65,10,1004,10,"11","2",100

        "2013-04-09 00:06:59","1068029027","EXPORT_INFO","BUY_ITEM_INNER",16,7,304,7,"11","2",70

        "2013-04-09 00:08:27","100000198108800","EXPORT_INFO","BUY_ITEM_INNER",65,2,1004,2,"11","2",20

 

 

 

导入

[plain] 

LOAD DATA INFILE '/tmp/jason.txt' INTO TABLE aa.jason FIELDS TERMINATED BY ',';  

 

或者

 

[plain] 

LOAD DATA INFILE '/tmp/jason1.txt' INTO TABLE aa.jason1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '/n';  

 

总结：

FIELDS TERMINATED BY ',' 字段间分割符

OPTIONALLY ENCLOSED BY '"' 将字段包围 对数值型无效

LINES TERMINATED BY '/n' 换行符

 

mysqldump也有同样功能

 

[plain] 

mysqldump -uroot -p -T /tmp test fi --fields-enclosed-by=/" --fields-terminated-by="/t"  

 

fi 是导出的文件名，默认添加.txt

test 是要导出的库名

 

-T /tmp 是导出的目录位置

 

--fields-enclosed-by=/" 是每个数据都在双引号里面

 

--fields-terminated-by="/t" 表示每个数据是以制表符分割的

bitsCN.com