mysql series_MySQL模拟PostgreSQLgenerate_series表函数

PostgreSQL 提供了一个很强大的造数据的函数generate_series，基于Common Table Expression。mysql="" pre="" sequence="" session="" />

表结构如下： ytt[love]>show create table test_series; +-------------+-------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-------------------------------------------------------------------------------------------------------------------------+ | test_series | CREATE TABLE `test_series` ( `id` int(11) NOT NULL, `log_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------------+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 对应的PostgreSQL 运算结果： 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 | 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)第一： SESSION 变量。

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; END$$ DELIMITER ;

生成20个种子库 ytt[love]>call sp_seed(20); Query OK, 1 row affected (0.15 sec) 现在利用刚才的种子库以及SESSION 变量来实现。 ytt[love]>insert into test_series select @a := @a + 1 as seq, date_sub(current_date(), interval @a day) from tmp_seed,(select @a:=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)

第二：

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