MySQL5.5后-datetime类型的列设置默认值为CURRENT_TIMESTAMP

• 背景
• 解决方案
• 建立数据表时设置默认值
• 使用ALTER TABLE 修改默认值
• navicat图形界面设置
• 总结

参考
背景

根据《阿里巴巴Java开发手册（正式版）》,数据表中必备三字段:id,gmt_create,gmt_modified.gmt_create和gmt_modified均为datetime类型.但是datetime类型精确到s,对于有些需要精确到ms的需求应为datetime(3)类型.为了便于开发,会将其默认值设置为当前时间,但如何将datetime(3)类型的列设置为当前时间呢?

解决方案

建立数据表时设置默认值

建表语句如下:

CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
  `gmt_modified` datetime(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
1
2
3
4
5
6
7
使用ALTER TABLE 修改默认值

有两种方式修改列类型和默认值,如下:

ALTER TABLE user MODIFY [COLUMN] gmt_create datetime(3) DEFAULT CURRENT_TIMESTAMP(3);
ALTER TABLE user CHANGE gmt_create gmt_create datetime(3) DEFAULT CURRENT_TIMESTAMP(3);
ALTER TABLE user CHANGE gmt_modified gmt_modified datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3);
MODIFY关键字只能修改列类型和默认值,而CHANGE可以修改列名,所以CHANGE后跟两个gmt_create,第一个gmt_create是旧列名,第二个gmt_create是新列名.
若列类型为datetime(3),则默认值也必须是CURRENT_TIMESTAMP(3).若设置默认值为CURRENT_TIMESTAMP(),则报错 1067 - Invalid default value for 'gmt_create'
为gmt_modified设置UPDATE时更新列值为当前时间需要增加语句ON UPDATE CURRENT_TIMESTAMP(3)
navicat图形界面设置

gmt_create设置: 

gmt_modified设置: 

总结

其实之前也遇到过这个问题,也记录到文档中,但是没有写到博客中,导致今天又浪费了一个小时的时间解决这个问题,特此记录. 
1. datetime(3)类型的默认值为CURRENT_TIMESTAMP(3)而不是CURRENT_TIMESTAMP().切记!!! 
2. mysql5.5及之前版本只支持timestamp类型设置默认值为CURRENT_TIMESTAMP,不支持datetime类型默认值设置为CURRENT_TIMESTAMP
--------------------- 
作者：pfjia 
来源：CSDN 
原文：https://blog.csdn.net/jpf254/article/details/81843454