Sequences for MariaDB Enterprise Server

Overview

For best performance, every InnoDB table should have a primary key column. If your table does not have a column or a set of columns that could act as a natural primary key, then you can use a sequence to generate an integer value to use as the table's primary key. Sequences were first added in MariaDB Enterprise Server 10.3 and MariaDB Community Server 10.3.

Note

Sequences can be used instead of AUTO_INCREMENT columns in most cases. Sequences are compliant with the SQL standard, while AUTO_INCREMENT columns are not, so sequences are the better option for applications that require standard-compliant features.

Compatibility

  • MariaDB Enterprise Server 10.3

  • MariaDB Enterprise Server 10.4

  • MariaDB Enterprise Server 10.5

  • MariaDB Enterprise Server 10.6

Creating a Sequence

A sequence can be created using the CREATE SEQUENCE statement.

Internally, a sequence is represented by a table with a single row that stores the internal state of the sequence. The sequence can use any storage engine for the table, and the sequence does not have to use the same storage engine as the tables that use the sequence.

InnoDB uses special performance optimizations for internal sequence tables. The internal sequence tables are crash-safe, but InnoDB does not use its typical transactional locking on the table, and InnoDB does not write row versions to the Undo Log.

Creating a Sequence with Default Arguments

To create a sequence with default arguments, you can execute the CREATE SEQUENCE statement while only providing a name for the sequence.

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. If the database does not exist, then create the database for the sequence using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;

  3. Create a sequence with the CREATE SEQUENCE statement:

    CREATE SEQUENCE hq_sales.invoice_seq;

Creating a Sequence with Custom Arguments

To create a sequence with custom arguments, you can execute the CREATE SEQUENCE statement while providing some of those custom arguments.

  1. Choose the arguments that you would like to set:

    Argument

    Description

    INCREMENT = n

    • Sets the value that is added to the last sequence value to generate the next sequence value.

    • Can be negative.

    • If set to 0, then the value of the auto_increment_increment system variable is used.

    MINVALUE = n

    • Sets the minimum value of the sequence.

    • If INCREMENT is positive, then MINVALUE defaults to 1.

    • If INCREMENT is negative, then MINVALUE defaults to -9223372036854775807.

    • MINVALUE can be greater than or equal to -9223372036854775807.

    • MINVALUE must be less than MAXVALUE.

    • MINVALUE must be less than or equal to START.

    MAXVALUE = n

    • Sets the maximum value of the sequence.

    • If INCREMENT is positive, then MAXVALUE defaults to 9223372036854775806.

    • If INCREMENT is negative, then MAXVALUE defaults to -1.

    • MAXVALUE can be less than or equal to 9223372036854775806.

    • MAXVALUE must be greater than MINVALUE.

    • MAXVALUE must be greater than or equal to START.

    START = n

    • Sets the starting value of the sequence.

    • If INCREMENT is positive, then START defaults to MINVALUE.

    • If INCREMENT is negative, then START defaults to MAXVALUE.

    • START must be greater than or equal to MINVALUE.

    • START must be less than or equal to MAXVALUE.

    CACHE = n

    • Sets the number of values that should be cached.

    • When a sequence has cached values available, queries can use the cached values, which means they do not have to read from or write to the sequence.

    • Caching values can improve performance.

    • When there are no more cached values, the sequence generates more cached values.

    • If set to 0, then there is no cache.

    CYCLE / NOCYCLE

    • If CYCLE is set, then the sequence restarts at MINVALUE when MAXVALUE is reached.

    • If NOCYCLE is set, then the sequence can no longer return new values when MAXVALUE is reached.

    • NOCYCLE is the default.

  2. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  3. If the database does not exist, then create the database for the sequence using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;

  4. Create a sequence with the CREATE SEQUENCE statement:

    CREATE SEQUENCE hq_sales.invoice_seq
   INCREMENT = 2
   MINVALUE = 1
   MAXVALUE = 9223372036854775805
   START = 1
   CACHE = 1000
   CYCLE;

Choosing a Data Type for a Sequence-backed Column

When designing a schema, columns backed by sequences should use integer data types. The following types can be used:

Data Type

Signed Range

Unsigned Range

TINYINT

-128 - 127

0 - 255

SMALLINT

-32768 - 32767

0 - 65535

MEDIUMINT

-8388608 - 8388607

0 - 16777215

INT

-2147483648 - 2147483647

0 - 4294967295

