你好,游客 登录 注册 搜索
背景:
阅读新闻

MySQL load 报错Incorrect datetime value

[日期:2017-03-19] 来源:Linux社区  作者:贺子_DBA [字体: ]

有个需求要从Oracle把数据倒进MySQL

方法一:用spool导成txt文件,然后load进去mysql

oracle源端:spool配置

[oracle11@S248 ~]$ cat spool_bak.sql

set colsep '|'

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

spool /oracle/spool1.txt

select swebid,dstart from liuwenhe.fukuandate  where dstart is not null and  swebid is not null ;

spool off

EOF

运行spool文件,生成txt文件

SQL> @spool_bak.sql

把生成的txt文件传到mysql服务器下。

然后执行load进去mysql

mysql> LOAD DATA INFILE 'C:\\Users\\manet\\Desktop\\spool.txt'  INTO TABLE fukuandate  FIELDS TERMINATED  BY "|"

不幸的是报错:

Incorrect datetime value: '15-SEP-15' for column 'DSTART' at row 1

很明显是日期格式的问题,查看导出来的txt文件内容:

[oracle11@S248 ~]$ cat spool1.txt

403965|15-SEP-15

442917|01-AUG-16

然而mysql的默认日期格式:为yyy-mm-dd

mysql> show variables like 'datetime_format';      ####查看mysql的默认格式

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

| Variable_name  | Value            |

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

| datetime_format | %Y-%m-%d %H:%i:%s |

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

1 row in set (0.00 sec)

mysql> SELECT NOW() FROM DUAL;

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

| NOW()              |

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

| 2017-03-03 13:32:18 |

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

1 row in set (0.00 sec)

于是猜想把数据中的日期改成yyyy-mm-dd的形式,修改oraclespool.sql脚本,把日期格式改成yyyy-mm-dd的形式。

[oracle11@S248 ~]$ cat  spool.sql

set colsep '|'

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

spool /oracle/spool.txt

select swebid,to_char(dstart,'yyyy-mm-dd') from liuwenhe.fukuandate  where dstart is not null and  swebid is not null;

spool off

EOF

然后从新执行spool.sql脚本,新生成的txt文件,再次load进去,成功了。。

mysql> LOAD DATA INFILE 'C:\\Users\\manet\\Desktop\\spool.txt'  INTO TABLE fukuandate  FIELDS TERMINATED  BY "|"

共 73506 行受到影响

方法二:注意这种方法仅仅适合于数据量比较小的情况,因为数据量大的话,你没办法编辑sql文件。用plsql developer 工具导成sql文件,然后修改sql文件,倒进mysql。

在plsql developer 工具里,在左侧找到需要导出的表,然后-右击-选择导出数据,之后选择路径,如下图:

然后编辑导出的sql文件,使它能正确的在mysql中执行,to_date替换成STR_TO_DATE

insert into FUKUANDATE (SWEBID, DSTART)

values (403965, to_date('15-09-2015', 'dd-mm-yyyy'));

改成:

INSERT INTO FUKUANDATE (SWEBID, DSTART) VALUES (403965,STR_TO_DATE('15-09-2015','%d-%m-%Y'));

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-03/141914.htm

linux
相关资讯       Incorrect datetime value 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款