mysql 10%报错_ORACLE导出文本到MYSQL 报错 Incorrect integer value: ''

今天从ORACLE导出数据文本格式的到MYSQL 5.6 发现

load data infile Incorrect date value ''

这种报错，从5.6开始在严格模式下插入数据如果是数字和日期

mysql> insert into testnull1 values('','test','');

ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

mysql> insert into testnull1 values(1,'test','');

ERROR 1292 (22007): Incorrect datetime value: '' for column 'dt' at row 1

会报错，实际上我们希望''为NULL；

5.6如果表结构是如果表是int,varchar,datetime

后如果通过 load data infile 如果记录如下

,test,

同样会把空值转换为''会报如上的错，这个时候就要用\N来代替。

那么如果从ORACLE中导出

select nvl(to_char(id),'\N'),nvl(name,'\N'),to_char(dt,'yyyy-mm-dd hh24:mi:ss') from testnulli;

将NULL值转换为\N如下：

create table testnulli

(id int,name varchar2(20),dt date);

insert into testnulli

values(1,'gaopeng',to_date('2010-10-10 12:02:01','YYYY-MM-DD HH24:MI:SS'));

insert into testnulli

values(null,'gaopeng',null);

DECLARE

row_result varchar2(4000);

selectsql  varchar2(4000);

qrycursor  SYS_REFCURSOR;

txt_handle UTL_FILE.file_type;

BEGIN

--selectsql  := 'select id || '','' || name || '','' || dti  from testdump where name = ''gaopeng''   and dti > to_date(''2015-03-17 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'')';

selectsql  := 'select nvl(to_char(id),''\N'')||'',''||nvl(name,''\N'')||'',''||nvl(to_char(dt,''yyyy-mm-dd hh24:mi:ss''),''\N'') from testnulli';

txt_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'testnull.txt', 'w', 32767);

open qrycursor for selectsql;

loop

fetch qrycursor

into row_result;

exit when qrycursor%notfound;

UTL_FILE.PUT_LINE(txt_handle, row_result);

end loop;

--关闭游标

close qrycursor;

UTL_FILE.FCLOSE(txt_handle);

end;

导出的文本如下：

1,gaopeng,2010-10-10 12:02:01

\N,gaopeng,\N

然后再次load data infile 就不会出问题

mysql> load data infile '/tmp/testnull.txt' into table testnull1 fields terminated by ',' ;

Query OK, 2 rows affected (0.01 sec)

Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from testnull1;

+------+---------+---------------------+

| id   | name    | dt                  |

+------+---------+---------------------+

|    1 | gaopeng | 2010-10-10 12:02:01 |

| NULL | gaopeng | NULL                |

+------+---------+---------------------+

2 rows in set (0.02 sec)

实际上

mysql> insert into testnull1 values(\N,'test',\N);

Query OK, 1 row affected (0.00 sec)

等价于

mysql> insert into testnull1 values(null,'test',null);

Query OK, 1 row affected (0.01 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-1815013/，如需转载，请注明出处，否则将追究法律责任。