程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 通過觸發器實現物化視圖

通過觸發器實現物化視圖

編輯:MySQL綜合教程


通過觸發器實現物化視圖   在電商平台中,我們有時需要對用戶訂單進行一些聚合計算,如訂單總數有多少,總金額有多少,平均價格是多少,而實現這個特性基本有下面幾個辦法:   一, 每次查詢這些聚合信息的時候,直接執行SQL語句的sum,avg,count等,好處是實現簡單,不足是每次均需要進行掃表查詢,特別是訂單變更比較少,而查詢比較多的情況下,此方法會浪費不少的機器資源。   二, 新建一個聚合表,當有訂單增刪改的時候,通過程序進行計算新的聚合信息,然後存儲到該聚合表,每次查詢的時候只需查詢對應計算好的記錄即可,好處是查詢非常簡單,不足是需要應用程序進行同步聚合信息,且如果訂單庫操作整個,而聚合庫失敗,則需要保證數據的一致性。   三,利用DB的觸發器實現物化視圖的方式,好處是數據的同步交給db 去保證,應用程序無需關注,並且若觸發器執行失敗,則對應的源表操作也會回滾,不足是需要開發對應的觸發器程序。本文主要說明用觸發器實現這樣的一個特性,為了更好的說明如何創建的過程,我們舉了這樣一個例子,該例子已經在mysql全部調試通過。  www.2cto.com          1, 新建一個訂單表               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,創建一個存儲聚合信息的表              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)  //因為需要按照產品名字聚合,這裡把product_name作為唯一key進行去重            ) engine=innodb;  www.2cto.com           3,為表orders創建after insert的觸發器               首先說明一下如何查看一個表中是否已經創建了哪些觸發器:
                        select * from information_schema.TRIGGERS 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, @new_price_avg, @new_orders_cnt);              end;  www.2cto.com              $$            delimiter ;           4,為表orders創建after update的觸發器              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),                                //錯誤,此時的price_sum已經是新值, 不能重新 -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),                                       //錯誤,此時的price_sum已經是新值, 不能重新 -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,為表orders創建after delete的觸發器              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 ;       作者 tenfyguo

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved