MySQL通过触发器解决数据库中表的行数限制详解及实例

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 ;

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.

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

delimiter ;

 GLOBAL event_scheduler = 1;

SET GLOBAL event_scheduler = ON;

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 ;