我有这样一个sql脚本:
USE table_01;
DROP TABLE IF EXISTS `test_01`;
CREATE TABLE `test_01` (
`id` varchar(50) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`DATE_01` datetime NOT NULL,
`DATE_02` datetime NOT NULL,
`VALUE` tinyint(1) NOT NULL,
`DATE_03` datetime DEFAULT NULL,
PRIMARY KEY (`id`,`DATE_01`,`DATE_02`),
KEY `XXX_idx` (`id`,`DATE_01`,`DATE_02`,`VALUE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE 'XXXXXX/sample.csv'
INTO TABLE test_01
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (id, @DATE_01, @DATE_02, VALUE, @DATE_03)
SET `DATE_01` = STR_TO_DATE(@DATE_01, '%d/%m/%Y'),
`DATE_02` = STR_TO_DATE(@DATE_02, '%d/%m/%Y'),
`DATE_03` = STR_TO_DATE(@DATE_03, '%d/%m/%Y')
csv文件如下所示:
如果我检查三个日期列的格式，它们是“dd/mm/yyy hh:mm”
我的文件的总行数是2000+，但我只能得到1100+行，其他的显示错误
1107 row(s) affected, 64 warning(s): 1292 Truncated incorrect date value: '01/12/2010 00:00' 1292 Truncated incorrect date value: '03/02/2011 00:00' 1292 ...........
Records: 2365 Deleted: 0 Skipped: 1258 Warnings: 7095
为什么时间都转换为“00:00:00”，我该如何解决这个问题？ 我是SQL新手，我已经为这个问题纠结了一整天了，请帮帮我。。。。
您给str_to_date()提供的格式说明符只包括日期部分，而不包括时间部分。 您需要对其进行扩展，如下所示:
LOAD DATA LOCAL INFILE 'XXXXXX/sample.csv'
INTO TABLE test_01
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (id, @DATE_01, @DATE_02, VALUE, @DATE_03)
SET
`DATE_01` = STR_TO_DATE(@DATE_01, '%d/%m/%Y %H:%i'), -- here
`DATE_02` = STR_TO_DATE(@DATE_02, '%d/%m/%Y %H:%i'), -- same here
`DATE_03` = STR_TO_DATE(@DATE_03, '%d/%m/%Y %H:%i') -- and here
