MYSQL错误TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE的解决方案

本文链接：https://blog.csdn.net/TRQ809135642/article/details/70050742

在我的MYSQL创建数据表时报如下错误：

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

意思是只能有一个带CURRENT_TIMESTAMP的timestamp列存在；

通过搜索资料发现是因为mysql版本问题，我的是5.5版本，而在5.6.5 之后mysql做了如下的变化：

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.

原先建表语句是：

DROP TABLE IF EXISTS `activity`;

CREATE TABLE `activity` (
`activity_id` int(11) NOT NULL AUTO_INCREMENT,
`activity_name` varchar(100) NOT NULL,
`activity_starttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`activity_endtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`type_limt` int(11) DEFAULT '1',
`time_limt` int(11) DEFAULT '0',
`activity_detail` varchar(1000) DEFAULT NULL,
`admin_id` int(11) NOT NULL,
`activity_creattime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`activity_id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

通过触发器替代之后为：

CREATE TABLE `activity` (
`activity_id` INT(11) NOT NULL AUTO_INCREMENT,
`activity_name` VARCHAR(100) NOT NULL,
`activity_starttime` DATETIME NOT NULL,
`activity_endtime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`type_limt` INT(11) DEFAULT '1',
`time_limt` INT(11) DEFAULT '0',
`activity_detail` VARCHAR(1000) DEFAULT NULL,
`admin_id` INT(11) NOT NULL,
`activity_creattime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`activity_id`)
) ENGINE=MYISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
DROP TRIGGER IF EXISTS `update_activity_trigger`;
DELIMITER //
CREATE TRIGGER `update_activity_trigger` BEFORE UPDATE ON `activity`
FOR EACH ROW SET NEW.`activity_starttime` = NOW()
//
DELIMITER ;