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

```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:

```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:

```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.

## Name conflicts

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

```MariaDB [test]> CREATE TABLE seq_1_to_100 (col INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table 'seq_1_to_100' already exists```

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

```MariaDB [test]> ALTER TABLE seq_1_to_100 ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.07 sec)

MariaDB [test]> SELECT * FROM seq_1_to_100;
Empty set (0.00 sec)```

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:

```MariaDB [test]> DROP TABLE seq_1_to_100;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> SELECT COUNT(*) FROM seq_1_to_100;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)```

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

```MariaDB [test]> CREATE TEMPORARY TABLE seq_1_to_100 (col INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)

MariaDB [test]> SELECT * FROM seq_1_to_100;
Empty set (0.00 sec)```