Is there a function similar to generate_series function in PostgreSQL?

Question

The generate_series function in PostgreSQL generates a large amount of data very quickly. But because I am not familiar with MySQL, I wrote a stored procedure and found that the speed of generating data is very slow.

delimiter $$
CREATE PROCEDURE inst_para_select(IN n int)
BEGIN    
    DECLARE i INT DEFAULT 1;
    WHILE i <= n  DO
        INSERT INTO `para_select` VALUES(i,CONCAT(i,'_test'),NOW());
        SET i = i+1;
    END WHILE;
END $$
delimiter;

Is there a better way to quickly create large amounts of data in MySQL, functions or stored procedures?

Does this answer your question? [generate\_series() equivalent in MySQL](https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql) — Schwern, Feb 16 '20 at 17:36
Thanks Schwern, I understand that in that post, there needs to be a premise that there is already an any_table, but PostgreSQL's generate_series does not need a table in advance, and you can directly use this generated data to create it. — HappyGuitarBoy, Feb 17 '20 at 01:59

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Every well performing solution I've seen for MySQL must use some sort of existing table of numbers or a view. This limits you to how many rows are in the table or view.

You could make a table of sequential integers as large as you might possibly need and be done with it.

One of the more interesting generated implementations I've come across is by Markus Winand. He creates a view from 0 to 15. Then uses cross joins and bit math to generate views of larger and larger sequences.

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;

The views will always build the full Cartesian product. Although you can limit the result with the where clause, the work to produce all rows is still done. It's just filtering the unneeded. That means, it is very inefficient to use a large generator view if you need just a few rows.

Always use the smallest possible generator for best performance.

Is there a function similar to generate_series function in PostgreSQL?

1 Answers1