mysql中实现触发器修改数据就添加一条记录到另一张表

创建一张表,填入数据:
CREATE DATABASE `test`; USE `test`; DROP TABLE IF EXISTS `tt`; CREATE TABLE `tt` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `num` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; insert into `tt`(`id`,`num`) values (1,4),(2,2),(3,3),(4,1); 创建一张日志表: CREATE TABLE `log` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `effect` int(20) NOT NULL comment '受影响的行id', `created_time` datetime NOT NULL comment '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 创建触发器:
DELIMITER $$ USE `test`$$ DROP TRIGGER `trigger_tt`$$ CREATE TRIGGER `trigger_tt` AFTER UPDATE ON `tt` FOR EACH ROW BEGIN IF((old.num != new.num) OR (old.num IS NULL && new.num IS NOT NULL)) THEN INSERT `test`.`log` (`effect`,`created_time`) VALUES (new.id,NOW()); END IF; END; $$ DELIMITER ;
修改数据num之后向log表中写入一条数据 执行下面的语句进行测试:
UPDATE tt SET `num`=2 WHERE id =1; UPDATE tt SET `num`=4 WHERE id =1; UPDATE tt SET `num`=1 WHERE id =4; 查看log表数据 id effect created_time 1 1 2020-05-09 13:46:19 2 1 2020-05-09 13:46:50 3 4 2020-05-09 13:47:22