How do I make a row generator in mysql.

Question

Is the a way to generate an arbitrary number of rows that can be used in a join similar to the oracle syntax:

SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=10

Answer 1

Have you considered a LEFT OUTER JOIN?

Answer 2

I don't know if this helps but you can number the rows from each select statement with sth. like:

SET @NUM = 0;

SELECT @NUM:=@NUM+1 rowNumber, * FROM ...

And later join them on this one. At large databases this can be very slow.

Answer 3

Hate to say this, but MySQL is the only RDBMS of the big four that doesn't have this feature.

In Oracle:

SELECT  *
FROM    dual
CONNECT BY
        level < n

In MS SQL (up to 100 rows):

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier

In PostgreSQL:

SELECT  *
FROM    generate_series (1, n)

In MySQL, nothing.

Answer 4

If I'm understanding you, you want a list of consequtive numbers?

Just make the list:

create table artificial_range (id int not null primary key auto_increment, idn int);
insert into artificial_range (idn) values (0); --first row
insert into artificial_range(idn) select idn from artificial_range; --2nd
insert into artificial_range(idn) select idn from artificial_range; -- now 4 rows
insert into artificial_range(idn) select idn from artificial_range; --8
insert into artificial_range(idn) select idn from artificial_range; --16
insert into artificial_range(idn) select idn from artificial_range; --32
insert into artificial_range(idn) select idn from artificial_range; --64
insert into artificial_range(idn) select idn from artificial_range; --128

... etc, until you have, say, 1024.

update artificial_range set idn = id - 1 ;

-- now you have a series staring at 1 (id) and a series starting at 0

Now join to it, or join to transformations of it:

    create view days_this_century as 
select date_add('2000-01-01', interval a.idn day) as cdate 
from artificial_range;