Sequence

You are viewing an old version of this article. View the current version here.

A Sequence engine allows the creation of ascending or descending sequences of numbers 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.

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

MariaDB [test]> 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_FROM_to_TO or seq_FROM_to_TO_step_STEP.

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

MariaDB [test]> SELECT * FROM seq_5_to_1_step_2;
+-----+
| seq |
+-----+
|   5 |
|   3 |
|   1 |
+-----+

This engine is particularly used in joins. For example, this query finds all prime numbers below 50:

MariaDB [test]> select seq from seq_2_to_50 s1 where 0 not in
     -> (select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq));
+-----+
| seq |
+-----+
|   2 |
|   3 |
|   5 |
|   7 |
|  11 |
|  13 |
|  17 |
|  19 |
|  23 |
|  29 |
|  31 |
|  37 |
|  41 |
|  43 |
|  47 |
+-----+

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 USE command) to be able to query a Sequence table.

Resources

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.