范围
类型 | 范围 |
---|---|
DATE | ‘1000-01-01’ to ‘9999-12-31’ |
DATETIME | ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ |
TIMESTAMP | ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’ |
默认值
先看一个属性 explicit_defaults_for_timestamp
Command-Line Format | --explicit-defaults-for-timestamp[={OFF|ON}] |
---|---|
Deprecated | Yes |
System Variable | explicit_defaults_for_timestamp |
Scope | Global, Session |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
In MySQL 5.6, the nonstandard behaviors just described are deprecated; expect them to be removed in a future MySQL release.
If explicit_defaults_for_timestamp is disabled at server startup, this warning appears in the error log:
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
mysql 5.6 explicit_defaults_for_timestamp
Command-Line Format | --explicit-defaults-for-timestamp[={OFF|ON}] |
---|---|
Deprecated | Yes |
System Variable | explicit_defaults_for_timestamp |
Scope | Global, Session |
Dynamic | Yes |
SET_VAR Hint Applies | No |
Type | Boolean |
Default Value | ON |
mysql 8.0 explicit_defaults_for_timestamp
mysql5.6.6默认是OFF, mysql8.x默认是ON, 当db sysvar_explicit_defaults_for_timestamp=OFF
两个类型的默认值是不一样的.timestamp为当前时间戳,datetime为null.
两个时间类型都支持current_timestamp获取当前时间,只是当sql不插入datetime类型字段时,默认值生效;
当sql指明插入datetime类型为null,默认值不生效,字段又不能null,就报错了。
类型修改
如果将timestamp 更新为datetime 类型,则需要检查后台是否需要修改。
如果后台有对时间类型赋值,则不需要修改,若insert 语句直接插入null, 则需要在insert 语句中移除该字段(使用db的默认值),或者在后台将该字段赋值当前时间
改backend的方式有以下方式:
- 后台配置实体属性为只读属性,使用db的默认值;
a.实体类声明属性只读
@Column的updatable和insertable来实现
b.修改实体类
@Generated(GenerationTime.INSERT) 使用数据库的默认值. - 后台通过注解自动生成插入时间;
a.hibernate 注解(类似spring data jpa注解)
@CreationTimestamp
@UpdateTimestamp