4

Possible Duplicate:
How can I set a maximum number of rows in MySQL table?

Is it possible ( and how ) to put a limit on a MySQL table ( let's say 100'000 ) and deleting the old entries when limit reaches?

Meaning, when I have 100'000 entries and the 100'001 appears, the entry with the smallest ID is deleted and the new one is created ( with the new ID of course ).

I want MySQL to handle this on it's own, so no outside scripts need to interfere.

I need this for logging purposes, meaning, I want to keep logs only for a certain time period, let's say a week. Maybe it is possible for MySQL just to delete entries, that are older then 1 week on it's own?

Community
  • 1
  • 1
Peon
  • 7,902
  • 7
  • 59
  • 100
  • mysql can schedule things on its own, or you can use a cron job to do it for you. you could also use a trigger to intercept the inserts and start the cleanup at that time as well. – Marc B Jan 11 '13 at 15:40
  • 3
    You can use a trigger or set up a job. Related topics: [How can I set a maximum number of rows in MySQL table](http://stackoverflow.com/questions/8048001/how-can-i-set-a-maximum-number-of-rows-in-mysql-table), [What is the best way to delete old rows from MySQL on a rolling basis](http://stackoverflow.com/questions/9472167/what-is-the-best-way-to-delete-old-rows-from-mysql-on-a-rolling-basis) – Kermit Jan 11 '13 at 15:40
  • +1 @njk - this is the best method of handling. – philwinkle Jan 11 '13 at 16:07

1 Answers1

3

I propose triggers. This is the best way of insuring that at each insert the maximum table size is being taken into account.

Possible duplicate of How can I set a maximum number of rows in MySQL table?

From that accepted answer:

Try to make a restriction on adding a new record to a table. Raise an error when a new record is going to be added.

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 ;

Note, that COUNT operation may be slow on big InnoDb tables.

On MySQL 5.5 you can use SIGNAL statement to raise an error.

Community
  • 1
  • 1
philwinkle
  • 7,036
  • 3
  • 27
  • 46
  • I tried to change `CALL sth();` to `DELETE FROM one ORDER BY id ASC LIMIT 1` but it gives errors. Can it be done that way? – Peon Jan 11 '13 at 16:12