ITKeyword,专注技术干货聚合推荐

注册 | 登录

MySQL通过触发器解决数据库中表的行数限制的需求

canot 分享于 2017-04-05

推荐:如何用触发器管理MySQL数据库

在SQL中,名词触发器指“在数据库中为响应一个特殊表格中的某些事件而自动执行的程序代码。”(Wikipedia)说得简单一些,它是在一个特殊的数据库事件,如INSERT或

2020腾讯云“6.18”活动开始了!!!(巨大优惠重现!4核8G,5M带宽 1999元/3年),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1059

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
地址https://www.aliyun.com/minisite/goods

最近项目一个需求是对操作日志的数量限制为10万条,超过十万条便删除最旧的那一条,保存数据库中日志数量不超过10万。
当时我的第一想法是通过触发器来做,便在数据库中执行了如下的SQL:

delimiter $
create trigger limitLog before insert on OperationLog for each row begin if (select count(*) from OperationLog) > 100000 then delete from OperationLog limit 1;
end if;
end $ delimiter ;

看起来似乎没什么问题,对于insert前执行判断,如果数量超过100000就执行删除。但在真正数据库超过100000条,也就是开始执行IF语句的时候就出问题,Mysql报错:

ERROR 1442 (HY000): Can't update table 'OperationLog' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

查阅资料才知道,MySQL为了防止触发器递归死循环的执行,不允许在某张表的触发器中直接对该表进行DML(SELECT,DELETE,UPDATE,INSERT)操作,当然可以对其他表进行这样操作。
触发器限制的是执行对该表的DML操作。触发器可以在你的执行前后来修改要执行的这一行数据,通过set关键字。

delimiter $
create trigger setLog before insert on OperationLog for each row begin set NEW.action = 'test';
end $ delimiter ;

上述语句表示在insert OpetationLog表的之前,更新insert这条数据的action字段值为test,NEW就表示新添加的这条字段,同样的OLD就表示delete时的字段。而在update的时候NEW以及OLD同时都可以使用。

临时触发器
刚刚谈到的触发器(Triggers)是基于某个表所产生的事件触发的,而临时触发器也称为事件调度器是基于特定时间周期触发来执行某些任务。MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务(如:Linux下的CRON或Windows下的任务计划)只能精 确到每分钟执行一次。对于一些对数据实时性要求比较高的应用(例如:股票、赔率、比分等)就非常适合。
在使用这个功能之前必须确保event_scheduler已开启,可执行

推荐:数据库触发器deleted 与inserted 数据的差异

deleted 与inserted 数据的差异 Inserted 存放进行insert和update 操作后的数据 Deleted 存放进行delete 和update操作前的数据 注意:update 操作相当于先进行de

 GLOBAL event_scheduler = 1;

或者

SET GLOBAL event_scheduler = ON;

要查看当前是否已开启事件调度器,可执行如下SQL:

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

SHOW PROCESSLIST;

而对于本文一开始提到的问题,使用这种机制则可完美解决:

delimiter $
CREATE EVENT limitLog ON SCHEDULE EVERY 1 SECOND DO IF (select count(*) from OperationLog) > 100000 then delete from OperationLog limit 1;END IF $ delimiter ;

亲测有效

推荐:用存储过程或触发器+作业调度进行数据库同步

  /*--同步两个数据库的示例  引用(邹建) 测试环境及同步要求: 有数据库服务器srv1和srv2,两台电脑能互相访问,有数据 srv1.库名..author有字段:id,name,phone, s

最近项目一个需求是对操作日志的数量限制为10万条,超过十万条便删除最旧的那一条,保存数据库中日志数量不超过10万。 当时我的第一想法是通过触发器来做,便在数据库中执行了如下的SQL: delimi

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。