MySQL触发器：如果表变得太大，是否可以删除行？

When inserting a new row in a table T, I would like to check if the table is larger than a certain threshold, and if it is the case delete the oldest record (creating some kind of FIFO in the end).

在表T中插入新行时,我想检查表是否大于某个阈值,如果是这种情况,则删除最旧的记录(最后创建某种FIFO)。

I thought I could simply make a trigger, but apparently MySQL doesn't allow the modification of the table on which we are actually inserting :

我以为我可以简单地创建一个触发器,但显然MySQL不允许修改我们实际插入的表:

Code: 1442  Msg: Can't update table 'amoreAgentTST01' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Here is the trigger I tried :

这是我试过的触发器:

Delimiter $$ 
CREATE TRIGGER test 
       AFTER INSERT ON amoreAgentTST01
       FOR EACH ROW
BEGIN
    DECLARE table_size INTEGER;
    DECLARE new_row_size INTEGER;
    DECLARE threshold INTEGER;
    DECLARE max_update_time TIMESTAMP;

SELECT SUM(OCTET_LENGTH(data)) INTO table_size FROM amoreAgentTST01;
SELECT OCTET_LENGTH(NEW.data) INTO new_row_size;
SELECT 500000 INTO threshold;
select max(updatetime) INTO max_update_time  from amoreAgentTST01;

IF (table_size+new_row_size) > threshold THEN
   DELETE FROM amoreAgentTST01 WHERE max_update_time = updatetime; -- and check if not current
END IF;
END$$
delimiter ;

Do you have any idea on how to do this within the database ?

您是否知道如何在数据库中执行此操作?

Or it is clearly something to be done in my program ?

或者显然我的计划要做些什么?

1 个解决方案

#1

Ideally you should have a dedicated archive strategy in a separate process that runs at off-peak times.

理想情况下,您应该在一个在非高峰时间运行的单独进程中拥有专用的归档策略。

You could implement this either as a scheduled stored procedure (yuck) or an additional background worker thread within your application server, or a totally separate application service. This would be a good place to put other regular housekeeping jobs.

您可以将其实现为计划存储过程(yuck)或应用程序服务器中的其他后台工作线程,或者完全独立的应用程序服务。这将是其他常规家务工作的好地方。

This has a few benefits. Apart from avoiding the trigger issue you're seeing, you should consider the performance implications of anything happening in a trigger. If you do many inserts, that trigger will do that work and effectively half the performance, not to mention the lock contention that will arise as other processes try to access the same table.

这有一些好处。除了避免你所看到的触发问题,你应该考虑触发器中发生的任何事情的性能影响。如果你进行了很多插入操作,那么该触发器将完成这项工作并有效地实现性能的一半,更不用说在其他进程尝试访问同一个表时会出现的锁争用。

A separate process that does housekeeping work minimises lock contention, and allows the work to be carried out as a high-performance bulk operation, in a transaction.

执行内务处理工作的单独流程可最大限度地减少锁争用,并允许在事务中将工作作为高性能批量操作来执行。

One last thing - you should possibly consider archiving records to another table or database, rather than deleting them.

最后一件事 - 您应该考虑将记录存档到另一个表或数据库,而不是删除它们。

#1