How Can I Generate a Sequence of Numbers in MySQL Without External Tools?

Generating Number Sequences Directly in MySQL

MySQL offers a built-in method for creating consecutive number sequences within SQL queries, avoiding the need for external scripting.

For example, to generate numbers from 1 to 5:

<code>nr
1
2
3
4
5</code>

The Solution: A Set-Based Approach

The following query uses a set-based approach to achieve this:

<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 method allows for the generation of number sequences directly within your MySQL queries, simplifying database management.

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