MySQL数据库使用trigger更新中间表

一些count类的查询，在很频繁时，一般不直接使用原表，二是使用trigger更新到中间表上，直接使用中间表获得查询结果。 以下是我写的一个例子。只有insert与update操作。

测试表与trigger创建，mid_test中sumflag的值为base_table中isflag大于0的值 MySQL> create table base_test(id int auto_increment primary key,     -> isflag tinyint(1),     -> ownflag varchar(5))engine=innodb; Query OK, 0 rows affected (0.45 sec) mysql> create table mid_test(ownflag varchar(5) primary key,     -> sumflag int)engine=innodb; Query OK, 0 rows affected (0.11 sec) 表创建 DELIMITER $ create trigger tri_base_insert after insert on base_test for each row begin if new.isflag > 0 then declare c int; set c = (select sumflag from mid_test where ownflag = new.ownflag); update mid_test set sumflag = c + 1 where ownflag = new.ownflag; end if; end$ DELIMITER ; Insert trigger 创建 DELIMITER $ create trigger tri_base_update after update on base_test for each row begin declare c int; declare d int; if new.isflag <> old.isflag then if new.isflag > 0 then set d=1; else set d=-1; end if; set c = (select sumflag from mid_test where ownflag = new.ownflag); update mid_test set sumflag = c + d where ownflag = new.ownflag; end if; end$ DELIMITER ; Update trigger 创建 INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('1', '0'); INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('2', '0'); INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('3', '0'); INSERT INTO `wzy`.`mid_test` (`ownflag`, `sumflag`) VALUES ('4', '0'); Mid_test初始化 测试： mysql> insert into base_test values(null,1,1); Query OK, 1 row affected (0.00 sec) mysql> select * from mid_test where ownflag='1'; +---------+---------+ | ownflag | sumflag | +---------+---------+ | 1      |      1 | +---------+---------+ 1 row in set (0.00 sec) mysql> insert into base_test values(null,1,1); Query OK, 1 row affected (0.12 sec) mysql> select * from mid_test where ownflag='1'; +---------+---------+ | ownflag | sumflag | +---------+---------+ | 1      |      2 | +---------+---------+ 1 row in set (0.00 sec) mysql> insert into base_test values(null,1,1); Query OK, 1 row affected (12.34 sec) mysql> select * from mid_test where ownflag='1'; +---------+---------+ | ownflag | sumflag | +---------+---------+ | 1      |      3 | +---------+---------+ 1 row in set (0.00 sec) mysql> update base_test set isflag=0 where ownflag='1'; Query OK, 0 rows affected (0.00 sec) Rows matched: 3  Changed: 0  Warnings: 0 mysql> select * from mid_test where ownflag='1'; +---------+---------+ | ownflag | sumflag | +---------+---------+ | 1      |      0 | +---------+---------+ 1 row in set (0.00 sec) mysql> update base_test set isflag=1 where id=3; Query OK, 1 row affected (0.04 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> select * from mid_test where ownflag='1'; +---------+---------+ | ownflag | sumflag | +---------+---------+ | 1      |      1 | +---------+---------+ 1 row in set (0.05 sec)