How Can I Generate a Sequence of Integers in MySQL?

Building a Sequence of Integers in MySQL

Creating a simple sequence of integers from n to m inclusive can be a common requirement in database operations. To achieve this in MySQL, a frequently recommended method involves using a recursive Common Table Expression (CTE).

One such approach involves merging a recursive CTE with an existing table:

WITH RECURSIVE Numbers AS (
    SELECT
        n,
        n + 1 AS next_n
    FROM Numbers
    WHERE n < m
    UNION
    SELECT
        m,
        NULL
)
SELECT
    n
FROM Numbers
ORDER BY n;

In this example, the Numbers CTE is defined to generate a sequence of integers by iteratively adding 1 to the previous value until it reaches the specified maximum value m. The query then uses this CTE to select and order the generated sequence.

An alternative approach, as suggested in the reference question, involves using a single query with a user-defined variable:

SET @row := 0;
SELECT
    @row := @row + 1 AS row,
    t.*
FROM some_table t, (SELECT @row := 0) r;

This method initializes the @row variable to 0 and increments it by 1 for each row in the result set. While this approach can be simpler in some scenarios, it relies on user-defined variables that are not supported in all MySQL versions or contexts.

The above is the detailed content of How Can I Generate a Sequence of Integers in MySQL?. For more information, please follow other related articles on the PHP Chinese website!