在mysql 5.6以下版本的建表语句中,出现多个current timestamp会报错,如下
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`)
)
错误码:1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
处理方式为:
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 COMMENT '更新时间',
PRIMARY KEY (`category_id`)
)
CREATE TRIGGER `update_product_category_trigger` BEFORE UPDATE ON `product_category`
FOR EACH ROW SET NEW.`update_time` = NOW()
通过创建触发器,更新操作时触发器自动执行变更为当前时间。
此外,在数据库的连接参数中,需要再添加一个额外的参数zeroDateTimeBehavior=convertToNull。
url=jdbc:mysql://localhost:3306/databaseName?characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull