mysql - 将包含时间数据的 CSV 导入 MySQL 时，为什么会收到 '1292 Truncated' 警告？

Question

我正在将 csv 文件导入 MySQL。部分数据是时间字段。我正在使用 TIME_FORMAT() 来设置所需的格式。但是，我没有得到我需要的东西。这是我用来格式化时间字段的代码。

CREATE TABLE `COURSE_SECTION` (
    `C_SEC_ID` INTEGER PRIMARY KEY AUTO_INCREMENT,
    `COURSE_ID` INTEGER NOT NULL,
    `TERM_ID` INTEGER NOT NULL,
    `SEC_NUM` INTEGER NOT NULL,
    `F_ID` INTEGER NOT NULL,
    `MTG_DAYS` VARCHAR(7) NOT NULL,
    `START_TIME` TIME NOT NULL,
    `END_TIME` TIME NOT NULL,
    `LOC_ID` INTEGER,
    `MAX_ENRL` INTEGER,
FOREIGN KEY (`COURSE_ID`)
    REFERENCES course (`COURSE_ID`)
    ON UPDATE CASCADE 
    ON DELETE NO ACTION,
FOREIGN KEY (`TERM_ID`)
    REFERENCES term (`TERM_ID`)
    ON UPDATE CASCADE 
    ON DELETE NO ACTION,
FOREIGN KEY (`F_ID`)
    REFERENCES faculty (`F_ID`)
    ON UPDATE CASCADE 
    ON DELETE NO ACTION,
FOREIGN KEY (`LOC_ID`)
    REFERENCES location (`LOC_ID`)
    ON UPDATE CASCADE 
    ON DELETE NO ACTION
);

csv 输入:

C_SEC_ID, COURSE_ID, TERM_ID, SEC_NUM, F_ID,    MTG_DAYS, START_TIME, END_TIME, LOC_ID, MAX_ENRL
Number,   Number,    Number,  Number,  Number,  String,   Date/Time,  String,   Number, Number
1,        1,         4,       1,       2,       MWF,      10:00 AM,   10:50 AM, 1,      140
2,1,4,2,3,TR,9:30 AM,10:45 AM,7,35
3,1,4,3,3,MWF,8:00 AM,8:50 AM,2,35
4,2,4,1,4,TR,11:00 AM,12:15 AM,6,35
5,2,5,2,4,TR,2:00 PM,3:15 PM,6,35
6,3,5,1,1,MWF,9:00 AM,9:50 AM,5,30
7,3,5,2,1,MWF,10:00 AM,10:50 AM,5,30
8,4,5,1,5,TR,8:00 AM,9:15 AM,3,35
9,5,5,1,2,MWF,2:00 PM,2:50 PM,5,35
10,5,5,2,2,MWF,3:00 PM,3:50 PM,5,35
11,1,6,1,1,MTWRF,8:00 AM,9:30 AM,1,50
12,2,6,1,2,MTWRF,8:00 AM,9:30 AM,6,35
13,3,6,1,3,MTWRF,8:00 AM,9:30 AM,5,35

导入 csv 的代码:

LOAD DATA LOCAL INFILE 'path to the file/Course_Section.csv' 
INTO TABLE COURSE_SECTION
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 2 ROWS
(C_SEC_ID,COURSE_ID,TERM_ID,SEC_NUM,F_ID,MTG_DAYS,@START_TIME,@END_TIME,LOC_ID,MAX_ENRL)
SET START_TIME = TIME_FORMAT(@START_TIME, '%h:%i %p'),
END_TIME = TIME_FORMAT(@END_TIME, '%h:%i %p');

表格中的结果是 03:15:00 而不是 03:15 PM

我还尝试了以下代码，但没有成功:

SET START_TIME = TIME_FORMAT(CAST(@START_TIME AS CHAR CHARACTER SET utf8), '%h:%i %p'),
END_TIME = TIME_FORMAT(CAST(@END_TIME AS CHAR CHARACTER SET utf8), '%h:%i %p');

结果与之前相同。

通过以下代码，我得到了正确的值，但有警告:

SET START_TIME = TIME_FORMAT(
    CAST(
        CONCAT(CASE WHEN RIGHT(@START_TIME,2) = 'PM' 
            THEN
                HOUR(@START_TIME) + 12 
            ELSE 
                HOUR(@START_TIME) END, ':',
                MINUTE(@START_TIME)) AS CHAR CHARACTER SET utf8), '%T'),

警告如下:

1292 Truncated incorrect time value: '10:00 AM'

该警告的确切含义是什么？我该如何消除它？

我的代码中缺少什么？

Answer 1

请尝试这个

TIME_FORMAT(@END_TIME, '%r');

对于 @START_TIME 也是如此，不使用任何自定义函数。也检查这个 fiddle :

http://sqlfiddle.com/#!9/dcb16/59828