搜索
首页数据库mysql教程通过触发器实现物化视图_MySQL

bitsCN.com
通过触发器实现物化视图 在电商平台中,我们有时需要对用户订单进行一些聚合计算,如订单总数有多少,总金额有多少,平均价格是多少,而实现这个特性基本有下面几个办法: 一, 每次查询这些聚合信息的时候,直接执行SQL语句的sum,avg,count等,好处是实现简单,不足是每次均需要进行扫表查询,特别是订单变更比较少,而查询比较多的情况下,此方法会浪费不少的机器资源。 二, 新建一个聚合表,当有订单增删改的时候,通过程序进行计算新的聚合信息,然后存储到该聚合表,每次查询的时候只需查询对应计算好的记录即可,好处是查询非常简单,不足是需要应用程序进行同步聚合信息,且如果订单库操作整个,而聚合库失败,则需要保证数据的一致性。 三,利用DB的触发器实现物化视图的方式,好处是数据的同步交给db 去保证,应用程序无需关注,并且若触发器执行失败,则对应的源表操作也会回滚,不足是需要开发对应的触发器程序。本文主要说明用触发器实现这样的一个特性,为了更好的说明如何创建的过程,我们举了这样一个例子,该例子已经在mysql全部调试通过。          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;           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;               $$           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)                             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                                    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 bitsCN.com

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
如何隐藏文本直到在 Powerpoint 中单击如何隐藏文本直到在 Powerpoint 中单击Apr 14, 2023 pm 04:40 PM

如何在 PowerPoint 中的任何点击之前隐藏文本如果您希望在单击 PowerPoint 幻灯片上的任意位置时显示文本,那么设置起来既快速又容易。要在 PowerPoint 中单击任何按钮之前隐藏文本:打开您的 PowerPoint 文档,然后单击“插入 ”菜单。单击新幻灯片。选择空白或其他预设之一。仍然在插入菜单中,单击文本框。在幻灯片上拖出一个文本框。单击文本框并输入您

拯救者y7000p玩cf分辨率多少(拯救者y7000玩cf怎么调全屏)拯救者y7000p玩cf分辨率多少(拯救者y7000玩cf怎么调全屏)Jan 07, 2024 am 10:13 AM

拯救者y7000p玩cf分辨率多少拯救者Y7000P玩CF的分辨率为1920*1080。因为该电脑配备了GTX1650显卡和i5-9300H处理器,性能较为优秀,足以满足CF这类游戏的需求。同时,1920*1080是目前主流电竞显示器的分辨率,画质清晰度足够。另外,如果有更高要求的玩家,可以适当降低游戏画质的设置,以获得更加流畅的游戏体验。为了享受更清晰的视觉体验,你可以将拯救者y7000p的分辨率调整为2560*1400。这样,你将能够享受到更高质量的图像显示。拯救者Y7000P2022款搭载

mysql 怎么修改hostmysql 怎么修改hostFeb 15, 2023 am 10:17 AM

mysql修改host的方法:1、通过“sudo service mysql stop”停掉mysql服务;2、以安全模式启动mysql,然后重置root密码;3、通过“update user set Host='%' where User='hive';”语句修改host即可。

oracle如何添加触发器oracle如何添加触发器Dec 12, 2023 am 10:17 AM

在Oracle数据库中,您可以使用CREATE TRIGGER语句来添加触发器。触发器是一种数据库对象,它可以在数据库表上定义一个或多个事件,并在事件发生时自动执行相应的操作。

我准备去西藏旅行背包去①背多少升的包合适把你认为最好的配置说下本人170体力不错第一次我准备去西藏旅行背包去①背多少升的包合适把你认为最好的配置说下本人170体力不错第一次Jan 07, 2024 am 10:06 AM

我准备去西藏旅行背包去①背多少升的包合适把你认为最好的配置说下本人170体力不错第一次去徒步多就60升或以上的徒步少就60升以下的全程都坐车就不用背包,旅行箱更方便,真要随身带东西,弄个25~40升的就绰绰有馀西藏旅游必备用品:太阳镜、太阳帽、防晒霜、护肤霜、润唇膏、长袖上衣、毛衣;对于特殊旅游或去阿里、藏北、川藏线旅游,建议带:睡袋(防寒)、床单(防脏)、羽绒服、旅游鞋或登山鞋、拖鞋、牙刷、牙膏、毛巾、卷筒纸、纸内裤、消毒湿巾、手电筒、防水火柴、刀具、绳子。前运包能装电脑吗能装电脑,有些背包有

mysql的触发器是什么级的mysql的触发器是什么级的Mar 30, 2023 pm 08:05 PM

mysql的触发器是行级的。按照SQL标准,触发器可以分为两种:1、行级触发器,对于修改的每一行数据都会激活一次,如果一个语句插入了100行数据,将会调用触发器100次;2、语句级触发器,针对每个语句激活一次,一个插入100行数据的语句只会调用一次触发器。而MySQL中只支持行级触发器,不支持预语句级触发器。

如何使用MySQL的触发器实现数据的自动归档如何使用MySQL的触发器实现数据的自动归档Aug 02, 2023 am 10:37 AM

如何使用MySQL的触发器实现数据的自动归档引言:在现代数据管理领域,数据的自动归档和清理是一个重要而又常见的需求。随着数据量的增加,保留完整的历史数据会占用过多的存储资源,并且会降低查询性能。MySQL的触发器提供了实现这一需求的有效方法。本文将介绍如何使用MySQL的触发器来实现数据的自动归档。一、什么是MySQL的触发器MySQL的触发器是一种特殊的存

如何在MySQL中使用PHP编写自定义触发器和存储过程如何在MySQL中使用PHP编写自定义触发器和存储过程Sep 20, 2023 am 11:25 AM

如何在MySQL中使用PHP编写自定义触发器和存储过程引言:在开发应用程序时,我们经常需要在数据库层面进行一些操作,如插入、更新或删除数据。MySQL是一个广泛使用的关系型数据库管理系统,而PHP是一种流行的服务器端脚本语言。本文将介绍如何在MySQL中使用PHP编写自定义触发器和存储过程,并提供具体的代码示例。一、什么是触发器和存储过程触发器(Trigg

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前By尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
1 个月前By尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前By尊渡假赌尊渡假赌尊渡假赌

热工具

螳螂BT

螳螂BT

Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

VSCode Windows 64位 下载

VSCode Windows 64位 下载

微软推出的免费、功能强大的一款IDE编辑器

Dreamweaver Mac版

Dreamweaver Mac版

视觉化网页开发工具

SublimeText3 英文版

SublimeText3 英文版

推荐:为Win版本,支持代码提示!

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器