Emulating Sequences in MySQL and MariaDB

Datetime:2016-08-23 02:21:42          Topic:          Share

Sequences are objects defined by the SQL standard that are used to create monotonically increasing sequences of numeric values. Whenever nextval is called on a sequence object, it generates and returns the next number in the sequence. For MySQL and MariaDB users, this might sound similar to MySQL’s AUTO_INCREMENT columns , but there are some differences:

  • Sequences are defined by the SQL Standard. AUTO_INCREMENT columns are not in the standard, but are a MySQL extension.
  • Sequences are their own objects with their own state, which means that multiple columns in multiple tables could all use numbers from the same sequence. In contrast, MySQL’s AUTO_INCREMENT feature is tied to a specific column in a specific table, so multiple columns in multiple tables cannot directly use the same AUTO_INCREMENT pool.

MySQL and MariaDB do not yet support SQL Standard sequences. If you would like MariaDB to support sequences, you may want to consider voting for this feature request .

Users who have migrated to MySQL or MariaDB from other databases might find this feature to be a strange omission, considering that many other databases do support sequences, including:

Despite the fact that MySQL and MariaDB don’t yet support sequences, it is fairly easy to emulate SQL standard sequences in MySQL and MariaDB using an AUTO_INCREMENT column and functions. In this blog post, I’ll describe how to do that using MariaDB 10.1.

Emulating sequences in MariaDB

The first step needed to create our emulated sequence is to create a table that keeps track of the sequence values:

CREATE TABLE sequence_values (

	id INT AUTO_INCREMENT PRIMARY KEY,

	thread_id INT NOT NULL,

	created DATETIME DEFAULT CURRENT_TIMESTAMP

);

The second step is to create a function that generates and returns the next value in the sequence:

DELIMITER //

CREATE FUNCTION `sequence_nextval`()

RETURNS INT

NOT DETERMINISTIC

MODIFIES SQL DATA

BEGIN

DECLARE nextval int;

INSERT INTO sequence_values (thread_id) VALUES (CONNECTION_ID());

SELECT last_insert_id() INTO nextval;

RETURN nextval;

END//

DELIMITER ;

Finally, let’s create a table that we want to use the sequence with:

CREATE TABLE sequence_test_a (

	seq int NOT NULL PRIMARY KEY,

	str varchar(50)

);

For users who are used to databases with real standard sequence support, it might be tempting to define the table in the following way instead:

CREATE TABLE sequence_test_a (

	seq int NOT NULL PRIMARY KEY DEFAULT sequence_nextval(),

	str varchar(50)

);

Unfortunately, MariaDB 10.1 does not support setting a DEFAULT value to a function. However, this will be supported in MariaDB 10.2 .

One of the benefits of sequences is that they can be used across multiple tables, so let’s create a second table that will use the sequence as well:

CREATE TABLE sequence_test_b (

	seq int NOT NULL PRIMARY KEY,

	str varchar(50)

);

Now let’s insert some data into the tables:

MariaDB [db1]> INSERT INTO sequence_test_a VALUES (sequence_nextval(), 'a_str1');

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> INSERT INTO sequence_test_a VALUES (sequence_nextval(), 'a_str2');

Query OK, 1 row affected (0.01 sec)

MariaDB [db1]> INSERT INTO sequence_test_b VALUES (sequence_nextval(), 'b_str1');

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> INSERT INTO sequence_test_b VALUES (sequence_nextval(), 'b_str2');

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> INSERT INTO sequence_test_a VALUES (sequence_nextval(), 'a_str3');

Query OK, 1 row affected (0.00 sec)

What are the contents of these tables now?

MariaDB [db1]> SELECT * FROM sequence_test_a;

+-----+--------+

| seq | str    |

+-----+--------+

|   1 | a_str1 |

|   2 | a_str2 |

|   5 | a_str3 |

+-----+--------+

3 rows in set (0.00 sec)

MariaDB [db1]> SELECT * FROM sequence_test_b;

+-----+--------+

| seq | str |

+-----+--------+

| 3 | b_str1 |

| 4 | b_str2 |

+-----+--------+

2 rows in set (0.00 sec)

As you can see from the above output, the seq column in each table was populated with monotonically increasing values in the order in which the rows were inserted, so our sequence appears to be working properly.

I should also note that the sequence_values table will grow over time:

MariaDB [db1]> SELECT * FROM sequence_values;

+----+-----------+---------------------+

| id | thread_id | created             |

+----+-----------+---------------------+

|  1 |         3 | 2016-08-18 14:09:49 |

|  2 |         3 | 2016-08-18 14:09:50 |

|  3 |         3 | 2016-08-18 14:09:58 |

|  4 |         3 | 2016-08-18 14:10:22 |

|  5 |         3 | 2016-08-18 14:10:23 |

+----+-----------+---------------------+

5 rows in set (0.00 sec)

If you do not need to keep track of when a sequence was generated, you could create an event or cron job to periodically prune old events.

Has anyone else created their own sequence implementation in MySQL or MariaDB?