Sequences Support in MariaDB Server 10.3

In the SQL standard, SQL:2003 sequences are defined. The idea of sequences is to have a way of requesting unique values on demand. The typical use case for sequences is to have a unique identifier that can be used on multiple tables. In addition, it might be useful in some cases to have an identifier before an actual row is inserted. With the normal way of having an automatically incrementing identifier, the identifier value will only be available after insert of the row, and the identifier will only be unique inside its own table. The implementation of sequences in MariaDB Server 10.3 follows the standard and includes compatibility with the way Oracle does sequences introduced in Oracle Database Server on top of the standard.

To create a sequence, a create statement is used:

CREATE SEQUENCE Seq1_1  
  START WITH 1  
  INCREMENT BY 1;

This creates a sequence that starts at 1 and is incremented with 1 every time a value is requested from the sequence. In this example, both START WITH and INCREMENT BY could have been left out since their default values are 1. The sequence will be visible among the tables in the database, i.e. if you run SHOW TABLES, it will be there. You can use DESCRIBE on the sequence to see what columns it has.

To test out the usage of sequences, let’s create a table.

CREATE TABLE Observation (
  Id int(11) NOT NULL,
  Place varchar(50) NOT NULL,
  BirdId int(11) NOT NULL,
  PRIMARY KEY (Id)
)

Since I want to use sequences this time, I did not put AUTO_INCREMENT on the ID column. Instead, I’ll ask for the next value from the sequence in the INSERT statements:

INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Helsinki', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Espoo', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Kirkkonummi', 10);
INSERT INTO Observation (Id, Place, BirdId) VALUES (NEXT VALUE FOR Seq1_1, 'Hanko', 10);

"A bird flying west from Helsinki and being observed in cities along the way." In the INSERT statements, there is a call to the sequence: NEXT VALUE FOR Seq1_1. It will retrieve the next value from the sequence. Instead of having the NEXT VALUE FOR in each INSERT statement, it could have been the default value of the column in this way:

ALTER TABLE Observation MODIFY Id int(11) NOT NULL DEFAULT NEXT VALUE FOR Seq1_1;

Running a SELECT over the Observation table will look like this:

SELECT * FROM Observation;
+----+-------------+--------+
| Id | Place       | BirdId |
+----+-------------+--------+
|  1 | Helsinki    |     10 |
|  2 | Espoo       |     10 |
|  3 | Kirkkonummi |     10 |
|  4 | Hanko       |     10 |
+----+-------------+--------+
4 rows in set (0.001 sec)

As we can see, the ID column has been populated with numbers that start from 1 and are incremented with 1 as defined in the sequence’s CREATE statement. To get the last retrieved number from the sequence, PREVIOUS VALUE is used:

SELECT PREVIOUS VALUE FOR Seq1_1;
+---------------------------+
| PREVIOUS VALUE FOR Seq1_1 |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set (0.001 sec)

Another useful option for sequences is CYCLE, which means that we start from the beginning after reaching a certain value. For example, if there are 4 phases in a process that are done sequentially and then start from the beginning, we could create a sequence to always be able to retrieve the number of the next phase.

CREATE SEQUENCE Seq1_1_c4  
  START WITH 1  
  INCREMENT BY 1
  MAXVALUE = 4
  CYCLE;

The sequence starts at 1 and is incremented with 1 every time the next value is requested. But when it reaches 4 (MAXVALUE), it will restart from 1 (CYCLE).

It’s also possible to set the next value of a sequence, to ALTER a sequence, or use sequences in Oracle mode with Oracle specific syntax. To switch to Oracle mode use:

SET SQL_MODE=ORACLE;

After that, you can retrieve the next value of a sequence in Oracle style:

SELECT Seq1_1.nextval;

Read more about sequences in the MariaDB documentation. Get MariaDB Server 10.3 as part of the MariaDB TX 3.0 download — now available.