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

MySQLviewshavelimitations:1)Theydon'tsupportallSQLoperations,restrictingdatamanipulationthroughviewswithjoinsorsubqueries.2)Theycanimpactperformance,especiallywithcomplexqueriesorlargedatasets.3)Viewsdon'tstoredata,potentiallyleadingtooutdatedinforma

ProperusermanagementinMySQLiscrucialforenhancingsecurityandensuringefficientdatabaseoperation.1)UseCREATEUSERtoaddusers,specifyingconnectionsourcewith@'localhost'or@'%'.2)GrantspecificprivilegeswithGRANT,usingleastprivilegeprincipletominimizerisks.3)

MySQLdoesn'timposeahardlimitontriggers,butpracticalfactorsdeterminetheireffectiveuse:1)Serverconfigurationimpactstriggermanagement;2)Complextriggersincreasesystemload;3)Largertablesslowtriggerperformance;4)Highconcurrencycancausetriggercontention;5)M

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

Adding MySQL users through the PHP web interface can use MySQLi extensions. The steps are as follows: 1. Connect to the MySQL database and use the MySQLi extension. 2. Create a user, use the CREATEUSER statement, and use the PASSWORD() function to encrypt the password. 3. Prevent SQL injection and use the mysqli_real_escape_string() function to process user input. 4. Assign permissions to new users and use the GRANT statement.

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

Zend Studio 13.0.1
Powerful PHP integrated development environment

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

Dreamweaver Mac version
Visual web development tools
