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

创建一个datetime表

> create table date_time(time datetime);

> desc date_time;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| time | datetime | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

输入"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 # 报错

输入”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

测试年份输入三位或者一位

> 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位

输入年份格式为”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 | # 可以存储

+---------------------+

最后测试极限时间

> 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 |

+---------------------+