如果日期>日期时间字段溢出'9999-12-31 23:59:59.4' [英] Datetime field overflow if date > '9999-12-31 23:59:59.4'

问题描述

对于我的表 admin_tool_functionality

CREATE TABLE admin_tool_functionality (

    id BIGINT NOT NULL AUTO_INCREMENT,

    admin_tool_functionality_type_id BIGINT NOT NULL,

    CONSTRAINT fk__admin_tool_functionality__admin_tool_functionality_type
        FOREIGN KEY (admin_tool_functionality_type_id)
        REFERENCES admin_tool_functionality_type(id),

    PRIMARY KEY (id, admin_tool_functionality_type_id),

    price FLOAT NOT NULL,

    valid_from_day DATETIME NOT NULL, 

    valid_until_day DATETIME NOT NULL,

    CHECK(valid_from_day < valid_until_day)

);

此插入工作正常:

INSERT INTO admin_tool_functionality 
    (admin_tool_functionality_type_id, price, valid_from_day, valid_until_day)
VALUES
    (1, 13.37, '2016-01-01', '9999-12-31 23:59:59.4');

但是任何大于'9999-12-31 23:59:59.4'的值都将失败:

But any value lager than '9999-12-31 23:59:59.4' is failing:

INSERT INTO admin_tool_functionality 
    (admin_tool_functionality_type_id, price, valid_from_day, valid_until_day)
VALUES
    (1, 13.37, '2016-01-01', '9999-12-31 23:59:59.5');

给我:

Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Datetime function: datetime field overflow
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3964)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)

尽管有文档说

DATETIME 值的范围是'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'

为什么会出现此错误?

推荐答案

请参考分数秒 MySQL文档，并注意@Pieter关于MySQL版本的评论.

Refer to fractional seconds documentation for MySQL and note @Pieter comments regarding MySQL version.

在MySQL 5.6.4之前，微秒被丢弃并且不被存储，但是可以根据文档在以下情况下使用:

Before MySQL 5.6.4 the microseconds are discarded and not stored but can be used in the following situations as per the documentation:

在诸如文字值之类的上下文以及某些时态函数的参数或从中返回值的情况下，尾随小数部分是允许的.

A trailing fractional part is permissible in contexts such as literal values, and in the arguments to or return values from some temporal functions.

在MySQL 5.6.4上或之后，扩展了分数支持.

On or after MySQL 5.6.4, fractional seconds support was expanded.

除非您为 DATETIME 列定义小数秒( fsp )，否则它将默认为0，即无微秒.

Unless you define fractional seconds (fsp) for your DATETIME column, it will default to 0 i.e. no microseconds.

基于文档中的以下注释:

Based on this comment in the documentation:

将带有小数秒部分的TIME，DATE或TIMESTAMP值插入相同类型但小数位数较少的列中会导致四舍五入

Inserting a TIME, DATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding

基于上述注释和您失败的 INSERT 的假设和理论是.4甚至.499999(MySQL 5.6.4或更高版本支持的最多6个小数位)将舍入 down ，而.5或更大的值将使 up 向上取整.

An assumption and theory based on the above comment and your failing INSERT is that .4 or even .499999 (up to 6 fractional digits which MySQL 5.6.4 or newer supports) will round down, whereas .5 or greater will round up.

由于微秒被认为是分数，因此将使秒成为整数或整数的一部分.如果将59.5进行四舍五入，则将导致您的日期部分必须更改(向前移动1天)，因为时间部分的范围是 00:00:00 到 23:59:59 .在调整日期和时间的功能之外，可以将这种行为视为意外行为.

Since microseconds are considered fractional, that would make the seconds the integer or whole part of the number. If 59.5 were to round up, that would result in your date part having to change (move forward 1 day) because time part range is 00:00:00 to 23:59:59. This behaviour could be considered unexpected outside of a function that adjusts dates and times.

这篇关于如果日期>日期时间字段溢出'9999-12-31 23:59:59.4'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！