mysql触发器实现oracle物化视图功能

oracle数据库支持物化视图，是根据基表实际存在的实表，物化视图可以用于预先计算并保存多表的链接（JOIN）
或聚集（GROUP BY）等耗时较多的SQL操作结果，sql server 叫做索引视图。
物化视图的刷新是指当基表发生DML操作，物化视图何时采用哪种方式和基表进行同步：
on demand
on commit
mysql本事不支持物化视图，但是可以通过一些机制实现实现物化视图的功能，例如创建一个on demand的物化视图：

mysql> create table orders(
-> order_id int unsigned not null auto_increment,
-> product_name varchar(30) not null,
-> price decimal(8,2) not null,
-> amount smallint not null,
-> primary key (order_id)
-> )engine=innodb;

mysql> select * from orders;
±---------±-------------±-------±-------+
| order_id | product_name | price | amount |
±---------±-------------±-------±-------+
| 1 | CPU | 135.00 | 1 |
| 2 | MEMORY | 48.00 | 3 |
| 3 | CPU | 126.00 | 3 |
| 4 | CPU | 105.00 | 4 |
±---------±-------------±-------±-------+

mysql> create table orders_mv(
-> product_name varchar(30) not null,
-> price_sum decimal(8,2) not null,
-> amount_sum int not null,
-> price_avg float not null,
-> orders_cnt int not null,
-> unique index(product_name)
-> );

mysql> insert into orders_mv select product_name ,sum(price),sum(amount),avg(price),count(*) from orders group by product_name;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from orders_mv;
±-------------±----------±-----------±----------±-----------+
| product_name | price_sum | amount_sum | price_avg | orders_cnt |
±-------------±----------±-----------±----------±-----------+
| CPU | 366.00 | 8 | 122 | 3 |
| MEMORY | 48.00 | 3 | 48 | 1 |
±-------------±----------±-----------±----------±-----------+

---

---

但是要是实现 on commit 的物化视图，就没这么简单了，Oracle通过物化视图日志来实现，
mysql通过触发器同样可以达到这个目的：

mysql> \d $$mysql>createtriggerp1afterinsertonorders->foreachrow->begin->set@ol{d}_{p}ric{e}_{s}um=0;->set@ol{d}_{a}moun{t}_{s}um=0;->set@ol{d}_{p}ric{e}_{a}vg=0;->set@ol{d}_{o}rder{s}_{c}nt=0;->selectifnull(pric{e}_{s}um,0),ifnull(amoun{t}_{s}um,0),ifnull(pric{e}_{a}vg,0),ifnull(order{s}_{c}nt,0)->fromorder{s}_{m}v->whereproduc{t}_{n}ame=new.produc{t}_{n}ame->into@ol{d}_{p}ric{e}_{s}um,@ol{d}_{a}moun{t}_{s}um,@ol{d}_{p}ric{e}_{a}vg,@ol{d}_{o}rder{s}_{c}nt;->set@ne{w}_{p}ric{e}_{s}um=@ol{d}_{p}ric{e}_{s}um+new.price;->set@ne{w}_{a}moun{t}_{s}um=@ol{d}_{a}moun{t}_{s}um+new.amount;->set@ne{w}_{o}rder{s}_{c}nt=@ol{d}_{o}rder{s}_{c}nt+1;->set@ne{w}_{p}ric{e}_{a}vg=@ne{w}_{p}ric{e}_{s}um/@ne{w}_{o}rder{s}_{c}nt;->replaceintoorder{s}_{m}v->values(new.produc{t}_{n}ame,@ne{w}_{p}ric{e}_{s}um,@ne{w}_{a}moun{t}_{s}um,@ne{w}_{p}ric{e}_{a}vg,@ne{w}_{o}rder{s}_{c}nt);->end;->$$

insert into orders values(NULL,‘SSD’,299,3);

mysql> select *from orders;
±---------±-------------±-------±-------+
| order_id | product_name | price | amount |
±---------±-------------±-------±-------+
| 1 | CPU | 135.00 | 1 |
| 2 | MEMORY | 48.00 | 3 |
| 3 | CPU | 126.00 | 3 |
| 4 | CPU | 105.00 | 4 |
| 11 | SSD | 299.00 | 3 |
±---------±-------------±-------±-------+
5 rows in set (0.00 sec)

mysql> select * from orders_mv;
±-------------±----------±-----------±----------±-----------+
| product_name | price_sum | amount_sum | price_avg | orders_cnt |
±-------------±----------±-----------±----------±-----------+
| CPU | 366.00 | 8 | 122 | 3 |
| MEMORY | 48.00 | 3 | 48 | 1 |
| SSD | 299.00 | 3 | 299 | 1 |
±-------------±----------±-----------±----------±-----------+