MySQL uses triggers to solve the row limit of the table in the database. Detailed explanation and examples

MySQL uses triggers to solve the row limit of the table in the database. Detailed explanation and examples

A recent project requirement is to limit the number of operation logs to 100,000. If there are more than 100,000, the oldest one will be deleted, and the number of logs saved in the database will not exceed 100,000.
My first thought was to use a trigger, so I executed the following SQL in the database:

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 ;

It seems that there is no problem. A check is performed before inserting, and if the number exceeds 100,000, the deletion is executed. But when the actual database exceeds 100,000 records, a problem occurs when the IF statement starts to execute, and MySQL reports an error:

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.

After consulting the information, I found out that in order to prevent the execution of recursive infinite loops of triggers, MySQL does not allow DML (SELECT, DELETE, UPDATE, INSERT) operations to be performed directly on a table in the trigger of a certain table. Of course, such operations can be performed on other tables.

Triggers restrict the DML operations that can be performed on the table. The trigger can modify the row of data to be executed before or after your execution through the set keyword.

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

delimiter ;

The above statement indicates that before inserting the OpetationLog table, the action field value of the inserted data is updated to test. NEW indicates the newly added field, and OLD indicates the field when it is deleted. When updating, both NEW and OLD can be used at the same time.

Temporary triggers

The triggers just discussed are triggered based on events generated by a certain table, while temporary triggers, also known as event schedulers, are triggered based on a specific time period to perform certain tasks. MySQL's event scheduler can execute a task accurately to one second, while the operating system's scheduled tasks (such as CRON under Linux or task scheduling under Windows) can only be executed accurately to once per minute. It is very suitable for applications that have high requirements for real-time data (such as stocks, odds, scores, etc.).

Before using this function, you must ensure that event_scheduler is enabled and executable

 GLOBAL event_scheduler = 1;

or

SET GLOBAL event_scheduler = ON;

To check whether the event scheduler is currently enabled, execute the following SQL:

SHOW VARIABLES LIKE 'event_scheduler';

or

SELECT @@event_scheduler;

or

SHOW PROCESSLIST;

As for the problems mentioned at the beginning of this article, this mechanism can be perfectly solved:

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 ;

Proven effectiveness

Thank you for reading, I hope it can help you, thank you for your support of this site!