How do I make a row generator in MySQL?

Question

Is there 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

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 

or using hint up to 32768

WITH hier(row) AS         (         SELECT  1         UNION ALL         SELECT  row + 1         FROM    hier         WHERE   row < 32768         ) SELECT  * FROM    hier OPTION (MAXRECURSION 32767) -- 32767 is the maximum value of the hint 

In PostgreSQL:

SELECT  * FROM    generate_series (1, n) 

In MySQL, nothing.

Answer 2

In MySql, it is my understand that you can get more than one row with a SELECT with no table (or DUAL).

Therefore, to get multiple rows, you do need a real or temporary table with at least the required number of rows.

However, you do not need to build a temporary table as you can use ANY existing table which has at least the number of rows required. So, if you have a table with at least the required number of rows, use:

SELECT  @curRow := @curRow + 1 AS row_number FROM    sometable  JOIN    (SELECT @curRow := 0) r WHERE   @curRow<100; 

Just replace "sometable" with the name of any table of yours with at least the required number of rows.

PS: The "r" is a table "alias": I could have used "AS r". Any subquery in a FROM or JOIN clause creates a "derived table" which, as with all tables, must have a name or alias. (See MySql manual: 13.2.9.8. Subqueries in the FROM Clause)