Generate 1 to 1000 in mysql


Sometimes its necessary to have thousand rows to join or generate new values, we need to get the thousand rows first. For this I used union of numbers 0-9.  I have joined four of these to get to four digit values. So here is the code:

SELECT @row := @row + 1 as row FROM
(select 0 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) t,
(select 0 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) t2,
(select 0 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) t3,
(select 0 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) t4,
(SELECT @row:=0) a

This code displays numbers from 1 to 10000. This is useful for generating dates of a range of dates, which I would soon post about.


, , , , , , , ,

  1. #1 by Nando on August 30, 2010 - 8:33 pm

    hey there!
    Is there a reason that you go through the numbers, 0 to 9 but skip 2 and repeat 6 twice? I’m trying to use your splendid example to get a date range for a specific week but noticed this and was puzzled!

  2. #2 by Srijith R on August 30, 2010 - 9:14 pm

    It actually needs only 10 numbers need not be 0-9. Anyways I have corrected it in the code. Thanks for pointing it out

(will not be published)