How to limit the total number of rows in mysql table

Question

I want the maximum rows of the table to be 100. If I add 101 rows to this table, then 1 row should be automatically deleted. Similarly, I just want the total number of rows in the table to be 100, the delete order should be FIFO.
Is there any direct MySQL function for this?

Answer 1

Use the ADD trigger to do this.

Below I kept a limit of 25, set according to your needs.

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 ; 

Answer 2

You can use a simple trigger as shown below:

 CREATE TRIGGER Deleter AFTER INSERT on YourTable FOR EACH ROW BEGIN Delete from yourTable where ID = (Select MIN(id) from yourTable); END; 

Same as: How do you create a threshold or limit a mysql table?