MySQL通過觸發(fā)器解決數(shù)據(jù)庫中表的行數(shù)限制詳解及實例
最近項目一個需求是對操作日志的數(shù)量限制為10萬條，超過十萬條便刪除最舊的那一條，保存數(shù)據(jù)庫中日志數(shù)量不超過10萬。
當時我的第一想法是通過觸發(fā)器來做，便在數(shù)據(jù)庫中執(zhí)行了如下的SQL:
| delimiter $create trigger limitLogbeforeinserton OperationLogfor each rowbeginif (select count(*) from OperationLog) > 100000 thendelete from OperationLog limit 1;end if;end $delimiter ; |
看起來似乎沒什么問題，對于insert前執(zhí)行判斷，如果數(shù)量超過100000就執(zhí)行刪除。但在真正數(shù)據(jù)庫超過100000條，也就是開始執(zhí)行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為了防止觸發(fā)器遞歸死循環(huán)的執(zhí)行，不允許在某張表的觸發(fā)器中直接對該表進行DML(SELECT，DELETE，UPDATE，INSERT)操作，當然可以對其他表進行這樣操作。
觸發(fā)器限制的是執(zhí)行對該表的DML操作。觸發(fā)器可以在你的執(zhí)行前后來修改要執(zhí)行的這一行數(shù)據(jù)，通過set關鍵字。
| delimiter $create trigger setLogbeforeinserton OperationLogfor each rowbeginset NEW.action = 'test';end $delimiter ; |
上述語句表示在insert OpetationLog表的之前，更新insert這條數(shù)據(jù)的action字段值為test，NEW就表示新添加的這條字段，同樣的OLD就表示delete時的字段。而在update的時候NEW以及OLD同時都可以使用。
臨時觸發(fā)器
剛剛談到的觸發(fā)器(Triggers)是基于某個表所產(chǎn)生的事件觸發(fā)的，而臨時觸發(fā)器也稱為事件調度器是基于特定時間周期觸發(fā)來執(zhí)行某些任務。MySQL的事件調度器可以精確到每秒鐘執(zhí)行一個任務，而操作系統(tǒng)的計劃任務（如：Linux下的CRON或Windows下的任務計劃）只能精 確到每分鐘執(zhí)行一次。對于一些對數(shù)據(jù)實時性要求比較高的應用（例如：股票、賠率、比分等）就非常適合。
在使用這個功能之前必須確保event_scheduler已開啟，可執(zhí)行
| GLOBAL event_scheduler = 1; |
或者
| SET GLOBAL event_scheduler = ON; |
要查看當前是否已開啟事件調度器，可執(zhí)行如下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 ; |
