Emulating Sequences in MySQL and MariaDB

Geoff MonteeMariaDB, MySQL7 Comments

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 stored function. However, this will be supported in MariaDB 10.2. (Edit: I’ve been told that new MariaDB 10.2 feature will not include support for stored functions as DEFAULT values. However, you can still use triggers, as mentioned in the comments by Frederico.)

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?

7 Comments on “Emulating Sequences in MySQL and MariaDB”

  1. Hi Geoff
    Using DEFAULT to generate the value won’t work on MariaDB 10.2, because stored functions cannot be used in that clause. I think we’ll have to use triggers

  2. Hi I have been using my Own sequence Generator, with will enable you to use multiple sequencers:

    * It is thread safe
    * Will never have problems with a heavy loaded and concurrent environment.
    * Allow you to get sequence values per Application name and Sequence Name
    * They can be used in Triggers

    You Just Need three things:

    1) A table to store the sequence metadata (last Sequence Value)
    2) The stored Procedure that will do the Job
    3) The Stored function which will use the stored procedure internally, because in mysql, you cannot insert, update or delete data inside a function.

    1) Table structure
    CREATE TABLE `__syssequences` (
    `systemName` CHAR(50) NOT NULL,
    `sequenceName` CHAR(50) NOT NULL,
    `sequenceStep` INT(11) DEFAULT ‘1’,
    `lastValue` INT(11) DEFAULT ‘1’,
    `lastInsert` DATETIME DEFAULT ‘0000-00-00 00:00:00’,
    `lastUpdate` DATETIME DEFAULT ‘0000-00-00 00:00:00’,
    PRIMARY KEY (`systemName`,`sequenceName`)
    ) ENGINE=INNODB ROW_FORMAT=FIXED

    ——————————————————————————–
    2) The stored procedure (SP):
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `__spSysGetNextSequence`$$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `__spSysGetNextSequence`(
    IN _systemName CHAR(50),
    IN _sequenceName CHAR(50),
    IN _currentValue INTEGER,
    IN _forceIncrement TINYINT(2),
    INOUT _newValue INTEGER
    )
    MODIFIES SQL DATA
    BEGIN
    INSERT INTO __syssequences VALUES (IFNULL(_systemName,”), _sequenceName, LAST_INSERT_ID( IF(_currentValue>0, _currentValue, 1) ), NOW(),NOW())
    ON DUPLICATE KEY UPDATE lastValue:=IF(LAST_INSERT_ID(0)=0,
    IF(IFNULL(_currentValue,0) > VALUE, LAST_INSERT_ID(_currentValue),
    IF((IFNULL(_currentValue,0)=0) OR (_forceIncrement=1), LAST_INSERT_ID(lastValue+(IFNULL(sequenceStep,1))), lastValue)), LAST_INSERT_ID(0)),
    lastUpdate := NOW();
    SET _newValue := IF(LAST_INSERT_ID()>IFNULL(_currentValue,0), LAST_INSERT_ID(), LAST_INSERT_ID(IF(IFNULL(_currentValue,0)>0,_currentValue,1)));
    END$$

    DELIMITER ;
    —————————————————————————

    3) The Stored function (fn)

    DELIMITER $$

    DROP FUNCTION IF EXISTS `__fnSysGetNextSequence`$$

    CREATE DEFINER=`root`@`localhost` FUNCTION `__fnSysGetNextSequence`(
    _sequenceName CHAR(50),
    _currentValue INTEGER,
    _forceIncrement TINYINT(2)
    ) RETURNS INT(11)
    READS SQL DATA
    BEGIN
    CALL `__spSysGetNextSequence`(”, _sequenceName,
    _currentValue,_forceIncrement, _currentValue);
    RETURN _currentValue;
    END$$

    DELIMITER ;
    ———————————————————————————————————————————-

    NOTES:

    I didnt have the time to document previous stuff, but with examples maybe you can get an idea of what is all about:

    – I have added ‘__’ as a suffix to the table name because it have to be one of the first tables alphabetically ordered, so when you generate a global dump file, the table data of the sequences but be the first one to be restored.

    – The ‘__’ added to the Stored Procedure and function are only for having them displayed at first in the DB administrator.

    – Warning: Althought you can this function in triggers (On Insert) , it is not recommended, because when you bulk restore the data to the tables, it can take longer than expected, because for each record it will call the function. If you used them in triggers I recommed you to:
    a) When restoring your database try to restore (create) the triggers after the data in restored
    b) Disable the usage of the function inside the trigger by having a condition based in am environment variable or something, that will only change before restoring the data.

    Usage Examples:
    ##Normal Usage
    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 0, 0); ##Returns 1 (first creation of sequence)
    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 0, 0); ##Returns 2 (Autoincrements when 3rd parameter (_currentValue) is ‘0’)
    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence02’, 0, 0); ##Returns 1 (first creation of sequence)

    select `__fnSysGetNextSequence`(‘MyApp2’, ‘MySequence01’, 0, 0); ##Returns 1 (first creation of sequence)
    select `__fnSysGetNextSequence`(‘MyApp2’, ‘MySequence01’ ,0, 0); ##Returns 2 (Autoincrements when 3rd parameter (_currentValue) is ‘0’)

    ##Forced Usage
    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 5, 0); ##Returns 5. if _currentValue is greater than last sequence value stored, it will become the greatest sequence value for now on.

    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 0, 0); ##Returns 6. Return Next Value (previous was 5)

    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 1, 0); ##Returns 1. if _currentValue is lower than last sequence value stored, it will just return the same _currentValue, without changing any stored sequence value.

    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 0, 0); ##Returns 7. Because previous Value was not altered.

  3. I forgt to add a last example:

    select `__fnSysGetNextSequence`(‘MyApp1’, ‘MySequence01’, 1, 1); ##Returns 8. Because, althought, 1 is less than 7, the last parameter ‘_forceIncrement’ is se to 1, so it will force the incremental of the sequence.

  4. There is a small errata in the function, definition, I forgot the put the ‘_systemName’ parameter:
    Here is the correct script:
    ——————————————————————————————-
    DELIMITER $$

    DROP FUNCTION IF EXISTS `__fnSysGetNextSequence`$$

    CREATE DEFINER=`root`@`localhost` FUNCTION `__fnSysGetNextSequence`(
    _systemName CHAR(50),
    _sequenceName CHAR(50),
    _currentValue INTEGER,
    _forceIncrement TINYINT(2)
    ) RETURNS INT(11)
    READS SQL DATA
    BEGIN
    CALL `__spSysGetNextSequence`(_systemName, _sequenceName,
    _currentValue,_forceIncrement, _currentValue);
    RETURN _currentValue;
    END$$

    DELIMITER ;
    ——————————————————————————————

  5. Wouldn’t it be nice if MySQL simply gave us a function: TableName.nextval() or a getNextVal(‘TableName’)
    that actually grabbed the NEXT AUTO_INCREMENT value, and updated the system_information to add one, so the next time you call it, it is the next value.

    That would allow me to call it 20 times in a row, and allocate 20 values, as they came through. Then others, using the normal Auto_Increment feature would work just fine.

    It seems like such a SIMPLE implementation concept. It’s not like they dont update that field internally when we do an insert.

    1. Hi Kirk,

      Thanks for reading my blog post! Your suggestion sounds interesting. If you want something like that in MariaDB, feel free to submit a feature request on their JIRA:

      https://jira.mariadb.org

      However, please keep in mind that MariaDB 10.3 will have sequences as defined by the SQL standard, so those may be more useful in future MariaDB versions than new functions built around auto-increment values.

Leave a Reply

Your email address will not be published. Required fields are marked *

2,971 Spambots Blocked by Simple Comments