mysql outfile infile 导入导出数据

cheng029

导出

[Shell] 纯文本查看 复制代码

[plain] view plaincopy
SELECT * INTO OUTFILE '/tmp/jason.txt' FIELDS TERMINATED BY ',' FROM test.jason;  

或者

[Shell] 纯文本查看 复制代码

[plain] view plaincopy
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

导入

[Shell] 纯文本查看 复制代码

[plain] view plaincopy
LOAD DATA INFILE '/tmp/jason.txt' INTO TABLE aa.jason FIELDS TERMINATED BY ',';  

或者

[Shell] 纯文本查看 复制代码

[plain] view plaincopy
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也有同样功能

[Shell] 纯文本查看 复制代码

[plain] view plaincopy
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" 表示每个数据是以制表符分割的

cyc1111

关羽五绺长髯，风度翩翩，手提青龙偃月刀，江湖人送绰号——刀郎。

dopost

如果回帖是一种美德，那我早就成为圣人了!

色粉盒撒娇

生活***好玩，因为生活老***玩我!

zhk2369

走，MM，咱们化蝶去……

yanfei

你的丑和你的脸没有关系。。。。。。

yzwj2004

月经不仅仅是女人的痛苦，也是男人的痛苦。