我想在MySQL表和Delete中设置最大行数

Question

what i actual want that i want to insert data in sql table but i want to limit table with 50 row no more and when i insert new data it delete the old data as new data enter for example there is 50 data already in table i add 10 new data the new data will insert and table show delete 10 rows from beginning. if new data(rows) 10 and already data in db is 45 row so it delete first 5 rows and add new 5 so i need help and suggestion how to put restriction on table and when i new data came it delete from start row some rows if data exceed 50 rows thanks in advance

Answer 1

Why? You are just making the inserts take longer.

Instead, you can insert new rows and use an auto-incrementing primary key. Then you can do something like:

select t.*
from t
order by t.id desc
fetch first 50 rows only;

This will get you the most recent 50 rows. And the query should perform quite well.

What advantages does this have?

• You get to keep all the data, which is quite useful to see what happened in the past.
• Performance is not affected.
• You can change "50" to another number on-the-fly.
• Your inserts are not slowed down by deletes.
• There is no need to deal with triggers and other complexity.

Of course, if your table is going to grow to tens of millions of rows, this might not be the optimal solution (the table itself will start to eat up memory for other purposes). But for smallish tables, this is a very viable solution.