Oracle databases support materialized views-not virtual tables based on base tables, but based on actual tables that are actually present in a table-materialized views are stored on nonvolatile storage devices.
The following experiment creates the Fast refresh mode on COMMIT, which is implemented in MySQL with the trigger INSERT, UPDATE, delete refresh operation
1, the base table creation, Orders table for the base table, ORDER_MV for materialized view chart
Copy Code code as follows:
Mysql> CREATE TABLE Orders (
-> order_id int NOT NULL auto_increment,
-> product_name varchar () NOT NULL,
-> Price Decimal (10,0) is not NULL,
-> amount smallint NOT NULL,
-> primary KEY (order_id));
Query OK, 0 rows affected
Mysql> CREATE TABLE ORDER_MV (
-> product_name varchar () NOT NULL,
-> price_sum Decimal (8.2) NOT NULL,
-> amount_sum int NOT NULL,
-> price_avg float NOT NULL,
-> order_cnt int NOT NULL,
-> Unique index (product_name));
Query OK, 0 rows affected
2. Insert Trigger
Copy Code code as follows:
Delimiter $$
Create Trigger Tgr_orders_insert
After insert on Orders
For each row
Begin
Set @old_price_sum = 0;
Set @old_amount_sum = 0;
Set @old_price_avg = 0;
Set @old_orders_cnt = 0;
Select Ifnull (price_sum,0), Ifnull (amount_sum,0), Ifnull (price_avg,0), Ifnull (order_cnt,0)
From ORDER_MV
where Product_name=new.product_name
Into @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
Set @new_price_sum = @old_price_sum +new.price;
Set @new_amount_sum = @old_amount_sum +new.amount;
Set @new_orders_cnt = @old_orders_cnt +1;
Set @new_price_avg = @new_price_sum/@new_orders_cnt;
Replace into ORDER_MV
VALUES (New.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);
End
$$
delimiter;
3. Update triggers
Copy Code code as follows:
Delimiter $$
Create Trigger Tgr_orders_update
Before update on Orders
For each row
Begin
Set @old_price_sum = 0;
Set @old_amount_sum = 0;
Set @old_price_avg = 0;
Set @old_orders_cnt = 0;
Set @cur_price = 0;
Set @cur_amount = 0;
Select Price,amount from Orders where order_id=new.order_id
into @cur_price, @cur_amount;
Select Ifnull (price_sum,0), Ifnull (amount_sum,0), Ifnull (price_avg,0), Ifnull (order_cnt,0)
From ORDER_MV
where Product_name=new.product_name
Into @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
Set @new_price_sum = @old_price_sum-@cur_price +new.price;
Set @new_amount_sum = @old_amount_sum-@cur_amount +new.amount;
Set @new_orders_cnt = @old_orders_cnt;
Set @new_price_avg = @new_price_sum/@new_orders_cnt;
Replace into ORDER_MV
VALUES (New.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);
End
$$
delimiter;
4, delete trigger
Copy Code code as follows:
Delimiter $$
Create Trigger Tgr_orders_delete
After delete on Orders
For each row
Begin
Set @old_price_sum = 0;
Set @old_amount_sum = 0;
Set @old_price_avg = 0;
Set @old_orders_cnt = 0;
Set @cur_price = 0;
Set @cur_amount = 0;
Select Price,amount from Orders where order_id=old.order_id
into @cur_price, @cur_amount;
Select Ifnull (price_sum,0), Ifnull (amount_sum,0), Ifnull (price_avg,0), Ifnull (order_cnt,0)
From ORDER_MV
where Product_name=old.product_name
Into @old_price_sum, @old_amount_sum, @old_price_avg, @old_orders_cnt;
Set @new_price_sum = @old_price_sum-old.price;
Set @new_amount_sum = @old_amount_sum-old.amount;
Set @new_orders_cnt = @old_orders_cnt-1;
If @new_orders_cnt >0 Then
Set @new_price_avg = @new_price_sum/@new_orders_cnt;
Replace into ORDER_MV
VALUES (Old.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_orders_cnt);
Else
Delete from ORDER_MV where product_name= @old. Name;
End If;
End
$$
delimiter;
5, here Delete trigger has a bug, is in a product of the last order is deleted, ORDER_MV table update can not be achieved, do not know this is not a MySQL bug. Of course, if this can also generate data directly with SQL statements, the direct result is inefficient execution.
Copy Code code as follows:
-> INSERT INTO ORDER_MV
-> Select Product_Name, sum (Price), sum (amount), Avg (Price), COUNT (*) from Orders
-> GROUP BY Product_Name;
