How to Efficiently Generate a Range of Numbers in MySQL Without Loops?

Generating Number Sequences in MySQL Without Loops

MySQL often requires generating consecutive numbers for tasks like populating tables or sequencing data. This can be efficiently achieved using set-based operations, avoiding the need for loops.

Here's a query to generate a numerical range:

<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 SeqValue ... UNION ALL SELECT 9 SeqValue
    ) ONES
    CROSS JOIN (
        SELECT 0 SeqValue UNION ALL SELECT 10 SeqValue ... UNION ALL SELECT 90 SeqValue
    ) TENS
    CROSS JOIN (
        SELECT 0 SeqValue UNION ALL SELECT 100 SeqValue ... UNION ALL SELECT 900 SeqValue
    ) HUNDREDS
) SEQ;</code>

This query leverages subqueries (ONES, TENS, HUNDREDS) to create sets of digits (0-9, 0-90, 0-900). CROSS JOIN combines these sets, generating all possible combinations. The results are summed to create the sequence, and finally inserted into myTable.

This approach offers a scalable and efficient method for creating numerical sequences within MySQL, eliminating the overhead of procedural loops. The range is easily adjustable by modifying the subqueries.

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