Reputation: 1166
I just want to generate xx rows with a number to each one, something like that :
SELECT 1 TO 50
The resultset contains 50 rows, numeroted from 1 to 50
thanks
Upvotes: 0
Views: 88
Reputation: 83457
With a stored procedure:
delimiter #
create procedure my_counter(lower_bound INT, upper_bound INT)
begin
declare v_max int unsigned default upper_bound;
declare v_counter int unsigned default lower_bound;
CREATE TABLE temp_my_counter (id int);
while v_counter <= v_max do
INSERT INTO temp_my_counter SELECT v_counter;
set v_counter=v_counter+1;
end while;
SELECT temp_my_counter.id FROM temp_my_counter;
DROP TABLE temp_my_counter;
end #
delimiter ;
Then call my_counter(2, 5);
will return
2
3
4
5
Upvotes: 1
Reputation: 1981
You could try this:
select @count := @count + 1
from (select @count := 0) count
cross join (select 1 from information_schema.TABLES limit 10) as ten
cross join (select 1 from information_schema.TABLES limit 10) as hundred
cross join (select 1 from information_schema.TABLES limit 10) as thousand
cross join (select 1 from information_schema.TABLES limit 10) as ten_thousand
limit 50;
or this (which is quicker):
select @count := @count + 1
from (select @count := 0) count
cross join (select 1 union all select 1) as two
cross join (select 1 union all select 1) as four
cross join (select 1 union all select 1) as eight
cross join (select 1 union all select 1) as sixteen
cross join (select 1 union all select 1) as thirty_two
cross join (select 1 union all select 1) as sixty_four
cross join (select 1 union all select 1) as one_two_eight
cross join (select 1 union all select 1) as two_five_six
cross join (select 1 union all select 1) as five_twelve
cross join (select 1 union all select 1) as one_thousand_twenty_four
limit 50;
Upvotes: 0
Reputation: 47331
The easiest way :-
select 1 union
select 2 union
select 3 union
select 4 union
...
select 50;
Or, you can consider to write a procedural or user-defined-function
Upvotes: 1