MySql 时间戳错误 : Data truncation: Incorrect datetime value

Question

我有一个这样的表:

    CREATE TABLE event (
                       id              BIGINT          NOT NULL AUTO_INCREMENT PRIMARY KEY,

                       name            VARCHAR(80)     NOT NULL,                       
                       start_datetime  TIMESTAMP       NOT NULL DEFAULT '1970-01-01 00:00:01',
                       end_datetime    TIMESTAMP       NOT NULL DEFAULT '1970-01-01 00:00:01',

                       description     TEXT,                      

                       created         TIMESTAMP(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
                       modified        TIMESTAMP(3)    NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)

);

插入这条新记录时没有错误:

insert into event (name, start_datetime, end_datetime)
value
('myName', '2020-01-05 18:00:00', '2020-01-07 23:59:00')
;

但是这条记录抛出错误:

insert into event (name, start_datetime, end_datetime)
value
('myName', '2045-01-05 18:00:00', '2045-01-07 23:59:00')
;

错误是

Data truncation: Incorrect datetime value: '2045-01-05 18:00:00' for column 'start_datetime' at row 1

有人可以帮忙吗？非常感谢!

更新:在 MySql 文档中找到这个:TIMESTAMP 数据类型用于包含日期和时间部分的值。 TIMESTAMP 的范围是“1970-01-01 00:00:01”UTC 到“2038-01-19 03:14:07”UTC。

所以我猜 2045 太远了…………

Answer 1

TIMESTAMP 仅对

有效

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

对于“更大”的日期切换到 DATETIME

the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

有关更多信息，请参阅 manual