Description\n-----------\n\nThis page is about sequence objects. For details about the storage engine, see Sequence Storage Engine.\n\nIntroduction\n\nA sequence is an object that generates a sequence of numeric values, as specified by the CREATE SEQUENCE statement.\n\nCREATE SEQUENCE creates a sequence that generates new values when called with NEXT VALUE FOR sequence_name. It's an alternative to AUTO INCREMENT when one wants to have more control of how the numbers are generated. As the SEQUENCE caches values (up to the CACHE value in the CREATE SEQUENCE statement, by default 1000) it can in some cases be much faster than AUTO INCREMENT. Another benefit is that one can access the last value generated by all used sequences, which solves one of the limitations with LAST_INSERT_ID().\n\nSequences should not be used with statement-based logging; see this section for details.\n\nCreating a Sequence\n\nThe CREATE SEQUENCE statement is used to create a sequence. Here is an example of a sequence starting at 100, incrementing by 10 each time:\n\n``sql\nCREATE SEQUENCE s START WITH 100 INCREMENT BY 10;\n`\n\nThe CREATE SEQUENCE statement, along with defaults, can be viewd with the SHOW CREATE SEQUENCE STATEMENT, for example:\n\n`sql\nSHOW CREATE SEQUENCE s\\G\n************************ 1. row ***********************\n       Table: s\nCreate Table: CREATE SEQUENCE s start with 100 minvalue 1 maxvalue 9223372036854775806 \n  increment by 10 cache 1000 nocycle ENGINE=InnoDB\n`\n\nUsing Sequence Objects\n\nTo get the next value from a sequence, use\n\n`sql\nNEXT VALUE FOR sequence_name\n`\n\nor\n\n`sql\nNEXTVAL(sequence_name)\n`\n\nor in Oracle mode (SQL_MODE=ORACLE)\n\n`\nsequence_name.nextval\n`\n\nFor retrieving the last value used by the current connection from a sequence use:\n\n`sql\nPREVIOUS VALUE FOR sequence_name\n`\n\nor\n\n`sql\nLASTVAL(sequence_name)\n`\n\nor in Oracle mode (SQL_MODE=ORACLE)\n\n`\nsequence_name.currval\n`\n\nFor example:\n\n`sql\nSELECT NEXTVAL(s);\n+------------+\n| NEXTVAL(s) |\n+------------+\n|        100 |\n+------------+\n\nSELECT NEXTVAL(s);\n+------------+\n| NEXTVAL(s) |\n+------------+\n|        110 |\n+------------+\n\nSELECT LASTVAL(s);\n+------------+\n| LASTVAL(s) |\n+------------+\n|        110 |\n+------------+\n`\n\nUsing Sequences in DEFAULT\n\nSequences can be used in DEFAULT:\n\n`sql\nCREATE SEQUENCE s1;\nCREATE TABLE t1 (a INT PRIMARY KEY DEFAULT (NEXT VALUE FOR s1), b INT);\nINSERT INTO t1 (b) VALUES (1),(2);\nSELECT  FROM t1;\n+---+------+\n| a | b    |\n+---+------+\n| 1 |    1 |\n| 2 |    2 |\n+---+------+\n`\n\nChanging a Sequence\n\nThe ALTER SEQUENCE statement is used for changing sequences. For example, to restart the sequence at another value:\n\n`sql\nALTER SEQUENCE s RESTART 50;\n\nSELECT NEXTVAL(s);\n+------------+\n| NEXTVAL(s) |\n+------------+\n|         50 |\n+------------+\n`\n\nThe SETVAL function can also be used to set the next value to be returned for a SEQUENCE, for example:\n\n`sql\nSELECT SETVAL(s, 100);\n+----------------+\n| SETVAL(s, 100) |\n+----------------+\n|            100 |\n+----------------+\n`\n\nSETVAL can only be used to increase the sequence value. Attempting to set a lower value will fail, returning NULL:\n\n`sql\nSELECT SETVAL(s, 50);\n+---------------+\n| SETVAL(s, 50) |\n+---------------+\n|          NULL |\n+---------------+\n`\n\nDropping a Sequence\n\nThe DROP SEQUENCE statement is used to drop a sequence, for example:\n\n`sql\nDROP SEQUENCE s;\n`\n\nReplication\n\nIf you want to use Sequences in a master-master setup or with Galera, you should use INCREMENT=0. This tells the Sequence to use auto_increment_increment and auto_increment_offset to generate unique values for each server.\n\nStatement-Based Replication Limitation\n\nUsing SELECT NEXT VALUE causes an error for statement-based logging:\n\n`\nERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.\n`\n\nThis happens because SELECT modifies objects.\n\nStandards Compliance\n\nMariaDB supports both ANSI SQL and Oracle syntax for sequences.\n\nHowever as SEQUENCE is implemented as a special kind of table, it uses the same namespace as tables. The benefits are that sequences show up in SHOW TABLES, and one can also create a sequence with CREATE TABLE and drop it with DROP TABLE. One can SELECT from it as from any other table. This ensures that all old tools that work with tables should work with sequences.\n\nSince sequence objects act as regular tables in many contexts, they will be affected by LOCK TABLES. This is not the case in other DBMS, such as Oracle, where LOCK TABLE does not affect sequences.\n\nNotes\n\nOne of the goals with the Sequence implementation is that all old tools, such as mariadb-dump (previously mysqldump), should work unchanged, while still keeping the normal usage of sequence standard compatibly.\n\nTo make this possible, sequence is currently implemented as a table with a few exclusive properties.\n\nThe special properties for sequence tables are:\n\n A sequence table has always one row.\n When one creates a sequence, either with CREATE TABLE or CREATE SEQUENCE, one row will be inserted.\n If one tries to insert into a sequence table, the single row will be updated. This allows mariadb-dump to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications should of course also use ALTER SEQUENCE.\n UPDATE or DELETE can't be performed on Sequence objects.\n Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The next_value column shows the next value not reserved by the cache.\n FLUSH TABLES will close the sequence and the next sequence number generated will be according to what's stored in the Sequence object. In effect, this will discard the cached values.\n A number of normal table operations work on Sequence tables. See next section.\n\nTable Operations that Work with Sequences\n\n SHOW CREATE TABLE sequence_name. This shows the table structure that is behind the SEQUENCE including the field names that can be used with SELECT or even CREATE TABLE.\n CREATE TABLE sequence-structure ... SEQUENCE=1\n ALTER TABLE sequence RENAME TO sequence2\n RENAME TABLE sequence_name TO new_sequence_name\n DROP TABLE sequence_name. This is allowed mainly to get old tools like mariadb-dump to work with sequence tables.\n SHOW TABLES\n\nImplementation\n\nInternally, sequence tables are created as a normal table without rollback (the InnoDB, Aria and MySAM engines support this), wrapped by a sequence engine object. This allowed us to create sequences with\\\nalmost no performance impact for normal tables. (The cost is one 'if' per insert if the binary log is enabled).\n\nUnderlying Table Structure\n\nThe following example shows the table structure of sequences and how it can be used as a table.\\\n(Output of results are slightly edited to make them easier to read.)\n\n`sql\nCREATE SEQUENCE t1;\nSHOW CREATE SEQUENCE t1\\G\n*********************** 1. row *********************\n  CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 9223372036854775806\n  increment by 1 cache 1000 nocycle ENGINE=InnoDB\n\nSHOW CREATE TABLE t1\\G\n********************* 1. row ***********************\nCreate Table: CREATE TABLE t1 (\n  next_not_cached_value bigint(21) NOT NULL,\n  minimum_value bigint(21) NOT NULL,\n  maximum_value bigint(21) NOT NULL,\n  start_value bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',\n  increment bigint(21) NOT NULL COMMENT 'increment value',\n  cache_size bigint(21) unsigned NOT NULL,\n  cycle_option tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',\n  cycle_count bigint(21) NOT NULL COMMENT 'How many cycles have been done'\n) ENGINE=InnoDB SEQUENCE=1\n\nSELECT  FROM t1\\G\nnext_not_cached_value: 1\n minimum_value: 1\n maximum_value: 9223372036854775806\n  start_value: 1\n  increment: 1\n  cache_size: 1000\n  cycle_option: 0\n  cycle_count: 0\n`\n\nThe cycle_count column is incremented every time the sequence wraps around.\n\nCredits\n\n Thanks to Jianwe Zhao from Aliyun for his work on SEQUENCE` in AliSQL, which gave ideas and inspiration for this work.\n Thanks to Peter Gulutzan, who helped test and gave useful comments about the implementation.\n\nURL: https://mariadb.com/docs/server/reference/sql-structure/sequences/sequence-overview