MySQL通過(guò)觸發(fā)器解決數(shù)據(jù)庫(kù)中表的行數(shù)限制詳解及實(shí)例
最近項(xiàng)目一個(gè)需求是對(duì)操作日志的數(shù)量限制為10萬(wàn)條,超過(guò)十萬(wàn)條便刪除最舊的那一條,保存數(shù)據(jù)庫(kù)中日志數(shù)量不超過(guò)10萬(wàn)。
當(dāng)時(shí)我的第一想法是通過(guò)觸發(fā)器來(lái)做,便在數(shù)據(jù)庫(kù)中執(zhí)行了如下的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 ;
看起來(lái)似乎沒(méi)什么問(wèn)題,對(duì)于insert前執(zhí)行判斷,如果數(shù)量超過(guò)100000就執(zhí)行刪除。但在真正數(shù)據(jù)庫(kù)超過(guò)100000條,也就是開(kāi)始執(zhí)行IF語(yǔ)句的時(shí)候就出問(wèn)題,MySQL報(bào)錯(cuò):
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為了防止觸發(fā)器遞歸死循環(huán)的執(zhí)行,不允許在某張表的觸發(fā)器中直接對(duì)該表進(jìn)行DML(SELECT,DELETE,UPDATE,INSERT)操作,當(dāng)然可以對(duì)其他表進(jìn)行這樣操作。
觸發(fā)器限制的是執(zhí)行對(duì)該表的DML操作。觸發(fā)器可以在你的執(zhí)行前后來(lái)修改要執(zhí)行的這一行數(shù)據(jù),通過(guò)set關(guān)鍵字。
delimiter $
create trigger setLog
before
insert
on OperationLog
for each row
begin
set NEW.action = 'test';
end $
delimiter ;
上述語(yǔ)句表示在insert OpetationLog表的之前,更新insert這條數(shù)據(jù)的action字段值為test,NEW就表示新添加的這條字段,同樣的OLD就表示delete時(shí)的字段。而在update的時(shí)候NEW以及OLD同時(shí)都可以使用。
臨時(shí)觸發(fā)器
剛剛談到的觸發(fā)器(Triggers)是基于某個(gè)表所產(chǎn)生的事件觸發(fā)的,而臨時(shí)觸發(fā)器也稱為事件調(diào)度器是基于特定時(shí)間周期觸發(fā)來(lái)執(zhí)行某些任務(wù)。MySQL的事件調(diào)度器可以精確到每秒鐘執(zhí)行一個(gè)任務(wù),而操作系統(tǒng)的計(jì)劃任務(wù)(如:Linux下的CRON或Windows下的任務(wù)計(jì)劃)只能精 確到每分鐘執(zhí)行一次。對(duì)于一些對(duì)數(shù)據(jù)實(shí)時(shí)性要求比較高的應(yīng)用(例如:股票、賠率、比分等)就非常適合。
在使用這個(gè)功能之前必須確保event_scheduler已開(kāi)啟,可執(zhí)行
GLOBAL event_scheduler = 1;
或者
SET GLOBAL event_scheduler = ON;
要查看當(dāng)前是否已開(kāi)啟事件調(diào)度器,可執(zhí)行如下SQL:
SHOW VARIABLES LIKE 'event_scheduler';
或
SELECT @@event_scheduler;
或
而對(duì)于本文一開(kāi)始提到的問(wèn)題,使用這種機(jī)制則可完美解決:
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 ;
親測(cè)有效
感謝閱讀,希望能幫助到大家,謝謝大家對(duì)本站的支持!
您可能感興趣的文章:- mysql實(shí)現(xiàn)查詢最接近的記錄數(shù)據(jù)示例
- 詳解MySQL恢復(fù)psc文件記錄數(shù)為0的解決方案
- PHP+MySQL統(tǒng)計(jì)該庫(kù)中每個(gè)表的記錄數(shù)并按遞減順序排列的方法
- MySQL數(shù)據(jù)庫(kù)查看數(shù)據(jù)表占用空間大小和記錄數(shù)的方法
- MYSQL速度慢的問(wèn)題 記錄數(shù)據(jù)庫(kù)語(yǔ)句
- MySQL分區(qū)表的局限和限制詳解
- MySQL 如何限制一張表的記錄數(shù)