【原创】MySQL模拟PostgreSQLgenerate_series表函数

2019独角兽企业重金招聘Python工程师标准>>>

PostgreSQL 提供了一个很强大的造数据的函数generate_series&＃xff0c;基于Common Table Expression。

MySQL 没有复杂的应用程序类型&＃xff0c;该如何实现这样的功能呢&＃xff1f; 我想到的三种方法如下:

1. 用存储过程来做。缺点是写好多数据库不擅长的应用逻辑。

2. 我们想到MySQL提供了SESSION 变量这样的特性&＃xff0c; 可以很方便的完成同样的功能。

3. MariaDB 提供了一种sequence 引擎&＃xff0c;也可以方便的做这件事情。

第一种我就不实现了&＃xff0c; 我来举例说明后两种。

表结构如下&＃xff1a; ytt[love]>show create table test_series; &＃43;-------------&＃43;-------------------------------------------------------------------------------------------------------------------------&＃43; | Table | Create Table | &＃43;-------------&＃43;-------------------------------------------------------------------------------------------------------------------------&＃43; | test_series | CREATE TABLE &＃96;test_series&＃96; ( &＃96;id&＃96; int(11) NOT NULL, &＃96;log_date&＃96; date NOT NULL ) ENGINE&＃61;InnoDB DEFAULT CHARSET&＃61;latin1 | &＃43;-------------&＃43;-------------------------------------------------------------------------------------------------------------------------&＃43; 1 row in set (0.00 sec) 对应的PostgreSQL 运算结果&＃xff1a; t_girl&＃61;# insert into test_series select seq, current_date - &＃39;1 day&＃39;::interval*seq from generate_series(1,20) as g(seq); INSERT 0 20 t_girl&＃61;# select * from test_series; id | log_date ----&＃43;------------ 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)

第一&＃xff1a; SESSION 变量。

MySQL 的SESSION 变量来变相实现的话&＃xff0c;需要一个种子库。

以下存储过程生成种子库。 DELIMITER $$ USE &＃96;t_girl&＃96;$$ DROP PROCEDURE IF EXISTS &＃96;sp_seed&＃96;$$ CREATE DEFINER&＃61;&＃96;root&＃96;&＃64;&＃96;localhost&＃96; PROCEDURE &＃96;sp_seed&＃96;( 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 &＃61; 1; WHILE i <&＃61; f_num DO INSERT INTO tmp_seed VALUES (i); SET i &＃61; i &＃43; 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 &＃64;a :&＃61; &＃64;a &＃43; 1 as seq, date_sub(current_date(), interval &＃64;a day) from tmp_seed,(select &＃64;a:&＃61;0) as seq; Query OK, 20 rows affected (0.02 sec) Records: 20 Duplicates: 0 Warnings: 0 ytt[love]>select * from test_series; &＃43;----&＃43;------------&＃43; | id | log_date | &＃43;----&＃43;------------&＃43; | 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 | &＃43;----&＃43;------------&＃43; 20 rows in set (0.00 sec)

第二&＃xff1a;

MySQL(MariaDB ) 提供了一个序列引擎&＃xff0c;可以有这样的功能。

由于MySQL 没有表函数功能&＃xff0c;所以如果要造多个字段的数据&＃xff0c;就得用JOIN来实现了。

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 &＃61; s2.seq; Query OK, 20 rows affected (0.07 sec) Records: 20 Duplicates: 0 Warnings: 0 ytt[love]>select * from test_series; &＃43;----&＃43;------------&＃43; | id | log_date | &＃43;----&＃43;------------&＃43; | 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 | &＃43;----&＃43;------------&＃43; 20 rows in set (0.00 sec)