Generating Numeric Sequences in MySQL

Generating Numeric Sequences in MySQLWhat is the easiest way to generate a sequence of integers in MySQL? In other words, which “SELECT <something>” statement should I write to get 0, 1, 2, … N – 1?

This is the question I have been struggling with for years and it looks like I have finally got the answer (although I must admit I had to put some development efforts and add a few hundred lines to the server code).  Percona Server for MySQL 8.0.20-11 includes a new feature dedicated to solving exactly this problem.

However, before revealing all the secrets, let us first consider existing solutions. So, we want to get the following:

What are our options for doing so?

The Old School Way

Let us start with the most straightforward solutions.

UNION to the Rescue

It may sound a bit primitive but the simplest solution would be to combine the result of multiple SELECT statements into a single result set with UNION.

Although this may work for smaller numbers, this solution is not very extensible.

Existing Table With a Unique Column

Let’s say that we already have a table with a unique column of any data type. For instance:

Now, we can join this table with a copy of itself and select the number of records for which id in the copy is less than or equal to the id in the original table.

The main drawback of this solution is its quadratic complexity on N that may cause significant resource utilization when N is big.

Session Variable Increment Within a SELECT

Provided that we already have a table t1 as in the previous example (although unique column constraint is not required here), we can join it with a single value SELECT that assigns initial value to a session variable. At the same time, for each record of the existing table, it will increment the value of this session variable.

This one is not bad: it’s extensible, linear complexity on N does not introduce unnecessary overhead, and the only drawback is a requirement to have an existing table.

Joining Multiple Views

We can always join several tables (or views) that contain more than one record to multiply the total number of records in the result set.

Using the same approach, by intersecting K instances of binary_v we can generate a sequence of 2^K values. Similarly, we can create a view for digits and as the result get 10^K values.

Although this seems to be pretty easy to understand, the execution plan of such a query is definitely far from being perfect.


Stored Procedures

Basically, before selecting, we can create a temporary table and fill it with the required numbers utilizing a pre-created stored procedure.

The stored procedure itself can be defined as follows: