MySQL simulates the PostgreSQLgenerate_series Table Function

PostgreSQL provides a powerful data generation function, generate_series, based on Common Table Expression.
The table structure is as follows: ytt [love]> show create table test_series; + ------------- + partition + | Table | Create Table | + ------------- + partition + | test_series | create table 'test _ series' ('id' int (11) not null, 'Log _ date' date not null) ENGINE = InnoDB default charset = latin1 | + ------------- + merge + 1 row in set (0.00 sec) PostgreSQL calculation result: t_girl = # insert into test_series select seq, current_date-'1 Day': interval * seq from generate_series (1, 20) as g (seq ); INSERT 0 20 t_girl = # select * from test_series; id | log_date ---- + ------------ 1 | 2014-03-02 2 2 | 2014-03-01 3 | 2014-02-28 4 | 2014-02-27 5 | 2014-02-26 6 6 | 2014-02-25 7 | 2014-02-24 8 | 2014-02-23 9 | 2014-02-22 10 | 2014-02-21 11 | 2014-02-20 12 | 2014-02-19 13 | 2014-02-18 14 | 2014-02-17 15 | 2014-02-16 16 16 | 2014-02-15 17 | 2014-02-14 18 | 2014-02-13 19 | 2014-02-12 20 | 2014-02-11 (20 rows)

First, the SESSION variable. DELIMITER $ USE't _ girl $ drop procedure if exists 'SP _ seed' $ create definer = 'root' @ 'localhost' PROCEDURE 'SP _ seed' (IN f_num int unsigned) begin drop table if exists tmp_seed; create temporary table tmp_seed (id INT); begin declare I INT; SET I = 1; WHILE I <= f_num do insert into tmp_seed VALUES (I ); SET I = I + 1; END WHILE; END $ DELIMITER;

Generate 20 seed banks, ytt [love]> call sp_seed (20); Query OK, 1 row affected (0.15 sec). Now we can use the same library and SESSION variable. Ytt [love]> insert into test_series select @ a: = @ a + 1 as seq, date_sub (current_date (), interval @ a day) from tmp_seed, (select @: = 0) as seq; Query OK, 20 rows affected (0.02 sec) Records: 20 Duplicates: 0 Warnings: 0 ytt [love]> select * from test_series; + ---- + ------------ + | id | log_date | + ---- + ------------ + | 1 | 2014-03-02 | 2 | 2014-03-01 | 3 | 2014-02-28 | 4 | 2014-02-27 | 5 | 2014-02-26 | | 6 | 2014-02-25 | 7 | 2014-02-24 | 8 | 2014-02-23 | 9 | 2014-02-22 | 10 | 2014-02-21 | 11 | 2014-02-20 | 12 | 2014-02-19 | 13 | 2014-02-18 | | 14 | 2014-02-17 | 15 | 2014-02-16 | 16 | 2014-02-15 | 17 | 2014-02-14 | 18 | 2014-02-13 | 19 | 2014-02-12 | 20 | 2014-02-11 | + ---- + ------------ + 20 rows in set (0.00 sec)

Second: ytt [love]> insert into test_series select s1.seq, date_sub (current_date (), interval s2.seq day) as date from seq_1_to_20 as s1, seq_1_to_20 as s2 where s1.seq = s2.seq; query OK, 20 rows affected (0.07 sec) Records: 20 Duplicates: 0 Warnings: 0ytt [love]> select * from test_series; + ---- + ------------ + | id | log_date | + ---- + ------------ + | 1 | 2014-03-02 | 2 | 2014-03-01 | 3 | 2014-02-28 | 4 | 2014-02-27 | 5 | 2014-02-26 | | 6 | 2014-02-25 | 7 | 2014-02-24 | 8 | 2014-02-23 | 9 | 2014-02-22 | 10 | 2014-02-21 | 11 | 2014-02-20 | 12 | 2014-02-19 | 13 | 2014-02-18 | | 14 | 2014-02-17 | 15 | 2014-02-16 | 16 | 2014-02-15 | 17 | 2014-02-14 | 18 | 2014-02-13 | 19 | 2014-02-12 | 20 | 2014-02-11 | + ---- + ------------ + 20 rows in set (0.00 sec)