MySQL：自动生成创建时间、更新时间；自动更新更新时间


#MySQL 笔记

示例：

create table `user_info` (
    `id` bigint unsigned not null auto_increment comment '自增ID',
    `name` varchar(45) not null default '' comment '用户名',
    `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间',
    primary key (`id`)
) engine = InnoDB character set = utf8mb4;

mysql> insert into user_info (name) values('letianbiji.com');
mysql> select * from user_info;
+----+----------------+-------------------------+-------------------------+
| id | name           | created_at              | updated_at              |
+----+----------------+-------------------------+-------------------------+
|  1 | letianbiji.com | 2020-06-06 22:29:38.930 | 2020-06-06 22:29:38.930 |
+----+----------------+-------------------------+-------------------------+

可以看到，created_at 和 updated_at 自动生成了。

mysql> update user_info set name = 'letian' where id=1;
mysql> select * from user_info;
+----+--------+-------------------------+-------------------------+
| id | name   | created_at              | updated_at              |
+----+--------+-------------------------+-------------------------+
|  1 | letian | 2020-06-06 22:29:38.930 | 2020-06-06 22:31:26.345 |
+----+--------+-------------------------+-------------------------+

可以看到，updated_at 自动更新了。

这种自动更新的方式仅支持 CURRENT_TIMESTAMP ，不支持其他函数。例如：

-- 下面的 DDL 会报错
create table `user_info_2` (
    `id` bigint unsigned not null auto_increment comment '自增ID',
    `name` varchar(45) not null default '' comment '用户名',
    `created_at` int NOT NULL DEFAULT unix_timestamp() COMMENT '创建时间',
    `updated_at` bigint NOT NULL DEFAULT unix_timestamp() ON UPDATE unix_timestamp() COMMENT '修改时间',
    primary key (`id`)
) engine = InnoDB character set = utf8mb4;


( 本文完 )