如何设置 MySQL 表中的最大行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8048001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I set a maximum number of rows in MySQL table?
提问by WASD42
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_ROWSproperty 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 行并删除其他行”）而是对数据库引擎的提示，即该表将至少有 100 000 行。
The only possible way I see to solve the problem is to use BEFORE INSERTtrigger 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? :)
有人知道更好的解决方案吗？:)
采纳答案by Devart
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.
请注意， COUNT 操作在大型 InnoDb 表上可能会很慢。
On MySQL 5.5 you can use SIGNAL// RESIGNALstatement to raise an error.
回答by joe
- Create a table with 100,000 rows.
- 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.
- 创建一个包含 100,000 行的表。
- 用过去的“时间戳”预填充其中一个字段。
- 选择最旧的记录，在“创建”（更新）记录时更新“时间戳”。
- 只使用选择和更新 - 永远不要使用插入或删除。
- “时间戳”字段上的反向索引使选择/更新快速。
回答by aleroot
There is no way to limit the maximum number of a table rows in MySQL, unless you write a Trigger to do that.
没有办法限制 MySQL 中表行的最大数量，除非你编写一个触发器来做到这一点。
回答by Jakub
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 tableand reset the auto increment id.
在插入语句之后，运行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 中有一个“封顶”表，因为你必须有预定义的操作（我们不允许记录，我们是否截断表？等）。
