The MariaDB Sequence Engine is a virtual table engine that generates sequences of numbers, useful for creating unique IDs or stepping through ranges.
Let’s see it in action. Imagine you need to generate a series of unique order IDs.
CREATE TABLE orders (
order_id BIGINT NOT NULL PRIMARY KEY,
order_date DATE
);
CREATE TABLE order_sequence (
id BIGINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=SEQUENCE;
Here, order_sequence is our sequence generator. It doesn’t store data itself; it’s just a mechanism to produce numbers.
Now, to populate our orders table with unique IDs:
INSERT INTO orders (order_id, order_date)
VALUES (
(SELECT id FROM order_sequence),
CURDATE()
);
Each time you SELECT id FROM order_sequence, it returns the next available number in the sequence. The AUTO_INCREMENT on the id column of the order_sequence table is what drives this generation. The engine essentially manages an internal counter.
The primary benefit of the Sequence Engine is its simplicity for generating sequential numbers without the overhead of a traditional table or relying solely on AUTO_INCREMENT on a regular table, which can have concurrency implications or require more complex management for custom sequences. It’s a dedicated tool for a specific job.
You can also configure the sequence behavior. For example, to start a sequence from a specific number and increment by a different step:
CREATE TABLE custom_sequence (
id BIGINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=SEQUENCE COMMENT='START=1000, STEP=5';
In this case, the first number generated by (SELECT id FROM custom_sequence) would be 1000, and subsequent numbers would increase by 5 (1005, 1010, etc.). The COMMENT field is how you pass these configuration options. START defines the initial value, and STEP defines the increment. The default START is 1 and the default STEP is 1.
The SEQUENCE engine is a lightweight, in-memory mechanism. It doesn’t persist its state to disk by default, which means if the server restarts, the sequence will reset to its initial state as defined by START or the default 1. This is a crucial point; if you need durable sequences, you’d typically use a regular table with AUTO_INCREMENT or a more robust ID generation strategy. However, for temporary, session-based, or non-critical sequences, its in-memory nature makes it very fast.
The sequence counter is managed per sequence table. If you have multiple SEQUENCE engine tables, they each maintain their own independent counters. The AUTO_INCREMENT property on the id column of the SEQUENCE table is internal to the engine’s operation; you don’t directly manipulate that column’s values in the same way you would with a standard table.
If you find yourself needing to generate a large number of IDs quickly or stepping through ranges for reporting or batch processing, the Sequence Engine can be a surprisingly efficient choice. It avoids the disk I/O and locking associated with INSERTs into a regular table just to get an ID.
When dealing with very high concurrency and needing guaranteed unique IDs across multiple server instances or in a distributed setup, the Sequence Engine alone might not be sufficient without additional coordination mechanisms.