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)