Generating a range of numbers in MySQL

Question

How do I generate a range of consecutive numbers (one per line) from a MySQL query so that I can insert them into a table?

For example:

nr 1 2 3 4 5 

I would like to use only MySQL for this (not PHP or other languages).

Answer 1

Here is one way to do it set-based without loops. This can also be made into a view for re-use. The example shows the generation of a sequence from 0 through 999, but of course, it may be modified to suit.

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 2 SeqValue     UNION ALL     SELECT 3 SeqValue     UNION ALL     SELECT 4 SeqValue     UNION ALL     SELECT 5 SeqValue     UNION ALL     SELECT 6 SeqValue     UNION ALL     SELECT 7 SeqValue     UNION ALL     SELECT 8 SeqValue     UNION ALL     SELECT 9 SeqValue     ) ONES CROSS JOIN     (     SELECT 0 SeqValue     UNION ALL     SELECT 10 SeqValue     UNION ALL     SELECT 20 SeqValue     UNION ALL     SELECT 30 SeqValue     UNION ALL     SELECT 40 SeqValue     UNION ALL     SELECT 50 SeqValue     UNION ALL     SELECT 60 SeqValue     UNION ALL     SELECT 70 SeqValue     UNION ALL     SELECT 80 SeqValue     UNION ALL     SELECT 90 SeqValue     ) TENS CROSS JOIN     (     SELECT 0 SeqValue     UNION ALL     SELECT 100 SeqValue     UNION ALL     SELECT 200 SeqValue     UNION ALL     SELECT 300 SeqValue     UNION ALL     SELECT 400 SeqValue     UNION ALL     SELECT 500 SeqValue     UNION ALL     SELECT 600 SeqValue     UNION ALL     SELECT 700 SeqValue     UNION ALL     SELECT 800 SeqValue     UNION ALL     SELECT 900 SeqValue     ) HUNDREDS ) SEQ 

Answer 2

Here's a hardware engineer's version of Pittsburgh DBA's solution:

SELECT     (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue) SeqValue FROM     (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8     CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16;