mysql最大行数,如何在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:

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.

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

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 ;

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

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