如何在MySQL表中设置最大行数? [英] How can I set a maximum number of rows in MySQL table?

问题描述

我需要在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属性不是硬性限制(存储不超过10万行并删除其他行")，而是数据库引擎的提示，该表将至少有10万行.

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 ;

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

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.

这篇关于如何在MySQL表中设置最大行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！