MySQL-timestamp

在数据库数据发生修改的时候,可以设置timestampdatetime自动初始化和更新到当前时间,以时间戳timestamp为例,进行测试。

版本和时区

  • MySQL版本:8.0.13
  • 时区:serverTimezone=Asia/Shanghai

三种数据类型

MySQL主要有三种表示日期时间的数据类型,datedatetimetimestamp

  • date精确到天,格式YYYY-MM-DD,datetime和timestamp精确到秒,格式YYYY-MM-DD HH:MM:SS
  • timestamp储存占用4个字节,datetime储存占用8个字节
  • timestamp可表示范围:1970-01-01 00:00:00 ~ 2038-01-09 03:14:07datetime可表示范围1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

总结,timestamp更轻量,索引速度更快,可以满足大部分场景,推荐使用

建表

1
2
3
4
5
6
7
8
create table `product_category` (
`category_id` int not null auto_increment,
`category_name` varchar(64) not null comment '类目名字',
`category_type` int not null comment '类目编号',
`create_time` timestamp not null default current_timestamp comment '创建时间',
`update_time` timestamp not null default current_timestamp on update current_timestamp comment '修改时间',
primary key (`category_id`)
);
  • create_timeupdate_time在新增数据时初始化为当前时间
  • update_time在修改数据时更新为当前时间

项目使用

新建以下实体类

1
2
3
4
5
6
7
8
9
10
11
@Entity
@Data
public class ProductCategory {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer categoryId;
private String categoryName;
private Integer categoryType;
private Date createTime;
private Date updateTime;
}

ProductCategoryRepository继承JpaRepository,@Service注入CategoryServiceImpl
测试类

1
2
3
4
5
ProductCategory productCategory = new ProductCategory();
productCategory.setCategoryName("classical");
productCategory.setCategoryType(10);
ProductCategory result = categoryService.save(productCategory);
Assert.assertNotNull(result);

会抛出异常

1
ERROR 1048 (23000): Column 'create_time' cannot be null

异常解决

以上代码执行sql相当于

1
insert into product_category (category_name,category_type,create_time,updateTime) VALUES ('classical',10,null,null)

在数据库中执行sql,产生相同的结果ERROR 1048 (23000)

第一种解决方法,实体类删除字段

1
2
private Date createTime;
private Date updateTime;

对应sql会更改为

1
insert into product_category (category_name,category_type) VALUES ('classical',10)

此时可以正常插入,但使用更改后的实体类作为模板,便无法查询这两个时间字段,不推荐。

第二种解决方法,执行以下语句

1
set global explicit_defaults_for_timestamp='OFF';

解释:MySQL中的系统变量explicit_defaults_for_timestamp,默认值是ON,即在新增数据(insert into)和修改数据(update)时,timestamp类型的字段不可以显示设置为null,修改值为OFF,问题解决,但不是标准sql,不推荐。

第三种解决方法,实体类添加注解(推荐)

1
2
3
4
5
@DynamicInsert
@DynamicUpdate
public class ProductCategory {
//省略...
}
  • 添加注解@DynamicInsert表示在insert时动态生成insert语句
  • 添加注解@DynamicUpdate表示在update时动态生成update语句

参考资料