MySQL 模拟PostgreSQL generate_series 表函数

浏览数：21 / 时间：2015年06月12日

PostgreSQL 提供了一个很强大的造数据的函数generate_series，基于Common Table Expression。
MySQL 没有复杂的应用程序类型，该如何实现这样的功能呢？我想到的三种方法如下:
1. 用存储过程来做。缺点是写好多数据库不擅长的应用逻辑。
2. 我们想到MySQL提供了SESSION 变量这样的特性，可以很方便的完成同样的功能。
3. MariaDB 提供了一种sequence 引擎，也可以方便的做这件事情。

第一种我就不实现了，我来举例说明后两种。

表结构如下：
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 变量。

MySQL 的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)

第二：
MySQL(MariaDB ) 提供了一个序列引擎，可以有这样的功能。
由于MySQL 没有表函数功能，所以如果要造多个字段的数据，就得用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 = 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)

MySQL 模拟PostgreSQL generate_series 表函数,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

MySQL 模拟PostgreSQL generate_series 表函数