mysql5.7 timestamp错误：there can be only oneTIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE
#1293 - Incorrect table definition; there can be only oneTIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
1 CREATE TABLE `t_customer` ( 2 `q_id` INT(11) NOT NULL AUTO_INCREMENT, 3 4 `c_ctime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , 5 `c_uptime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
改成
1 `c_ctime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 2 `c_uptime` DATETIME NOT NULL,
然后加上触发器
CREATE TRIGGER `t_customer_uptime` BEFORE UPDATE ON `t_customer` FOR EACHROW SET NEW.`c_uptime` = NOW()
1 CREATE TABLE `tb1` ( 2 `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 3 `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 4 `lastUpdated` DATETIME NOT NULL, 5 PRIMARY KEY (`id`) 6 ) ENGINE=InnoDB; 7 DROP TRIGGER IF EXISTS `insert_example_trigger`; 8 DROP TRIGGER IF EXISTS `update_example_trigger`; 9 DELIMITER // 10 CREATE TRIGGER `insert_example_trigger` BEFORE INSERT ON `tb1` 11 FOR EACH ROW SET NEW.`lastUpdated` = NOW() 12 // 13 CREATE TRIGGER `update_example_trigger` BEFORE UPDATE ON `tb1` 14 FOR EACH ROW SET NEW.`lastUpdated` = NOW() 15 // 16 DELIMITER ;
