时间戳

首页 > 解决方案 > mySql Insert X Select Y 失败，而 Select Y 成功并且 Insert X "literal Y" 也成功

mysql - mySql Insert X Select Y 失败，而 Select Y 成功并且 Insert X "literal Y" 也成功

问题描述

mySql 5.6 -
OK: Select Extract(YEAR_MONTH FROM STR_TO_DATE('09/06/2018 Thu',...
OK: Insert into T ...literal result of above select
FAILS: Insert into T Select Extract(YEAR_MONTH FROM STR_TO_DATE .. .
with:"数据截断：截断不正确的日期值："

显然，第三条语句失败是因为日期字符串对于 Extract 来说太长了。但我很困惑为什么第一个语句也没有失败。请参阅：http ://sqlfiddle.com/#! 9/4a8b65 并取消注释第 22-25 行以查看我不明白的故障。

CREATE TABLE `Transactions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Period` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Insert into Transactions ( `Period`)
Values 201809);

--EXTRACT doesn't fail with a longer string in SELECT
SELECT  
EXTRACT(YEAR_MONTH FROM STR_TO_DATE('09/05/2018 Wed', '%m/%d/%Y'));

--Insert/EXTRACT works fine with shorter string
Insert into Transactions (  `Period`)
Values(EXTRACT(YEAR_MONTH FROM 
               STR_TO_DATE('09/06/2018', '%m/%d/%Y')
              )); 

--This fails, why?      
Insert into Transactions (  `Period`)
Values(EXTRACT(YEAR_MONTH FROM 
               STR_TO_DATE('09/07/2018 Fri', '%m/%d/%Y')
              ));

标签： mysqlsqldatesql-insert

解决方案

推荐阅读