MySQL load 报错Incorrect datetime value

共计 2148 个字符，预计需要花费 6 分钟才能阅读完成。

有个需求要从 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