Mysql5.6以下版本不允许同时设置两个字段为CURRENT_TIMESTAMP

mysql5.6不存在此问题，mysql5.6重新定义了TIMESTAMP ：

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

 

在mysql5.5上执行如下sql:

CREATE TABLE `interface` (
  `id` varchar(50) NOT NULL COMMENT '主键',
  `url` varchar(100) NOT NULL COMMENT 'api链接',
  `method` varchar(30) NOT NULL COMMENT ' 请求方式',
  `param` text COMMENT '参数列表',
  `requestExam` text COMMENT '请求示例',
  `responseParam` text COMMENT '返回参数说明',
  `errorList` text COMMENT '接口错误码列表',
  `trueExam` text COMMENT '正确返回示例',
  `falseExam` text COMMENT '错误返回示例',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否可用;0不可用；1可用;-1 删除',
  `moduleId` varchar(50) NOT NULL COMMENT '所属模块ID',
  `interfaceName` varchar(100) NOT NULL COMMENT '接口名',
  `remark` text,
  `errors` text COMMENT '错误码、错误码信息',
  `updateBy` varchar(100) DEFAULT NULL,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

报错：

 there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

 

解决方案：

建表语句给其中一个timestamp字段指定默认值，另外使用mysql触发器，

建表语句：

CREATE TABLE `interface` (
  `id` VARCHAR(50) NOT NULL COMMENT '主键',
  `url` VARCHAR(100) NOT NULL COMMENT 'api链接',
  `method` VARCHAR(30) NOT NULL COMMENT ' 请求方式',
  `param` TEXT COMMENT '参数列表',
  `requestExam` TEXT COMMENT '请求示例',
  `responseParam` TEXT COMMENT '返回参数说明',
  `errorList` TEXT COMMENT '接口错误码列表',
  `trueExam` TEXT COMMENT '正确返回示例',
  `falseExam` TEXT COMMENT '错误返回示例',
  `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '是否可用;0不可用；1可用;-1 删除',
  `moduleId` VARCHAR(50) NOT NULL COMMENT '所属模块ID',
  `interfaceName` VARCHAR(100) NOT NULL COMMENT '接口名',
  `remark` TEXT,
  `errors` TEXT COMMENT '错误码、错误码信息',
  `updateBy` VARCHAR(100) DEFAULT NULL,
  `updateTime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  `createTime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

触发器语句：

CREATE TRIGGER `update_interface_update_time_trigger` BEFORE UPDATE ON `interface`
 FOR EACH ROW SET NEW.`updateTime` = NOW()