将 mysql 表限制为特定大小并自动删除最旧的条目 [英] Limiting mysql table to a sertain size and automatically deleting oldest entries

问题描述

可能的重复:
如何设置最大数量MySQL 表中的行?

是否可以(以及如何)对 MySQL 表设置限制(比如 100'000)并在达到限制时删除旧条目?

Is it possible ( and how ) to put a limit on a MySQL table ( let's say 100'000 ) and deleting the old entries when limit reaches?

意思是，当我有 100'000 个条目并且出现 100'001 时，ID 最小的条目将被删除并创建新的条目(当然是新的 ID).

Meaning, when I have 100'000 entries and the 100'001 appears, the entry with the smallest ID is deleted and the new one is created ( with the new ID of course ).

我希望 MySQL 自己处理这个问题，所以不需要外部脚本干扰.

I want MySQL to handle this on it's own, so no outside scripts need to interfere.

我需要这个来记录日志，也就是说，我只想将日志保留一段时间，比如说一周.也许 MySQL 可以仅删除超过 1 周的条目?

I need this for logging purposes, meaning, I want to keep logs only for a certain time period, let's say a week. Maybe it is possible for MySQL just to delete entries, that are older then 1 week on it's own?

推荐答案

我建议触发器.这是确保在每次插入时都考虑到最大表大小的最佳方式.

I propose triggers. This is the best way of insuring that at each insert the maximum table size is being taken into account.

可能重复的如何设置 MySQL 表的最大行数?

来自那个接受的答案:

尝试限制向表中添加新记录.将要添加新记录时引发错误.

Try to make a restriction on adding a new record to a table. Raise an error when a new record is going to be added.

DELIMITER $$

CREATE TRIGGER trigger1
BEFORE INSERT
ON table1
FOR EACH ROW
BEGIN
  SELECT COUNT(*) INTO @cnt FROM table1;
  IF @cnt >= 25 THEN
    CALL sth(); -- raise an error
  END IF;
END
$$

DELIMITER ;

请注意，COUNT 操作在大型 InnoDb 表上可能会很慢.

Note, that COUNT operation may be slow on big InnoDb tables.

在 MySQL 5.5 上，您可以使用 SIGNAL 语句引发错误.

On MySQL 5.5 you can use SIGNAL statement to raise an error.

这篇关于将 mysql 表限制为特定大小并自动删除最旧的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！