BIGINT

-9223372036854775808 - 9223372036854775807

0 - 18446744073709551615

To determine which type to use, consider the following points:

  1. What is MINVALUE for the sequence?

    The chosen data type needs to be able to fit the minimum value for the sequence:

    Data Type

    Minimum Sequence MINVALUE (Signed)

    Minimum Sequence MINVALUE (Unsigned)

    TINYINT

    -128

    0

    SMALLINT

    -32768

    0

    MEDIUMINT

    -8388608

    0

    INT

    -2147483648

    0

    BIGINT

    -9223372036854775807

    0

  2. What is MAXVALUE for the sequence?

    The chosen data type needs to be able to fit the maximum value for the sequence:

    Data Type

    Maximum Sequence MAXVALUE (Signed)

    Maximum Sequence MAXVALUE (Unsigned)

    TINYINT

    127

    255

    SMALLINT

    32767

    65535

    MEDIUMINT

    8388607

    16777215

    INT

    2147483647

    4294967295

    BIGINT

    9223372036854775806

    9223372036854775806

  3. How large will your table grow?

    If your sequence-backed column is being used as the table's primary key, then the maximum number of values generated by the sequence should be considered the maximum number of rows that can fit in the table.

    Let's say that we make the following assumptions:

    • The sequence's INCREMENT is 1.

    • The sequence's MINVALUE is equal to the the data type's minimum sequence MINVALUE.

    • The sequence's MAXVALUE is equal to the data type's maximum sequence MAXVALUE.

    • The sequence's START is equal to the sequence's MINVALUE.

    If these assumptions are correct, then we can calculate the maximum number of values for a sequence-backed column of each data type with by doing MAXVALUE - MINVALUE + 1:

    Data Type

    Maximum Number of Rows (Signed)

    Maximum Number of Rows (Unsigned)

    TINYINT

    256

    256

    SMALLINT

    65536

    65536

    MEDIUMINT

    1677216

    1677216

    INT

    4294967296

    4294967296

    BIGINT

    18446744073709551616

    9223372036854775807

    If you want to give your table the most room to grow, then it would be best to choose BIGINT SIGNED. This is quite different from AUTO_INCREMENT columns, in which BIGINT UNSIGNED provides the most room to grow. The reason for this is that a sequence's MAXVALUE must be less than or equal to 9223372036854775806, which means that a sequence can't utilize the full range of values for BIGINT UNSIGNED.

Creating a Sequence with the Biggest Range

To allow the table to sequence to utilize the whole BIGINT SIGNED range, you can execute the CREATE SEQUENCE statement while providing the relevant arguments.

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. If the database does not exist, then create the database for the sequence using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;

  3. Create a sequence with the CREATE SEQUENCE statement:

    CREATE SEQUENCE hq_sales.invoice_seq
   INCREMENT = 1
   MINVALUE = -9223372036854775807
   MAXVALUE = 9223372036854775806
   START = -9223372036854775807
   CACHE = 1000
   NOCYCLE;

Creating an InnoDB Table with a Sequence-backed Column

Let's create an InnoDB table with a sequence-backed column after confirming that the default storage engine is InnoDB:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

    SHOW SESSION VARIABLES
   LIKE 'default_storage_engine';

    +------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

  3. If the database does not exist, then create the database for the sequence and table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;

  4. Create a sequence with the CREATE SEQUENCE statement:

    CREATE SEQUENCE hq_sales.invoice_seq;

  5. Create the table using the CREATE TABLE statement:

    CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT NOT NULL DEFAULT (NEXT VALUE FOR hq_sales.invoice_seq),
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
);

    • Values for the invoice_id column will be fetched from the sequence by default.

    • The DEFAULT clause for the column is used to configure the column's default value.

    • The NEXT VALUE FOR clause is used to fetch a value from the sequence.

Inserting a Row with a Sequence-backed Column

If a column is configured to get its value from a sequence via the DEFAULT clause, then its value will be automatically generated by the sequence. There are multiple ways to insert rows with these automatically generated values.

Omitting the Column

If the column is not specified, then the sequence will automatically generate the value.

