mysql数据库配置触发器实现数据更新
目的:将表一里面最新插入的数据更新到表二id为1的记录上
数据记录表一 表结构
-- ----------------------------
-- Table structure for pi_science
-- ----------------------------
DROP TABLE IF EXISTS `pi_science`;
CREATE TABLE `pi_science` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`temperature` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '温度',
`humidity` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '湿度',
`pressure` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '气压',
`date` datetime DEFAULT NULL COMMENT '上报时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1480 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TRIGGER IF EXISTS `test`;
DELIMITER ;;
CREATE TRIGGER `test` AFTER INSERT ON `pi_science` FOR EACH ROW begin
update pi_science_up set temperature=new.temperature,humidity=new.humidity,pressure=new.pressure,date=new.date where id=1;
end
;;
DELIMITER ;
数据显示表二 表结构
-- ----------------------------
-- Table structure for pi_science_up
-- ----------------------------
DROP TABLE IF EXISTS `pi_science_up`;
CREATE TABLE `pi_science_up` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`temperature` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`humidity` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`pressure` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ----------------------------
-- Records of pi_science_up
-- ----------------------------
INSERT INTO `pi_science_up` VALUES ('1', '32.30°C', '15.35%RH', '410.76hPa', '2020-10-22 00:18:02');
打开我们的navicat工具,进入到表一进行表设计,在表设计里面可以看到新增触发器,如下图:
![](https://cdn.hlhasd.com/wp-content/uploads/2020/10/QQ截图20201022003705-1024x635.png)
如上图进行实在,在下面定义填写如何进行触发
begin
update pi_science_up set temperature=new.temperature,humidity=new.humidity,pressure=new.pressure,date=new.date where id=1;
end
特别注意里面的new.
这个是代表触发的数据,必须填写,不然数据不会得到更新,定义完成后保存,等待表一里面有新的数据插入,插入的数据会更新到表二id为1的记录上,教程结束。
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/cy.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/hanx.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/huaix.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/tx.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/se.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/wx.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/zk.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/shui.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/kuk.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/lh.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/gz.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/ku.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/kel.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/yiw.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/yun.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/jy.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/dy.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/gg.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/fn.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/fendou.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/shuai.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/kl.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/pj.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/fan.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/lw.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/qiang.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/ruo.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/ws.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/ok.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/gy.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/qt.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/cj.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/aini.gif)
![](https://cdn.hlhasd.com/wp-content/themes/tow/images/smilies/bu.gif)