MySQL uses triggers to limit the number of rows in a database table and provides examples.

MySQL uses triggers to limit the number of rows in a database table and provides examples.

MySQL uses triggers to limit the number of rows in a database table and provides examples.

A requirement for a recent project is to limit the number of operation logs to 0.1 million. If the number of operation logs exceeds 100,000, the oldest log will be deleted. The number of logs stored in the database cannot exceed 0.1 million.
At that time, my first thought was to use a trigger to execute the following SQL statement in the database:

delimiter $create trigger limitLogbeforeinserton OperationLogfor each rowbeginif (select count(*) from OperationLog) > 100000 thendelete from OperationLog limit 1;end if;end $delimiter ;

It seems that there is no problem. For the pre-insert judgment, if the number exceeds 100000, the deletion will be executed. However, when the real database contains more than 100000 records, that is, when the IF statement is executed, an error occurs. MySQL reports the following 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.

I can check the relevant information before knowing that MySQL does not allow direct DML (SELECT, DELETE, UPDATE, INSERT) on a table trigger to prevent recursive and endless execution of the trigger) of course, you can perform this operation on other tables.

The trigger limits the DML operation on the table. The trigger can modify the row of data to be executed before and after your execution, using the set keyword.

delimiter $create trigger setLogbeforeinserton OperationLogfor each rowbeginset NEW.action = 'test';end $delimiter ;

The preceding statement indicates that before the insert OpetationLog table, the value of the action field for updating the insert data is test, and NEW indicates the newly added field, and the same OLD value indicates the field during the delete operation. Both NEW and OLD can be used during update.

Temporary trigger

Triggers is triggered based on the events generated by a table, and the temporary trigger is also called the event scheduler to execute certain tasks based on a specific time period. The MySQL event scheduler can execute a task exactly every second, and the scheduled task of the operating system (such as CRON in Linux or task plan in Windows) it can only be executed once per minute. It is suitable for applications that require high real-time data (such as stocks, odds, and scores.

Before using this function, make sure that event_scheduler is enabled.

 GLOBAL event_scheduler = 1;

Or

SET GLOBAL event_scheduler = ON;

To check whether the event scheduler is enabled, run the following SQL statement:

SHOW VARIABLES LIKE 'event_scheduler';

Or

SELECT @@event_scheduler;

Or

SHOW PROCESSLIST;

For the problems mentioned at the beginning of this article, using 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 ;

Active in Test

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!