MYSQLloaddatainfile的故障排除

一、案例

今天用户使用informatica从ORACLE抽数到MYSQL，结果报错，信息如下：‘incorrect datetime value '' for column 'CREATED_DATE'’

二、问题

为什么会产生错误‘incorrect datetime value '' for column 'CREATED_DATE'’？

三、数据收集及分析

3.1 收集错误信息的触发条件

经与用户沟通，该错误信息是informatica执行如下语句时产生的。

load data infile '/tmp/T1.out'
into table sdm.t1 character set UTF8
fields terminated by ','
endclosed by '"'
lines terminated by '/n';

分析：分析以上语法正常，打开T1.out文件发现在CREATED_DATE字段没有值，只有",,"这样的表示。初步怀疑是由于这个造成的。

3.2 网上收集案例

通过收集网上的案例发现，这是由于load data infile这个命令对数据库格式的要求问题，如果你的数据文件中的字段时是NULL的话，必须要在数据文件中写入NULL这个字符，不能是",,"这样的表示，具体如下：

错误的例子,不能正常导入：
001,,Mars

正确的例子，能正导入：
001,NULL,Mars

分析：根据正确的例子编辑数据文件，结果能重新导入

3.3 反馈用户

将正确的数据文件格式反馈给用户，但是用户说在informatica里没有参数直接控制转存的文件用NULL代替",," ,但是好幸运，刚好有一个同事处理过类似的问题，给出如下代码：

load data infile '/tmp/T1.out'
into table sdm.t1 character set UTF8
fields terminated by ','
endclosed by '"'
lines terminated by '/n'
(id,@created_date,created_by)
set created_date=if(@created_date='',NULL,@created_date)
;

分析：测试成功

3.4 另一种解决方案

在my.ini里找到 
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” 
把其中的STRICT_TRANS_TABLES,去掉, 
或者把sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
注释
最后，重启mysql即可

分析：去掉STRICT_TRANS_TABLES后，不能严格保证数据健康，对数据有强一致要求的不建议去掉。

四、结论

mysql在执行load data infile时不能处理隐式null即",,"所致，综上所述解决方案：

数据文件使用显式的NULL表达。
load data infile中使用set 语法对",,"进行处理。
修改sql_mode参数，去掉STRICT_TRANS_TABLES

五、题外话

当我将informatica的数据文件上传到mysql服务器上执行load data infile时又出现另一个错误 ”data too long for column.....“,经过收集案例，解决方案如下：

我的数据文件不是UTF8格式，需要转换。
在MYSQL命令行界面使用set names utf8;
同样去掉sql_mode中的strict_trans_tables;