MariaDB sequence

In version 10.0.3, MariaDB introduced a storage engine called sequence. I ts ad hoc generates an integer sequence for the operation and then terminates. T he sequence contains positive integers, arranged in descending or ascending order, and uses the start, end, and increment values.

It is not allowed to be used in multiple queries and can only be used in its original query because of its virtual (not written to disk) nature. H owever, sequence tables can be converted to standard tables by the ALTER command. I f you delete the converted table, the sequence table still exists. T he sequence also cannot produce negative numbers or rotate at minimum/maximum values.

Install the sequence engine

Using sequences requires the sequence engine to be installed, and MariaDB is distributed as a plug-in rather than a binary. U se the following command to install it -

INSTALL SONAME "ha_sequence";

After installation, verify it -

SHOW ENGINESG

Keep in mind that after the engine is installed, you cannot create a standard table with a name that uses sequence syntax, but you can create a temporary table with a sequence syntax name.

Create a sequence

There are two ways to create a sequence -

• Create tables and use AUTO_INCREMENT define columns as auto-increments.

• Use an existing database and use a sequence SELECT query to generate a sequence. T he query seq_ using the syntax of the seq_ _step_STEP of the syntax of the syntax.

Best practices prefer the second approach. S ee an example of sequence creation given below -

SELECT * FROM seq_77_to_99;

Sequences have many uses -

• Find the missing value in the column to prevent related problems in the operation -

SELECT myseq.seq FROM seq_22_to_28 myseq LEFT JOIN table1 t ON myseq.seq
   = x.y WHERE x.y IS NULL;

• Combination of construction values -

SELECT x1.seq, x2.seq FROM seq_5_to_9 x1 JOIN seq_5_to_9 x2 ORDER BY 5, 6;

• Find a multiply of numbers -

SELECT seq FROM seq_3_to_100_step_4;

• Construct a date sequence for applications such as the reservation system.
• Construct a time series.