Several articles have been written over the years about how to implement circular tables in MySQL. The idea is that sometimes we want new rows to overwrite the oldest ones. The general advice is to do it with partitions, and it works. But since MariaDB 10.3, we can use a cleaner method to do this in a precise way.
In this article, we will see how to create a circular table always consisting of an exact number of rows. To do so we will use sequences and the
CHECK clause, implemented respectively in MariaDB 10.3 and 10.2. Currently MySQL doesn’t support sequences.
The table and the sequence
Suppose we want our table to have exactly five rows. We will start to count them from zero.
CREATE OR REPLACE SEQUENCE q0 MINVALUE 0 MAXVALUE 4 START WITH 0 CYCLE COMMENT 'Index for capped_table' ; CREATE OR REPLACE TABLE capped_table ( id TINYINT UNSIGNED NOT NULL DEFAULT NEXT VALUE FOR q0, label VARCHAR(20) NOT NULL, PRIMARY KEY (id), CHECK (id < 5) ) ENGINE InnoDB, COMMENT 'This table should never have more than 5 rows' ;
The sequence just acts as a counter the next row that will be inserted. The key here is that it must be a circular sequence (
CYCLE), which is not the default. After generating value 4, the sequence will restart and generate value 0. Non-circular sequences return an error instead.
The table (MongoDB users might easily guess where its name comes from) has an id column, whose default value is the next value of the sequence. But this does not prevent a user to manually insert a new row with an arbitrary id; so we use a
CHECK constraint to prevent that an out of range value is inserted.
No idea is a good idea until a test succeeds. So, let's try. Remember that this table is circular: we want old rows to be overwritten, so we must use
REPLACE instead of
REPLACE INTO capped_table (label) VALUE ('William Hartnell') , ('Patrick Troughton') , ('Jon Pertwee') , ('Tom Baker') ; MariaDB [test]> SELECT * FROM capped_table; +----+-------------------+ | id | label | +----+-------------------+ | 0 | William Hartnell | | 1 | Patrick Troughton | | 2 | Jon Pertwee | | 3 | Tom Baker | +----+-------------------+
So far so good, but we didn't reach the table limit yet. Let's add some more rows, and let's see if old rows are overwritten. Let's use letters this time, so it will be easy to distinguish new values from the old ones.
REPLACE INTO capped_table (label) VALUE ('A') , ('B') , ('C') ; MariaDB [test]> SELECT * FROM capped_table; +----+-------------+ | id | label | +----+-------------+ | 0 | B | | 1 | C | | 2 | Jon Pertwee | | 3 | Tom Baker | | 4 | A | +----+-------------+
Brilliant! The oldest two rows were overwritten by the second and third rows we just inserted.
The id doesn't represent a chronological order. Or better said, it does if we know which id is the newest one. But we don't, because
SELECT PREVIOUS VALUE FOR q0 will return the last number generated by the current session - not the global one.
To workaround this and be able to read rows in a chronological order, we can add a
Forbidding UPDATEs and DELETEs
If our circular table is meant to be a queue, the only possible write operation must be append. Thus, it is desirable to do forbid
DELETEs. We can do that by using triggers - this is my favourite solution because it works for all users, including root. Further details on this will be in a future article.