[分享]mysql outfile infile导入导出数据_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 2448 | 回复: 0

主题： [分享]mysql outfile infile导入导出数据

DJYang

注册用户

等级：列兵
经验：91
发帖：67
精华：0
注册：2011-8-8
状态：离线
发送短消息息给DJYang

加好友发送短消息息给DJYang

发消息

发表于：

2014-11-14 16:09:42 | [全部帖] [楼主帖]

楼主

导出

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

--转自