警告(代码1411):日期时间值不正确: [英] Warning (Code 1411): Incorrect datetime value:
问题描述
我试图使用mysql INFILE命令将数据添加到表中,但是出现以下错误。
警告(代码1411):datetime值不正确:''2015-05-15 15:57:46''for function str_to_date
警告(代码1048):列transaction_time不能为空
mysql -uroot test --show-warnings -vveLOAD DATA INFILE
'/ Users / xyz / log2.tsv'into table logs_master fields以'\''结尾的
由'\'终止的行(@transaction_time,col2,
col3,col4,...) )SET updated_at = NOW(),transaction_time =
STR_TO_DATE(@transaction_time,'%Y-%m-%d%H:%i:%s')
CREATE TABLE`test_action_logs_master`(
`transaction_time` datetime NOT NULL DEFAULT'0000-00-00 00:00:00' ,
`col1` varchar(100)DEFAULT NULL,
`col2` varchar(700)DEFAULT NULL,
`col3` varchar(700)DEF AULT NULL,
`col4` varchar(700)DEFAULT NULL,
`col5` varchar(200)DEFAULT NULL,
`col6` varchar(700)DEFAULT NULL,
` col7` varchar(700)DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY`col3`(`col3`),
KEY`col4`(`col4`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8
mysql版本:服务器版本:5.6.25
机器:OS X(MAC)优胜美地
显示变量,如'sql_mode'
- > ;
+ --------------- + ------------------------ +
| Variable_name |价值|
+ --------------- + ------------------------ +
| sql_mode | NO_ENGINE_SUBSTITUTION |
+ --------------- + ------------------------ +
1行(0.00秒)
我已经通过查看其他线程进行了更改并查看链接
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
不知怎的,它不起作用并给出列出的错误。其余的字段来源正确,只是作为日期时间值的领先字段没有被提供。
您的日期字段以额外的单引号括起来,因此您需要相应地调整格式;这将使您得到正确的结果(请注意额外的引号):
SELECT STR_TO_DATE('2015-05-15 15: 57:46',%Y-%m-%d%H:%i:%s')
您当前的陈述如下,由于模式不匹配,您将获得 NULL
:
SELECT STR_TO_DATE('2015-05-15 15:57:46','%Y-%m-%d%H:%i:%s')
Hi I am trying to add data to a table using mysql INFILE Command but getting the following error.
Warning (Code 1411): Incorrect datetime value: ''2015-05-15 15:57:46'' for function str_to_date
Warning (Code 1048): Column 'transaction_time' cannot be null
mysql -uroot test --show-warnings -vve "LOAD DATA INFILE '/Users/xyz/log2.tsv' into table logs_master fields terminated by '\t' enclosed by '' lines terminated by '\n' (@transaction_time, col2, col3, col4,...) SET updated_at = NOW(), transaction_time = STR_TO_DATE(@transaction_time, '%Y-%m-%d %H:%i:%s')"
CREATE TABLE `test_action_logs_master` (
`transaction_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`col1` varchar(100) DEFAULT NULL,
`col2` varchar(700) DEFAULT NULL,
`col3` varchar(700) DEFAULT NULL,
`col4` varchar(700) DEFAULT NULL,
`col5` varchar(200) DEFAULT NULL,
`col6` varchar(700) DEFAULT NULL,
`col7` varchar(700) DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `col3` (`col3`),
KEY `col4` (`col4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql version: Server version: 5.6.25 Machine: OS X(MAC) Yosemite
show variables like 'sql_mode'
-> ;
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| sql_mode | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)
I have made the changes by looking at other threads on the issue and also looking at the link http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
Somehow, it doesn't work and gives the listed error. The rest of the fields are sourced properly, just that the leading field which is a date time value is not getting sourced.
Your date field is enclosed in an extra pair of single quotes, so you need to adjust your format accordingly; this will get you your correct result (note the extra quotes):
SELECT STR_TO_DATE("'2015-05-15 15:57:46'", "'%Y-%m-%d %H:%i:%s'")
Your current statement is the following, and since the pattern doesn't match you are getting NULL
:
SELECT STR_TO_DATE("'2015-05-15 15:57:46'", '%Y-%m-%d %H:%i:%s')
这篇关于警告(代码1411):日期时间值不正确:的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!