CODEWITHSUNDEEP
mysqlsqlselectrowsdata-generation
Cyril
Cyril

Reputation: 3148

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?

Upvotes: 0

Views: 259

Answers (1)

Ergest Basha
Ergest Basha

Reputation: 9048

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)

Upvotes: 1

Related Questions