How to limit record entries on MySQL

[dotty]

Hi,

Does anyone know how to limit the number of records that can be inserted into a MySQL database? I want to limit it to a small number like 8 or 10.

I have a good reason for doing this, the details of which I won't bore you with!:quiet:

thanks 🙂

[garblar]

well, you could always do a count query before your insert to check if you've reached the maximum that you want

[dotty]

yes, that would be the sensible thing to do... thanks Garbler - that's what I will do!

[Sxooter]

This is probably best done with some combination of views and application logic.

Create a view something like:

create view mytable10 as select * from mytable order by timestampfield desc limit 10;

Then always select data from that view, which will limit you to the latest 10 entries. Then every so often hit the table with a delete statement like:

delete from mytable where timestampfield < (select timestampfield from mytable order by timestampfield limit 1 offset 11)

Replace the easily readable pgsql limit / offset syntax with mysql's offset,limit syntax above, btw.