Sequence

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

A Sequence engine allows to create ascending or descending sequences of numbers with a given starting, ending values, and a given 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 |
+-----+
5 rows in set (0.00 sec)

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:

MariaDB [test]> SELECT * FROM seq_5_to_1_step_2;
+-----+
| seq |
+-----+
|   5 |
|   3 |
|   1 |
+-----+
3 rows in set (0.00 sec)

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 |
+-----+
15 rows in set (0.01 sec)

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.