Let's insert a row into the table created in the Creating an InnoDB Table with a Sequence-backed Column section:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. Insert a row with the INSERT statement, but do not specify the sequence-backed column:

    INSERT INTO hq_sales.invoices
   (branch_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (1, 1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');

  3. Select the same row with the SELECT statement to confirm that a value was automatically generated:

    SELECT invoice_id
FROM hq_sales.invoices
WHERE branch_id = 1
AND customer_id = 1
AND invoice_date = '2020-05-10 12:35:10';

    +------------+
| invoice_id |
+------------+
|          1 |
+------------+

Specifying the Sequence's Next Value

If the column's value is specified as the sequence's next value with either the NEXT VALUE FOR clause or the NEXTVAL() function, then the sequence will automatically generate the value. This is most useful in cases where the column's DEFAULT clause is not properly set.

Let's insert a row into the table created in the Creating an InnoDB Table with a Sequence-backed Column section:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. Insert a row with the INSERT statement, and specify the sequence's next value with either the NEXT VALUE FOR clause or the NEXTVAL() function:

    INSERT INTO hq_sales.invoices
   (invoice_id, branch_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (NEXT VALUE FOR hq_sales.invoice_seq, 1, 2, '2020-05-10 14:17:32', 1508.57, 'WIRE_TRANSFER');

  3. Select the same row with the SELECT statement to confirm that a value was automatically generated:

    SELECT invoice_id
FROM hq_sales.invoices
WHERE branch_id = 1
AND customer_id = 2
AND invoice_date = '2020-05-10 14:17:32';

    +------------+
| invoice_id |
+------------+
|          2 |
+------------+

Getting the Last Inserted Sequence Value

After InnoDB inserts an automatically generated value into a sequence-backed column, the application sometimes needs to know what value it inserted. For example, the application may need to use the value to insert a foreign key column in a dependent table. Either the PREVIOUS VALUE FOR clause or the LASTVAL() function can be used to get the lasted inserted value for a sequence-backed column without re-reading the row from the table.

Let's insert a row into the table created in the Creating an InnoDB Table with a Sequence-backed Column section and then use either the PREVIOUS VALUE FOR clause or the LASTVAL() function:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. Insert a row with the INSERT statement, but do not specify the sequence-backed column:

    INSERT INTO hq_sales.invoices
   (branch_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (1, 4, '2020-05-10 12:37:22', 104.19, 'CREDIT_CARD');

  3. Execute a SELECT statement with either the PREVIOUS VALUE FOR clause or the LASTVAL() function to get the value for the new row:

    SELECT PREVIOUS VALUE
   FOR hq_sales.invoice_seq;

    +-----------------------------------------+
| PREVIOUS VALUE FOR hq_sales.invoice_seq |
+-----------------------------------------+
|                                       3 |
+-----------------------------------------+

  4. Select the same row with the SELECT statement to confirm that the sequence-backed column has the same value:

    SELECT invoice_id
FROM hq_sales.invoices
WHERE branch_id = 1
AND customer_id = 4
AND invoice_date = '2020-05-10 12:37:22';

    +------------+
| invoice_id |
+------------+
|          3 |
+------------+

Setting a Sequence's Next Value

A sequence's next value can be set with either the ALTER SEQUENCE statement or the SETVAL() function.

Let's set the next value for the sequenced used by the table created in the Creating an InnoDB Table with a Sequence-backed Column section and then insert a row into the table, so we can confirm that it uses the new value:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root

  2. Set the sequence's next value with either the ALTER SEQUENCE statement or the SETVAL() function:

    ALTER SEQUENCE hq_sales.invoice_seq
   RESTART = 100;

  3. Insert a row with the INSERT statement, but do not specify the sequence-backed column:

    INSERT INTO hq_sales.invoices
   (branch_id, customer_id, invoice_date, invoice_total, payment_method)
VALUES
   (1, 5, '2020-05-10 12:43:19', 1105.98, 'CREDIT_CARD');

  4. Execute a SELECT statement with either the PREVIOUS VALUE FOR clause or the LASTVAL() function to get the value for the new row:

    SELECT PREVIOUS VALUE
   FOR hq_sales.invoice_seq;

    +-----------------------------------------+
| PREVIOUS VALUE FOR hq_sales.invoice_seq |
+-----------------------------------------+
|                                     100 |
+-----------------------------------------+

  5. Select the same row with the SELECT statement to confirm that the sequence-backed column has the same value:

    SELECT invoice_id
FROM hq_sales.invoices
WHERE branch_id = 1
AND customer_id = 5
AND invoice_date = '2020-05-10 12:43:19';

    +------------+
| invoice_id |
+------------+
|        100 |
+------------+