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

• mysql
• stack
• queue
• fifo

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)

You can just get count of your table , and if its 40 ; just delete the first row , and insert the one you wanna insert.

get count 
if = 40
      delete
      insert
else
    insert

Well, you could certainly create an AFTER trigger on insert for the table, have it call a stored procedure that does something like:

delete from TableName where ID not in (select top N ID from TableName)

Stack does not delete previous content when insert something new.

But if you really need your structure like this, then write a before Trigger for every insert.

Was just pondering this same FIFO problem, except for handling a fixed number of COLUMNS in FIFO fashion, rather than rows. But I think my solution applies here, and a quick Google search didn't yield many FIFO solutions/examples for MySQL.

Here's my column-based scenario: I'm limited 10 available columns for my data (data1, data2, data3, etc), and if I've filled up all 10 columns and a new piece of data needs to be written, I want to over-write the OLDEST piece of data.

My solution: I added an 11th column to track which column was last updated. So when new data needs to be written, my script can look up the most recently updated column and with some simple arithmetic, back-track to determine the OLDEST column, and insert the new data into that column (over-writing anything that was already there). At the same time, I can update the 'last_updated' column with the column I just updated.

Granted, your question was specifically about rows and not columns, but I don't see why the same approach I'm using for columns couldn't work for rows...for example, you could create a column to track last row updated. You wouldn't ever be inserting new rows; just over-writing existing ones.

I wasn't planning on handling my scenario completely at the DB level, but it seems like it ought to be possible.

Lastly: this approach ensures that you NEVER have more than N rows/columns of data. Many of the trigger approaches seem to require the creation of an "extra" row, even if only very, very briefly.

FYI

https://github.com/netkiller/mysql-fifo-plugin

This is a fifo queue for mysql.

You can push some of record to fifo queue. or pull record from fifo queue.

:( but it is not an engines of table.