How can I set a maximum number of rows in MySQL table?

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.

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

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.

Tags:

Mysql

Triggers

Related