如何设置 MySQL 表中的最大行数?

2021-11-20 00:00:00 mysql triggers

我需要在我的 MySQL 表中设置最大行数限制.文档告诉我们可以使用以下 SQL 代码来创建表:

I need to set a maximum limit of rows in my MySQL table. Documentation tell us that one can use following SQL code to create table:

CREATE TABLE `table_with_limit` 
   `id` int(11) DEFAULT NULL
) ENGINE=InnoDB MAX_ROWS=100000

但是 MAX_ROWS 属性不是硬性限制(存储不超过 100 000 行并删除其他行")而是对数据库引擎的提示，即该表将至少有 100 000 行.

But MAX_ROWS property is not a hard limit ("store not more then 100 000 rows and delete other") but a hint for database engine that this table will have AT LEAST 100 000 rows.

我认为解决问题的唯一可能方法是使用 BEFORE INSERT 触发器，该触发器将检查表中的行数并删除较旧的行.但我很确定这是一个巨大的过热:/

The only possible way I see to solve the problem is to use BEFORE INSERT trigger which will check the count of rows in table and delete the older rows. But I'm pretty sure that this is a huge overheat :/

另一种解决方案是每 N 分钟用 cron 脚本清除表.这是最简单的方法，但仍然需要另一个系统来监视.

Another solution is to clear the table with cron script every N minutes. This is a simplest way, but still it needs another system to watch for.

有人知道更好的解决方案吗?:)

Anyone knows a better solution? :)

推荐答案

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

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//RESIGNAL 语句引发错误.

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

相关文章