一、案例
今天用户使用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;