Fixed number of rows in MySql

How to set the maximum number of SQL records to 10 and automatically delete the last record if a new one is开发者_StackOverflow社区 added.

---

There is no such feature in MySQL. You have to program it yourself, either in your application, or in the database by writing a trigger or stored procedure.

Count the current number of rows (SELECT COUNT(*) FROM table), if it's greater than 10, DELETE FROM table ORDER BY somecol LIMIT 1 to remove one.

---

You can delete all records except last ten inserted with a single query that you run when you want

delete from table where id < (select * from (select id from table order by id desc limit 9,1) as tab)

---

Add a date/time field with the insert date and time. Then run a trigger on insert to delete the top 1 rows and sort by date descending.

---

You can just keep all the records, and only return the 10 most recent. You then write a cleanup procedure that periodically removes the older ones.

SELECT * FROM tbl ORDER BY created_at DESC LIMIT 10