如何在MySQL表中设置最大行数？

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:

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

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

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.

但MAX_ROWS属性不是硬限制（“存储不超过100 000行并删除其他”）但是数据库引擎提示此表将至少有10万行。

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 :/

我看到解决问题的唯一可能方法是使用BEFORE INSERT触发器，它将检查表中的行数并删除旧行。但我很确定这是一个巨大的过热：/

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.

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

Anyone knows a better solution? :)

谁知道更好的解决方案？ :)

4 个解决方案

#1

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 ;

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

请注意，对于大型InnoDb表，COUNT操作可能会很慢。

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

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

#2

Create a table with 100,000 rows.
创建一个包含100,000行的表。
Pre-fill one of the fields with a "time-stamp" in the past.
使用过去的“时间戳”预填充其中一个字段。
Select oldest record, update "time-stamp" when "creating" (updating) record.
选择最旧的记录，在“创建”（更新）记录时更新“时间戳”。
Only use select and update - never use insert or delete.
仅使用选择和更新 - 永远不要使用插入或删除。
Reverse index on "time-stamp" field makes the select/update fast.
“时间戳”字段的反向索引使选择/更新快速。

#3

There is no way to limit the maximum number of a table rows in MySQL, unless you write a Trigger to do that.

除非你编写一个触发器来执行该操作，否则无法限制MySQL中表行的最大数量。

#4

I'm just making up an answer off the top of my head. My assumption is that you want something like a 'bucket' where you put in records, and that you want to empty it before it hits a certain record number count.

我只是在脑海中回答问题。我的假设是你想要一个类似“桶”的东西放在记录中，并且你想在它达到一定的记录数之前清空它。

After an insert statement, run SELECT LAST_INSERT_ID(); which will get you the auto increment of a record id. Yes you still have to run an extra query, but it will be low resource intensive. Once you reach a certain count, truncate the table and reset the auto increment id.

在insert语句之后，运行SELECT LAST_INSERT_ID（）;这将使您获得记录ID的自动增量。是的，你仍然需要运行一个额外的查询，但它将是低资源密集型。达到某个计数后，截断表并重置自动增量ID。

Otherwise you can't have a 'capped' table in mysql, as you would have to have pre-defined actions like (do we not allowe the record, do we truncate the table? etc).

否则你不能在mysql中有一个'capped'表，因为你必须有预定义的动作，比如（我们不允许记录，我们是否截断了表？等等）。

#1