How do I make a row generator in MySQL?

Question

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

Watts

Could you please post the query you meant to write? – Psychodiagnostics

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

Differentia

Reg MySQL: Hope that fills the gap a little bit: use-the-index-luke.com/blog/2011-07-30/… – Abe

Also incase anyone is looking for the mysql version of the example schemas in use the index luke, here is the link use-the-index-luke.com/sql/example-schema/mysql/where-clause – Pregnable

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

Meshwork

Answer 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!

Caryophyllaceous

Example of creating ten records with different dates: INSERT INTO T1 (id, created_at) SELECT one.X, two.X FROM (select seq, seq X from seq_1_to_10) one LEFT JOIN (select seq, NOW() - INTERVAL seq DAY X from seq_1_to_10) two ON one.seq = two.seq – Legere

Answer 4

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.

Antisana

Answer 5

I had a table with a column (c5) that contained a number x, I needed a SQL expression that repeated the same row x numbers of times.

My table A contained:

c1  c2  c3  c4  c5
16  1   2   16  3
16  1   2   17  2 
16  1   2   18  1

And I needed:

c1  c2  c3  c4  c5  n
16  1   2   16  3   1
16  1   2   16  3   2
16  1   2   16  3   3
16  1   2   17  2   1
16  1   2   17  2   2
16  1   2   18  1   1

I solved that with the expression:

SELECT
    c1, c2, c3, c4, c5, row_number AS n
FROM
    (
        SELECT
            @curRow := @curRow + 1 AS row_number
        FROM
            tablea
        JOIN (SELECT @curRow := 0) r
        WHERE
            @curRow < (
                SELECT
                    max(field1)
                FROM
                    tablea
            )
    ) AS vwtable2
LEFT JOIN tablea d ON vwtable2.row_number <= tablea.field1;

Vankirk

Answer 6

If I'm understanding you, you want a list of consequtive numbers?

Just make the list:

create table artificial_range (id int not null primary key auto_increment, idn int);
insert into artificial_range (idn) values (0); --first row
insert into artificial_range(idn) select idn from artificial_range; --2nd
insert into artificial_range(idn) select idn from artificial_range; -- now 4 rows
insert into artificial_range(idn) select idn from artificial_range; --8
insert into artificial_range(idn) select idn from artificial_range; --16
insert into artificial_range(idn) select idn from artificial_range; --32
insert into artificial_range(idn) select idn from artificial_range; --64
insert into artificial_range(idn) select idn from artificial_range; --128

... etc, until you have, say, 1024.

update artificial_range set idn = id - 1 ; 

-- now you have a series staring at 1 (id) and a series starting at 0

Now join to it, or join to transformations of it:

    create view days_this_century as 
select date_add('2000-01-01', interval a.idn day) as cdate 
from artificial_range;

Rigid

that was exactly what I was hoping to avoid. – Watts

Answer 7

I don't know if this helps but you can number the rows from each select statement with sth. like:

SET @NUM = 0;

SELECT @NUM:=@NUM+1 rowNumber, * FROM ...

And later join them on this one. At large databases this can be very slow.

Ninefold

Answer 8

To generate 10 rows:

SELECT a AS a 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 from dual

You can generate 100 rows making a join with another 10 rows:

select t2.a*10 + t1.a from (SELECT 0 AS a 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) t1, (SELECT 0 AS a 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) t2

And then 1000 rows with another join, ...

Shadow

Answer 9

You can, but it's a little bit tricky,

here it is:

mysql> create table t(inc bigint primary key auto_increment);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values(0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t select 0 from t;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 0 from t;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t select 0 from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t select 0 from t;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select count(inc), max(inc) from t;
+------------+----------+
| count(inc) | max(inc) |
+------------+----------+
|         16 |       20 |
+------------+----------+
1 row in set (0.00 sec)

mysql> select row_number() over w as inc from t window w as (order by inc);
+-----+
| inc |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
+-----+
16 rows in set (0.00 sec)

You can double the count of generated rows by issuing the statement

insert into t select 0 from t;

as much as you need.

Also note that auto_increment by default generate some gaps, that's why row_number() is used.

Torrential