时间戳

首页 > 解决方案 > MySQL INSERT INTO SELECT 不匹配记录由函数 str_to_date 触发

mysql - MySQL INSERT INTO SELECT 不匹配记录由函数 str_to_date 触发

问题描述

我有一个奇怪的 MySQL INSERT SELECT 行为，我需要将 dt_int 从 TABLE2 转换为 TABLE1 中的日期时间 dt。

表结构为

TABLE1
PK INT(11) -- auto increment
dt datetime

TABLE2
PK INT(11)  -- auto increment
dt_int INT(11)

我有这样的插入选择查询

INSERT INTO TABLE1(dt)
(
  SELECT str_to_date(dt_int, '%Y%m%d')
  FROM TABLE2 
  WHERE str_to_date(dt_int, '%Y%m%d') IS NOT NULL
)

如果表中的所有日期都有效，则它工作正常。但是，如果表包含类似这样的数据

TABLE2
PK   |   dt_int
1        20201209
2        20202020

它会遇到错误代码 1411：函数 str_to_date 的日期时间值“20202020”不正确。

内部 select 语句只返回有效日期，但 insert 语句仍会尝试为过滤的日期转换日期。为什么会这样？有什么我可以做的吗？

[已编辑] MySQL 版本为 5.7，引擎为 InnoDB。目前托管在 Windows 环境中。

标签： mysqlinsert-selectstr-to-date

解决方案

INSERT INTO table1
SELECT PK, CONCAT(dt_int DIV 10000, '-1-1') 
           + INTERVAL dt_int MOD 10000 DIV 100 - 1 MONTH 
           + INTERVAL dt_int MOD 100 - 1 DAY
FROM table2
WHERE 0 + DATE_FORMAT(CONCAT(dt_int DIV 10000, '-1-1') 
                      + INTERVAL dt_int MOD 10000 DIV 100 - 1 MONTH  
                      + INTERVAL dt_int MOD 100 - 1 DAY, '%Y%m%d') = dt_int;

小提琴

推荐阅读