最近做的一个手机售票项目中,需要删除未付款的订单,同时更新库存信息,这里我们使用到了MySQL的触发器。由于该项目中使用了订单表和订单明细表,因此我们首先需要在订单表中创建一个触发器,在删除订单表中信息的同时,删除订单明细表中的信息,该触发器如下所示:
USE `smc`;//切换到相应数据库
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `orders_BDEL` BEFORE DELETE ON `orders` FOR EACH ROW//创建一个在删除前触发的触发器
begin
delete from orderdetail
where orderdetail.order_id = OLD.id;//OLD指的是被删除的记录,也就是这里的orders表
end
删除订单明细表中信息的同时,我们需要更新库存信息,触发器如下:
USE `smc`;
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `orderdetail_BDEL` BEFORE DELETE ON `orderdetail` FOR EACH ROW
begin
update ticket set ticket.inventory=ticket.inventory+1//更新票的库存
where ticket.id = OLD.ticket_id;
end
经过这两个触发器,就可以完成删除订单的同时,更新库存信息。
由于该公司的交易量不大,因此我们决定在每天凌晨执行一次删除操作,将下单时间超过一天的订单删除掉,这里我们使用到了Windows的计划任务,规定在每天凌晨3点执行一次删除订单的sql语句。这里,我们将删除订单信息的sql语句写在del_orders.sql文件中,如下所示:
use smc;
SET SQL_SAFE_UPDATES=0;//MySQL为了数据库更新操作的安全性,此值默认为1,不带where和limit条件的update和delete操作语句是无法执行的,即使是有where和limit条件但不带key column的update和delete也不能执行。SQL_SAFE_UPDATES =0时,update和delete操作将会顺利执行。
delete FROM orders where to_days(now())-to_days(orders.createTime)>=1 and orders.status=0;//删除下单时间超过一天且未付款的订单
sql文件写好后,我们将它放到D盘根目录下,然后,新建一个批处理文件del_orders.bat,用记事本打开,编写如下脚本:
@echo off
"D:\Program-Files\MySQL\MySQL Server 5.6\bin\mysql" -hlocalhost -uroot -p1234 < D:\del_orders.sql
这里双引号中的内容指的是将目录切换到MySQL安装目录中bin下面的mysql.exe,之所以要在这里使用双引号,是因为MySQL安装路径中含有空格,脚本无法识别,如果你的路径没有空格,则不用添加引号。-u后面跟上MySQL用户名,-p后面跟上MySQL密码,"<"为导入文件,后面的路径为sql文件的路径。
批处理文件写好后,我们可以手动双击执行,因为我们每天都要执行该操作,因此使用了Windows的“计划任务”。打开Windows控制面板,找到“计划任务”,进去后,选择“创建基本任务”,第一步,为该任务命名;第二步,选择任务的执行时间,这里,我们选择“每天”,然后设置每天执行的开始时间;第三步,选择操作类型,我们选择“启动程序”;第四步,选择“浏览”,将上面的批处理文件添加进来;最后一步,点击完成。至此,定时任务就完成了。
通过这种方式,每天凌晨,Windows会自动执行该批处理文件,在执行该文件的时候,会将删除订单的sql文件加载进来,这个时候就会执行删除下单超过一天但未付款的订单的操作,在执行删除操作之前就会触发我们前面创建的两个触发器,这样就实现了自动删除超时订单和更新库存的操作。
同理,我们可以通过这种方式完成MySQL数据库文件的自动备份,在每天执行删除订单信息之前备份数据库文件,以免发生意外。备份数据库的批处理脚本如下:
@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"D:\Program-Files\MySQL\MySQL Server 5.6\bin\mysqldump" --opt -u root --password=1234 smc > D:\db_backup\smc_%Ymd%.sql//">"前面的smc为要备份的数据库,">"后面为备份文件的存放位置
@echo on