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

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