重庆思庄Oracle、Redhat认证学习论坛

标题: 数据导入导出的一些选项格式 [打印本页]

作者: jikcheng 时间: 2014-11-12 15:48
标题: 数据导入导出的一些选项格式
使用select ...into outfile 'file.name'

select * from city into outfile 'city.bak' fields TERMINATED BY ','

TERMINATED BY 指定间隔符号
示例：导出的文件。

1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800
5,Amsterdam,NLD,Noord-Holland,731200
6,Rotterdam,NLD,Zuid-Holland,593321
7,Haag,NLD,Zuid-Holland,440900
8,Utrecht,NLD,Utrecht,234323
9,Eindhoven,NLD,Noord-Brabant,201843
10,Tilburg,NLD,Noord-Brabant,193238
11,Groningen,NLD,Groningen,172701
12,Breda,NLD,Noord-Brabant,160398

引用字符：
(root@localhost) [world]> (root@localhost) [world]> select * from City into outfile 'city.bak' fields TERMINATED BY ',' ENCLOSED BY '"';
示例：
"1","Kabul","AFG","Kabol","1780000"
"2","Qandahar","AFG","Qandahar","237500"
"3","Herat","AFG","Herat","186800"
"4","Mazar-e-Sharif","AFG","Balkh","127800"
"5","Amsterdam","NLD","Noord-Holland","731200"
"6","Rotterdam","NLD","Zuid-Holland","593321"
"7","Haag","NLD","Zuid-Holland","440900"
"8","Utrecht","NLD","Utrecht","234323"
"9","Eindhoven","NLD","Noord-Brabant","201843"
"10","Tilburg","NLD","Noord-Brabant","193238"
"11","Groningen","NLD","Groningen","172701"
"12","Breda","NLD","Noord-Brabant","160398"
"13","Apeldoorn","NLD","Gelderland","153491"
"14","Nijmegen","NLD","Gelderland","152463"

如果你设置了自己的转义字符来代表，回车，换行。制表符。导出时也是按照自己的转义字符来定义。
select * from City into outfile 'city.bak' fields TERMINATED BY ',' ENCLOSED BY '"' escaped by 'i'

指定换行符：
LINES TERMINATED BY

select * from City into outfile 'city.bak' fields TERMINATED BY ',' ENCLOSED BY '"' escaped by 'i' LINES TERMINATED BY '\r' ;

示例:
"1","Kabul","AFG","Kabol","1780000"^M"2","Qandahar","AFG","Qandahar","237500"^M"3","Herat","AFG","Herat","186800"^M"4","Mazar-e-Shariif","AFG","Balkh","127800"^M"5","Amsterdam","NLD","Noord-Holland","731200"^M"6","Rotterdam","NLD","Zuiid-Holland","593321"^M"7","Haag","NLD","Zuiid-Holland","440900"^M"8","Utrecht","NLD","Utrecht","234323"^M"9","Eiindhoven","NLD","Noord-Brabant","201843"^M"10","Tiilburg","NLD","Noord-Brabant","193238"^M"11","Groniingen","NLD","Groniingen","172701"^M"12","Breda","NLD","Noord-Brabant","160398"^M"13","Apeldoorn","NLD","Gelderland","153491"^M"14","Niijmegen","NLD","Gelderland","152463"^M"15","Enschede","NLD","Overiijssel","149544"^M"16","Haarlem","NLD","Noord-Holland","148772"^M"17","Almere","NLD","Fl

指定换行符后导出的文件格式就成了这样。建议不修改换行符格式。
用什么格式导出的。就用什么格式导入
select * from City into outfile 'city.bak' fields TERMINATED BY ',' ENCLOSED BY '"' escaped by 'i' LINES TERMINATED BY '\r' ;

load data infile 'city.bak' into table city_copy fields TERMINATED BY ',' ENCLOSED BY '"' escaped by 'i' LINES TERMINATED BY '\r' ;

导出的格式文件。都在服务器端。要想从客户端的机器上导入数据。需要加上local 选项。
示例：
(win_ssl@192.168.154.190) [world]> load data local infile 'city.bak' into table
city_copy fields TERMINATED BY ',' ENCLOSED BY '"' escaped by 'i' LINES TERMINA
TED BY '\r' ;

city.bak 是从linux主机上传送回来的。

作者: 郑全 时间: 2014-11-12 17:53

在oracle中通过 spool方式产生的类似输出，

"1","Kabul","AFG","Kabol","1780000"
"2","Qandahar","AFG","Qandahar","237500"
"3","Herat","AFG","Herat","186800"
"4","Mazar-e-Sharif","AFG","Balkh","127800"
"5","Amsterdam","NLD","Noord-Holland","731200"
"6","Rotterdam","NLD","Zuid-Holland","593321"
"7","Haag","NLD","Zuid-Holland","440900"
"8","Utrecht","NLD","Utrecht","234323"
"9","Eindhoven","NLD","Noord-Brabant","201843"
"10","Tilburg","NLD","Noord-Brabant","193238"
"11","Groningen","NLD","Groningen","172701"
"12","Breda","NLD","Noord-Brabant","160398"
"13","Apeldoorn","NLD","Gelderland","153491"
"14","Nijmegen","NLD","Gelderland","152463"

是否也可以导入到 mysql

作者: 郑全 时间: 2014-11-12 17:54

这里的导入，有点象oracle里面的 sqlloader吧。

作者: jikcheng 时间: 2014-11-12 20:07
通过 oracle 导出的格式化文本是可以导入MySQL中的。设置好格式就可以无错的导入导出

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)