mysql中日期时间戳timestamp使用小结

青旅半醒 2023-07-12 05:12 6阅读 0赞

timestamp时间戳类型在mysql数据库中比较常见，但是我们很容易忽视它的一些特征，这个我在面试中吃过亏，这里对他的一些用法和特点补充一下。也让自己加深印象。一般来说，我们使用日期，一般采用date，或者datetime，仅仅使用时间的话使用time。

下面大致说明一下timestamp与datetime的区别：

1、他们都可以表示日期时间，默认格式都是"YYYY-MM-DD HH:MM:SS"，都可以通过now()或者'20200307225430'或者'2020-03-07 22:54:30'等值来设置值。

2、他们表示的时间范围不同：timestamp表示的是世界标准时间1970-01-01 00:00:01 - 2038-01-19 03:14:07，对于我们中国北京时间（东八区）来说，需要在小时上+8，也就是1970-01-01 08:00:01 - 2038-01-19 11:14:07。而datetime时间范围是1000-01-01 00:00:00 - 9999-12-31 23:59:59。

3、默认情况下，timestamp有默认值current\_timestamp，也就是当前时间，然后每次修改的时候，会自动刷新。而datetime除非手动修改值，否则不会自动刷新。

对于设置值，我们通过如下的sql执行结果来看看：

mysql> update users set name='aaaa',create_date=now(),modify_date=now();
    Query OK, 3 rows affected (0.05 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
    mysql> select * from users;
    +----+------+---------------------+---------------------+
    | id | name | create_date         | modify_date         |
    +----+------+---------------------+---------------------+
    |  1 | aaaa | 2020-03-07 22:56:53 | 2020-03-07 22:56:53 |
    |  2 | aaaa | 2020-03-07 22:56:53 | 2020-03-07 22:56:53 |
    |  3 | aaaa | 2020-03-07 22:56:53 | 2020-03-07 22:56:53 |
    +----+------+---------------------+---------------------+
    3 rows in set (0.00 sec)
    
    mysql> update users set name='aaaa',create_date='20200307225730',modify_date=20200307225700;
    Query OK, 3 rows affected (0.05 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
    mysql> update users set name='aaaa',create_date='2020-03-07 22:57:30',modify_date='2020-03-07 22:57:00';
    Query OK, 0 rows affected (0.05 sec)
    Rows matched: 3  Changed: 0  Warnings: 0
    
    mysql> select * from users;
    +----+------+---------------------+---------------------+
    | id | name | create_date         | modify_date         |
    +----+------+---------------------+---------------------+
    |  1 | aaaa | 2020-03-07 22:57:30 | 2020-03-07 22:57:00 |
    |  2 | aaaa | 2020-03-07 22:57:30 | 2020-03-07 22:57:00 |
    |  3 | aaaa | 2020-03-07 22:57:30 | 2020-03-07 22:57:00 |
    +----+------+---------------------+---------------------+
    3 rows in set (0.00 sec)

对于时间timestamp的时间范围，我们可以通过如下的sql语句来感受一下：

对于最小时间：

mysql> update users set modifydate = '19700101080000' where id = 7;
    1292 - Incorrect datetime value: '19700101080000' for column 'modifydate' at row 1
    mysql> update users set modifydate = '19700101080001' where id = 7;
    Query OK, 0 rows affected

对于最大时间：

mysql> update users set modifydate = '20380119111408' where id = 6;
    1292 - Incorrect datetime value: '20380119111408' for column 'modifydate' at row 1
    mysql> update users set modifydate = '20380119111407' where id = 6;
    Query OK, 0 rows affected

下面我们重点说一下timestamp默认情况以及修改记录时刷新的情况。

默认情况下，我们设置列类型为timestamp不用设置别的默认值，他的默认值就是current\_timestamp，以后修改记录的时候，会刷新，我们通过建表语句来感受一下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70][]

默认情况下，我们设置modify\_date的时候，就只是指定了timestamp，并没有后面的设置默认值，并在修改的时候指定当前时间。但是我们通过show create table users查看的 sql语句来看，他出现了是指默认值，以及修改记录的时候会刷新。

我们插入一条记录，然后修改记录的名称，看看modify\_date的变化：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1][]

我们可以在定义表的时候，指定默认值，不指定on update 时刷新，可以让timestamp可以和datetime一样，一经设置，后面只能通过手动修改才能发生改变，而不是自动刷新。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2][]

这时候，我们可以检验一下建表语句：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 3][]

这种情况之下，列定义后面没有on update current\_timestamp字样。就不会在修改记录时，自动刷新当前时间了。

还有一种情况是，我们在设置timestamp默认时间的时候，可以设置为0000-00-00 00:00:00，而不是当前时间，后面再修改记录的时候，自动刷新当前时间。虽然timestamp表示的时间范围是1970-01-01 00:00:01开始，但是默认值却可以设置为0000-00-00 00:00:00，也是很奇怪，不过确实可以。我们再修改表结构，插入记录并修改记录：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 4][]

我们不妨再次查看建表语句，发现这种设置timestamp的方式有什么特别的地方：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 5][]

我们看到，我们在建表的时候，仅仅指定on update current\_timestamp，并没有设置默认值，但是他最后生成的语句中自带了默认值'0000-00-00 00:00:00'。

总结一下就是，timestamp有以下特征：

1、时间范围：1970-01-01 00:00:01 ----- 2038-01-19 03:14:07

2、默认情况下，建表语句仅仅指定timestamp 等价于 timestamp default current\_timestamp on update current\_timestamp。

他有默认值，就是当前时间，后续修改记录的时候，不用手动指定值，可以自动刷新。

3、可以接受指定值，可以设置修改记录的时候，不刷新，这时候表现就和datetime一样。

4、timestamp on update current\_timestamp和timestamp not null default '0000-00-00 00:00:00' on update current\_timestamp等价。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200307221012615.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200307221530825.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200307222053300.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200307222252847.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200307223047358.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2020030722325184.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk=,size_16,color_FFFFFF,t_70