MySQL 时间戳 TIMESTAMP 使用

最新推荐文章于 2023-06-14 13:27:57 发布

northsky365

最新推荐文章于 2023-06-14 13:27:57 发布

阅读量285

点赞数

CC 4.0 BY-SA版权

分类专栏： MySQL

本文链接：https://blog.youkuaiyun.com/northsky365/article/details/119413256

MySQL 专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍了MySQL 5.7中TIMESTAMP类型的时间戳字段使用，包括默认值、自动插入与更新策略。通过实例演示了如何设置默认当前时间、仅插入时更新和同时插入及更新时更新时间戳。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL 5.7
MySQL 时间戳 TIMESTAMP 使用

参考：mysql各版本timestamp默认值

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(16) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(32) NOT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,								#插入时的时间
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,				#更新时的时间
  `update_time1` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,	#插入/更新时的时间
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

table.info

mysql> insert into user (username, email, password) values ('aaa', '123@126.com', '123'),('bbb', '234@126.com', '456'),('bbb', '345@126.com', '789');            
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+----------+-------------+----------+---------------------+-------------+---------------------+
| id | username | email       | password | create_time         | update_time | update_time1        |
+----+----------+-------------+----------+---------------------+-------------+---------------------+
|  4 | aaa      | 123@126.com | 123      | 2021-08-05 14:07:28 | NULL        | 2021-08-05 14:07:28 |
|  5 | bbb      | 234@126.com | 456      | 2021-08-05 14:07:28 | NULL        | 2021-08-05 14:07:28 |
|  6 | bbb      | 345@126.com | 789      | 2021-08-05 14:07:28 | NULL        | 2021-08-05 14:07:28 |
+----+----------+-------------+----------+---------------------+-------------+---------------------+
3 rows in set (0.00 sec)

mysql> update user set username = 'aab' where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user where id = 4;
+----+----------+-------------+----------+---------------------+---------------------+---------------------
| id | username | email       | password | create_time         | update_time         | update_time1        
+----+----------+-------------+----------+---------------------+---------------------+---------------------
|  4 | aab      | 123@126.com | 123      | 2021-08-05 14:07:28 | 2021-08-05 14:10:23 | 2021-08-05 14:10:23 
+----+----------+-------------+----------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>