MySQL table as a FIFO/Queue

Go To


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

2012-04-04 19:12
by shashankaholic
A stack generally doesn't delete things. A limited FIFO buffer might, though. What kind of data structure are you trying to represent - tadman 2012-04-04 19:14
You could do something like this with a stored procedure or (I think) trigger - Brian Hoover 2012-04-04 19:14
@tadman Sorry, I wrongly put up my question. Definitely, i m looking for limited FIFO or Queue. Correct me - shashankaholic 2012-04-04 19:22


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

then your triggered sql would be along the lines off:

2012-04-04 19:17
by Ricky Baby
Thanks, I guess this sql on BEFORE INSERT will do the job - shashankaholic 2012-04-04 19:40
I get this error using the query /* SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' */ any thoughts? MySQL 5.5.16bman 2013-05-26 16:52
I also tried a BEFORE INSERT I get this error SQL Error (1442): Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/triggerbman 2013-05-27 04:58
You can not do insert/update/delete on the same table where the trigger is executing - user1658296 2014-12-09 11:23


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
2012-04-04 19:16
by kommradHomer
yes you are right, but i am looking for a more dynamic approach, where this logic can be handled at DB - shashankaholic 2012-04-04 19:28
oh. triggers are your shot then yea - kommradHomer 2012-04-04 20:08


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)
2012-04-04 19:18
by itsmatt
AFTER trigger or BEFORE - shashankaholic 2012-04-04 19:27
If you do a BEFORE trigger, it would have N+1 after the insert, wouldn't it? I believe you'd want an AFTER trigger - itsmatt 2012-04-04 19:31
but if there are already N rows,AFTER insert would make it to N+1 - shashankaholic 2012-04-04 19:38
If it is critical that you never have more than N rows in the table, then change the select to "select top N-1 from TableName) and do a BEFORE trigger then. I would argue, however that since there's a trigger on this that is going to get executed immediately AFTER the insert, it really doesn't matter - unless this is a homework assignment. As written, with an AFTER trigger something like what I wrote will result in only N rows in the table when the stored proc is completed - itsmatt 2012-04-04 19:43
I agree, it really doesn't matter coz end result is going same. Its either BEFORE and top N-1 or AFTER and top N. However, if BEFORE is making sure count don't go beyond N, then thats perfect - shashankaholic 2012-04-04 19:53


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.

2012-04-04 19:19
by Ankit Sharma


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.

2013-05-17 03:55
by mnyounger



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.

2013-12-18 07:19
by netkiller