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)