mysql&Oracle导入导出txt格式的数据



一: mysql导入数据load data infile用法

导出一张表的数据，以',' 为分隔符

select * from pet  into  outfile 'c:/pet.txt'   fields terminated by ',' lines terminated by '\r\n';

select * from pet  into  outfile 'c:/pet1.txt'   fields terminated by ','  enclosed by '"' lines terminated by '\r\n';

enclosed by '"' : 数据导出都是带 引号的 "Fluffy","Harold","cat","f","1993-02-04"

导入一张表的数据，以',' 为分隔符

load data local infile 'c:/pet.txt' into table pet  fields terminated by ','  lines terminated by '\r\n'

load data local infile 'c:/pet.txt' into table pet  fields terminated by ','  lines terminated by '\r\n' (name,owner,species,sex,birth,death) ;

load data local infile 'c:/pet1.txt' into table pet  fields terminated by ',' enclosed by '"'  lines terminated by '\r\n';

 enclosed by'"' 导入带引号 "Fluffy","Harold","cat","f","1993-02-04" 格式的数据

 注意： （）括号里面的字段可以控制导入的列数量

 

基本语法：

load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]
[lines terminated by'n']
[ignore number lines]
[(col_name, )]

 

1 如果你指定关键词low_priority，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据。可以使用如下的命令：

load data low_priority infile "/home/mark/data sql" into table Orders;

2 如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上。

3 replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行。如果你指定ignore，跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略。例如：

load data low_priority infile "/home/mark/data sql" replace into table Orders;

4分隔符

terminated by分隔符：意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符

terminated by描述字段的分隔符，默认情况下是tab字符（\t）
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

5.字符集

character set gbk;

这个字符集一定要写，要不然就会乱码或者只导入一部分数据。

load data local infile 'c:/pet1.txt' into table  character set gbk pet  fields terminated by ',' enclosed by '"'  lines terminated by '\r\n';

二：Oracle导入数据load data infile用法

1. 输出txt格式的脚本

set trimspool on
set linesize 40000
set pagesize 0
set newpage none --会在页和页面之间没有任何间隔
set heading off   --是否显示列标题
set term off  --输出的文件不会显示在屏幕上，可以提高spool的速度
set feedback off  --是否显示当前sql语句查询或修改的行数
set trims on  --将spool输出中每行后面多余的空格去掉
spool c:/s24_tbl_acct.txt

select

XACCOUNT        ||'|'||

BANK            ||'|'||

BUSINESS        ||'|'||

CATEGORY        ||'|'||
NCRED_RSN   

from tbl_acct_201212;
spool off;
exit ;

  

----------------------------------------------------------------------------------------------

Oracle通过sqlplus spool导出数据

spool常用的设置
set colsep' ';    //域输出分隔符
set echo off;    //显示start启动的脚本中的每个sql命令，缺省为on
set feedback off;  //回显本次sql命令处理的记录条数，缺省为on
set heading off;   //输出域标题，缺省为on
set pagesize 0;   //输出每页行数，缺省为24,为了避免分页，可设定为0。
set termout off;   //显示脚本中的命令的执行结果，缺省为on
set trimout on;   //去除标准输出每行的拖尾空格，缺省为off
set trimspool on;  //去除重定向（spool）输出每行的拖尾空格，缺省为off

注：LINESIZE要稍微设置大些，免得数据被截断， 它应和相应的TRIMSPOOL结合使用防止导出的文本有太多的尾部空格。但是如果 LINESIZE设置太大，会大大降低导出的速度 。

SQL>set colsep' ';     //-域输出分隔符

SQL>set newp none            //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的

SQL>set echo off;     //显示start启动的脚本中的每个sql命令，缺省为on

SQL> set echo on               //设置运行命令是是否显示语句

SQL> set feedback on;       //设置显示“已选择XX行”

SQL>set feedback off;      //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行"

SQL>set heading off;     //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据

SQL>set pagesize 0;      //输出每页行数，缺省为24,为了避免分页，可设定为0。

SQL>set linesize 80;      //输出一行字符个数，缺省为80

SQL>set numwidth 12;     //输出number类型域长度，缺省为10

SQL>set termout off;     //显示脚本中的命令的执行结果，缺省为on

SQL>set trimout on;   //去除标准输出每行的拖尾空格，缺省为off

SQL>set trimspool on;  //去除重定向（spool）输出每行的拖尾空格，缺省为off

SQL>set serveroutput on;  //设置允许显示输出类似dbms_output

SQL> set timing on;          //设置显示“已用时间：XXXX”

SQL> set autotrace on-;    //设置允许对执行的sql进行分析

set verify off                     //可以关闭和打开提示确认信息old 1和new 1的显示. 

ksh脚本处理：



Ruby代码 
#!/bin/ksh  
DB_USER=scott                               #DB USER  
DB_PWD=tiger                              #DB PASSWORD  
DB_SERV=orcl                                #DB SERVICE NAME  
returnMessage=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<<EOF  
whenever sqlerror exit 1  
whenever oserror exit 2  
set pagesize 0 feedback off verify off heading off  
set time off echo off  
set pagesize 0  
set linesize 2000  
set trims on  
set feedback off  
set TERMOUT OFF  
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';  
spool sp_test.txt  
select PK_ID || '|' ||GRID_ID || '...' from tmp_jy_sys_table_col;  
spool off  
/  
exit 3;  
EOF` 

#!/bin/ksh
DB_USER=tivan                               #DB USER
DB_PWD=tivan                               #DB PASSWORD
DB_SERV=orcl                                #DB SERVICE NAME
returnMessage=`sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<<EOF
whenever sqlerror exit 1
whenever oserror exit 2
set pagesize 0 feedback off verify off heading off
set time off echo off
set pagesize 0
set linesize 2000
set trims on
set feedback off
set TERMOUT OFF
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
spool sp_test.txt
select PK_ID || '|' ||GRID_ID || '...' from tmp_jy_sys_table_col;
spool off
/
exit 3;
EOF`

-EOF-

2. 导入txt格式的脚本

1.建一个ctl文件内容如下

load data
infile 'C:\Users\Administrator\Desktop\exp\gsyh.txt'
append into table JCPT_CompanyAnnouncement
fields terminated by '&&&' optionally enclosed by '\n'
trailing nullcols(
ID "SEQ_TEST.NEXTVAL" , --主键自动增长列
COMPANYID,
ANNOUNCEMENTDATE date"yyyy-mm-dd",
TITLE,
ABSTRACT,
THEME,
PUBLISHTIME       date"yyyy-mm-dd hh24:mi:ss",
ENDATE,
BODY,
FILEURL,
FILEMD5
) 

2.建一个 bat 文件，linux 用sh文件 内容如下

sqlldr userid=developer/developer_ora__123_456@BankAssist control=C:\Users\Administrator\Desktop\exp\control.ctl log=C:\Users\Administrator\Desktop\exp\gdyh.log

3.这个目录下弄个数据文件C:\Users\Administrator\Desktop\exp\gsyh.txt