How to Generate a Range of Numbers in MySQL Using Only SQL?

Generate number sequence in MySQL

Using MySQL queries to generate a series of consecutive numbers can be achieved in a variety of ways. However, for a collection-based solution using only MySQL, consider the following query:

<code class="language-sql">INSERT INTO myTable (nr)
SELECT SEQ.SeqValue
FROM (
    SELECT (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
    FROM (
        SELECT 0 SeqValue UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) ONES
    CROSS JOIN (
        SELECT 0 SeqValue UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
    ) TENS
    CROSS JOIN (
        SELECT 0 SeqValue UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
    ) HUNDREDS
) SEQ;</code>

This query relies on a nested set of subqueries to generate a sequence of numbers. Specifically:

1. ONES: Generates a sequence of numbers from 0 to 9.
2. TENS: Generates a sequence of multiples of 10 from 0 to 90.
3. HUNDREDS: Generates a sequence of multiples of 100 from 0 to 900.

By cross-joining these subqueries, we obtain all possible combinations of sequences. The final SEQ subquery combines these values ​​to generate the desired sequence:

<code>SeqValue = HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue</code>

For example, to generate a sequence from 0 to 999, the query would return the following output:

<code>nr
0
1
2
3
4
5
...
998
999</code>

The above is the detailed content of How to Generate a Range of Numbers in MySQL Using Only SQL?. For more information, please follow other related articles on the PHP Chinese website!