?
Solved

Make MySql auto delete old records in a table

Posted on 2011-02-21
6
Medium Priority
?
1,017 Views
Last Modified: 2013-12-24
Hi,

is it possible to make mysql limit the number of records in table to 100 and when a new record is inserted delete the eldest to ensure the table doesn't grow too much?

I am using Coldfusion so could run a bunch of sql queries to achieve it but was hoping there was a built in function or trigger that can handle this
0
Comment
Question by:Lmillard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 20

Expert Comment

by:Proculopsis
ID: 34942062

If you always select top 100 ordered descending by age then your result set will always be manageable.  Then from time-to-time you can perform maintenance to remove any surplus rows or you could create an insert trigger to maintain on-the-fly.
0
 

Author Comment

by:Lmillard
ID: 34942172
Hi, this is inserting records into a table that is history orientated so i would like it to be in the background as its not about retrieving the right amount of records, more keeping the size under control. I am unfamiliar with triggers so was hoping there was a setting for the table
0
 
LVL 20

Expert Comment

by:Proculopsis
ID: 34942329

//Try something like this:

CREATE TRIGGER purgeOld
BEFORE INSERT ON myTable
DELETE FROM myTable WHERE ......
0
 
LVL 82

Expert Comment

by:leakim971
ID: 34943490
A trigger will not work with MySQL : http://forums.mysql.com/read.php?99,122354,122505#msg-122505

I think you need to do that with the language used on the server side
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 34945059

After insert, or periodically (nightly batch?) you could do a delete statement like this..


delete from myTable where ID not in (
  select top 100 ID
  from myTable
  order by theDate desc)
0

Featured Post

ATEN's HDBaseT Presentation at InfoComm 2017

Hear ATEN Product Manager YT Liang review HDBaseT technology, highlighting ATEN’s latest solutions as they relate to real-world applications during her presentation at the HDBaseT booth at InfoComm 2017.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question