关于Mysql datetime类型存储范围测试

港控/mmm° 2021-12-03 19:07 248阅读 0赞

1.  创建一个datetime表
    
        > create table date_time(time datetime); > desc date_time; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | time | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+
2.  输入"10000-10-10 00:00:00"进行测试
    
        > insert into date_time values("10000-10-10 00:00:00"); ERROR 1292 (22007): Incorrect datetime value: '10000-10-10 00:00:00' for column 'time' at row 1 # 报错
3.  输入”23-01-02 12:23:56"测试
    
        > insert into date_time values("23-01-02 12:23:56"); > insert into date_time values("79-01-02 12:23:56"); > select * from date_time; +---------------------+ | time | +---------------------+ | 2023-01-02 12:23:56 | | 1979-01-02 12:23:56 | +---------------------+
    
    会和date一样，'00'-'69'会转换为2000-2069，'70'-'99'会转换为1970-1999
4.  测试年份输入三位或者一位
    
        > insert into date_time values("179-01-02 12:23:56"); > insert into date_time values("9-01-02 12:23:56"); > select * from date_time; +---------------------+ | time | +---------------------+ | 2023-01-02 12:23:56 | | 1979-01-02 12:23:56 | | 0179-01-02 12:23:56 | | 0009-01-02 12:23:56 | +---------------------+
    
    可以存储进去，但会自动在前面补足0，使年份变为4位
5.  输入年份格式为”00YY“
    
        > insert into date_time values("0079-01-02 12:23:56"); > select * from date_time; +---------------------+ | time | +---------------------+ | 2023-01-02 12:23:56 | | 1979-01-02 12:23:56 | | 0179-01-02 12:23:56 | | 0009-01-02 12:23:56 | | 0079-01-02 12:23:56 | # 可以存储 +---------------------+
6.  最后测试极限时间
    
        > insert into date_time values("0000-00-00 00:00:00"); > insert into date_time values("9999-12-31 23:59:59"); > select * from date_time; +---------------------+ | time | +---------------------+ | 2023-01-02 12:23:56 | | 1979-01-02 12:23:56 | | 0179-01-02 12:23:56 | | 0009-01-02 12:23:56 | | 0079-01-02 12:23:56 | | 0000-00-00 00:00:00 | | 9999-12-31 23:59:59 | +---------------------+

总结：

1.  datetime类型范围为”0000-00-00 00:00:00“~”9999-12-31 23:59:59“；
2.  要存储年份为两位数的需要在前面补足”00“，不然会按照date类型进行转换（'00'-'69'会转换为2000-2069，'70'-'99'会转换为1970-1999）；

转载于:https://www.cnblogs.com/Hades123/p/11172841.html