SEQUENCE Storage Engine

This article is about the Sequence storage engine. For details about sequence objects, see Sequences.

A Sequence engine allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment.

It creates completely virtual, ephemeral tables automatically when you need them. There is no way to create a Sequence table explicitly. Nor are they ever written to disk or create .frm files. They are read-only, transactional, and support XA.

Installing

The Sequence engine is installed by default, and SHOW ENGINES will list the Sequence storage engine as supported:

SHOW ENGINES\G
...
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: SEQUENCE
     Support: YES
     Comment: Generated tables filled with sequential values
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: MRG_MyISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO

...

Usage and Examples

To use a Sequence table, you simply select from it, as in

SELECT * FROM seq_1_to_5;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

To use a sequence in a statement, you select from the table named by a pattern seq_FROMtoTO or seq_FROMtoTOstepSTEP.

In the case of an odd step, the sequence will commence with the FROM, and end at the final result before TO.

A sequence can go backwards too. In this case the final value will always be the TO value, so that a descending sequence has the same values as an ascending sequence:

This engine is particularly useful with joins and subqueries. For example, this query finds all prime numbers below 50:

And almost (without 2, the only even prime number) the same result with joins:

Sequence tables can also be useful in date calculations. For example, to find the day of the week that a particular date has fallen on over a 40 year period (perhaps for birthday planning ahead!):

Although Sequence tables can only directly make use of positive integers, they can indirectly be used to return negative results by making use of the statement:

is required to avoid a BIGINT UNSIGNED value is out of range error.

Sequence tables, while virtual, are still tables, so they must be in a database. This means that a default database must be selected (for example, via the command) to be able to query a Sequence table. The database cannot be used as the default for a Sequence table.

If the SEQUENCE storage engine is installed, it is not possible to create a table with a name which follows the SEQUENCE pattern:

However, a SEQUENCE table can be converted to another engine and the new table can be referred in any statement:

While a SEQUENCE table cannot be dropped, it is possible to drop the converted table. The SEQUENCE table with the same name will still exist:

A temporary table with a SEQUENCE-like name can always be created and used:

• For details about sequence objects, see .

• - Dean Ellis tries out the Sequence engine.

• - Federico Razzoli writes more in-depth on the engine.

_{This page is licensed: CC BY-SA / Gnu FDL}