How do I make a row generator in MySQL?

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.

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)

MySQL 8.0

With MySQL 8.0, MariaDB 10.2, and later versions, you can use recursive CTEs:

WITH RECURSIVE sequence AS (
    SELECT 1 AS level
    UNION ALL
    SELECT level + 1 AS value
    FROM sequence
    WHERE sequence.level < 10
)
SELECT level
FROM sequence;

Note that CTEs are limited by cte_max_recursion_depth (default 1000, max 4,294,967,295 (2³²−1)) in MySQL and by max_recursive_iterations (default 4,294,967,295) in MariaDB.

You can increase the limit by executing:

SET cte_max_recursion_depth = 4294967295;

It will only affect your current session and won't be persisted.

MySQL 5.7, 5.6 and less

For MySQL versions prior to 8.0, you can use the clever trick of Markus Winand below:

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n 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   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
   FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
   FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
   FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
   FROM generator_64k lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_16m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
   FROM generator_64k lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_4b
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
   FROM generator_64k lo, generator_64k hi;

and then:

SELECT n FROM generator_4b limit 10;

It takes only about 20 ms on my laptop to create even generator_4b, which contains more than 4 billion rows. And all of the generator views above combined take only 28 KB of storage.

If you want to know how it works, you can find a detailed explanation in his blog post.

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!