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