How do I make a row generator in MySQL?

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

Solution 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.

Solution 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)

Solution 3:

Since this is currently one of the first results in Google for "mysql row generator", I'll add an update.

If your flavor of MySQL happens to be MariaDB, they have this feature. It's called the "Sequence Storage engine" and it's used like this:

select * from seq_1_to_10;

With the results:

+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
+-----+
10 rows in set (0.00 sec)

Until version 10.0 it was a separate plugin that needed to be explicitly installed, but from 10.0 onwards it's built in. Enjoy!