mysqlsqlselectrowsdata-generation

Generate a specific number of rows in MySQL (without using a table)


How to generate a defined number of rows with an SQL query — without using a table (where these rows already exist) ?

For example, in order to return 3 rows, we could do this:

select * from (
    select 1 union all
    select 1 union all
    select 1
) t

Which gives this result:

1
1
1

However, this is impractical if I need to return, say, a thousand rows. Is there another way?


Solution

  • You can use:

    WITH recursive numbers AS 
    (  select 1 as Numbers
       union all
       select Numbers
       from numbers
       limit 1000
     )
    select * from numbers;
    

    Change the limit as you need.

    Another option is :

    WITH recursive numbers AS 
    (  select 1 as Numbers
       union all
       select Numbers + 1
       from numbers
       where Numbers < 1000
     )
    select * from numbers;
    

    You may need to increase @@cte_max_recursion_depth to a larger value.

    Tested on

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.25    |
    +-----------+
    1 row in set (0.00 sec)
    
    
    mysql>     WITH recursive numbers AS
        ->     (  select 1 as Numbers
        ->        union all
        ->        select Numbers
        ->        from numbers
        ->        limit 100
        ->      )
        ->     select * from numbers;
    +---------+
    | Numbers |
    +---------+
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    |    1 |
    +------+
    100 rows in set (0.00 sec)