shashankaholic shashankaholic - 5 months ago 145
MySQL Question

MySQL table as a FIFO/Queue

How can we treat a Mysql table as a limited FIFO buffer (Queue).

Objectives are :


  1. The table at a time can have only N number of rows.

  2. When a row is inserted, the oldest row shpuld be deleted to maintain the row count as N.



Pls suggest approaches.

UPDATE:
Sorry guys, as many pointed I changed my question from STACK to FIFO queue

Answer

Past Mysql 5 you could use a trigger to achieve this.

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

then your triggered sql would be along the lines off:

DELETE FROM foo WHERE id NOT IN (SELECT id FROM foo ORDER BY id DESC LIMIT 10)
Comments