Materialized View through triggers

Using triggers to realize the materialized view on the e-commerce platform, we sometimes need to perform some aggregate calculations on user orders, such as the total number of orders, the total amount, and the average price, there are several ways to implement this feature: 1. When you query these aggregate information, execute the SQL statement sum, avg, count, and so on. The advantage is that the implementation is simple, the disadvantage is that you need to perform a scan Table query every time, especially when there are few order changes. However, if there are many queries, this method will waste a lot of machine resources. 2. Create an aggregate table. When an order is added, deleted, or modified, compute the new aggregate information through the program and store it to the aggregate table, you only need to query the corresponding calculated record for each query. The advantage is that the query is very simple, but the application needs to synchronize the aggregated information. If the order library is complete, but the aggregation library fails, data consistency must be ensured. 3. The database trigger is used to implement the materialized view. The advantage is that data synchronization is provided to the DB to ensure that the application does not need to pay attention to the view. If the trigger fails to be executed, the corresponding source table operations will also be rolled back, and the development of the corresponding trigger program is insufficient. This article mainly describes how to use a trigger to implement such a feature. In order to better illustrate how to create a process, we provide an example that has been successfully debugged in mysql. Www.2cto.com 1, create an order table drop table orders if exists; 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; 2, create a table to store aggregate information drop table orders_mv if exists; 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 key product_name (product_name) // because the product name must be aggregated, product_name is used as the unique key for de-duplication.) engine = innodb; www.2cto.com 3, create an after insert trigger for the table orders first describes how to check whether any triggers have been created in a table:
Select * from orders where event_object_table = 'tbl _ name' \ G drop trigger tgr_orders_insert; 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 (orders_cnt, 0) from orders_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 orders_mv values (NEW. product_name, @ new_price_sum, @ new_amount_sum, @ resolve, @ new_orders_cnt); end; www.2cto.com $ delimiter; 4. Create the after update trigger for the table orders drop trigger tgr_orders_update; delimiter $ create trigger tgr_orders_update after update on orders for each row begin if (STRCMP (OLD. product_name, NEW. product_name) then update orders_mv set price_sum = (price_sum-OLD. price), amount_sum = (amount_sum-OLD. amount), orders_cnt = (orders_cnt-1), // error. At this time, price_sum is a new value and cannot be re-OLD. price + NEW. price // price_avg = (price_sum-OLD. price)/IF (orders_cnt-1)> 0, (orders_cnt-1), 1) price_avg = price_sum/IF (orders_cnt> 0, orders_cnt, 1) where product_name = OLD. product_name; 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 (orders_cnt, 0) www.2cto.com from orders_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 orders_mv values (NEW. product_name, @ new_price_sum, @ new_amount_sum, @ new_price_avg, @ new_orders_cnt); else update orders_mv set price_sum = (price_sum-OLD. price + NEW. price), amount_sum = (amount_sum-OLD. amount + NEW. amount), // error. At this time, price_sum is a new value and cannot be re-OLD. price + NEW. price // price_avg = (price_sum-OLD. price + NEW. price)/IF (orders_cnt> 0, orders_cnt, 1) price_avg = price_sum/IF (orders_cnt> 0, orders_cnt, 1) where product_name = OLD. product_name; end if; end; $ delimiter; 5. Create the after delete trigger for the table orders drop trigger tgr_orders_delete; delimiter $ create trigger tgr_orders_delete after delete on orders for each row begin www.2cto.com update orders_mv set price_sum = (price_sum-OLD. price), amount_sum = (amount_sum-OLD. amount), orders_cnt = (orders_cnt-1), price_avg = price_sum/IF (orders_cnt> 0, orders_cnt, 1) where product_name = OLD. product_name; end; $ delimiter; by tenfyguo