mysql - 使用存储过程插入多个值

Question

我是存储过程的新手，以下是我的要求，我需要提出存储过程来填充我的表(例如下面)过去 4 个月的数据，其中 T_KEY、A_KEY、M_KEY 和 G_KEY 的值不同。

mysql> select * from R_A_METRICS;  
+---------------------+------------+-----------------+-------+------------+  
| TIMESTAMP           |  T_KEY      | A_KEY           | M_KEY | G_KEY         

| 2017-06-30 00:00:00 |    a        | b               | c     | d                         
| 2017-06-30 00:15:00 |   a1        | b1              | c1    | d1   

下面是我填充 4 个月数据的示例程序，

PROCEDURE `POPULATE_A_METRICS`()  
BEGIN  
DECLARE START_DAY TIMESTAMP;  
DECLARE END_DAY TIMESTAMP;  
SET START_DAY = CURDATE() - INTERVAL 4 MONTH;  
SET END_DAY = CURDATE();  
WHILE (START_DAY <= END_DAY) DO  
insert into R_A_METRICS values(START_DAY,T_KEY,A_KEY,M_KEY,G_KEY);  
SET START_DAY = START_DAY + INTERVAL 15 MINUTE;  
END WHILE;  
END

我想设置像@T_KEY=a,a1,a2这样的值并将其以循环方式插入到表中。其他列(如 A_KEY、M_KEY、G_KEY)的类似要求

关于如何通过存储过程实现这一点有什么想法吗？

TIA

Answer 1

要创建一系列插入值(例如 a、a1、a2)，您必须有一个临时值并存储要存储的当前序列:

delimiter $$
DROP PROCEDURE  IF EXISTS `POPULATE_A_METRICS`$$
CREATE PROCEDURE `POPULATE_A_METRICS`()  
BEGIN  
    DECLARE START_DAY TIMESTAMP;  
    DECLARE END_DAY TIMESTAMP;  

    SET START_DAY = CURDATE() - INTERVAL 4 MONTH;  
    SET END_DAY = CURDATE();  

    set @counter = '';  /*a counter, blank at first*/
    set @T_KEY = 'a';
    set @A_KEY = 'b';
    set @M_KEY = 'c';
    set @G_KEY = 'd';

    WHILE (START_DAY <= END_DAY) DO  

        insert into R_A_METRICS (`TIMESTAMP`,T_KEY,A_KEY,M_KEY,G_KEY)
        values (START_DAY,@T_KEY,@A_KEY,@M_KEY,@G_KEY); 

        SET START_DAY = START_DAY + INTERVAL 15 MINUTE;  

        set @counter = @counter + 1 ; /*increase counter and concatenate bellow: */
        set @T_KEY = concat('a',@counter);
        set @A_KEY = concat('b',@counter);
        set @M_KEY = concat('c',@counter);
        set @G_KEY = concat('d',@counter);

    END WHILE;  
END$$

执行后你将得到:

mysql> SELECT `TIMESTAMP`,T_KEY,A_KEY,M_KEY,G_KEY FROM R_A_METRICS;
+---------------------+-------+-------+-------+-------+
| TIMESTAMP           | T_KEY | A_KEY | M_KEY | G_KEY |
+---------------------+-------+-------+-------+-------+
| 2017-11-02 20:00:00 | a     | b     | c     | d     |
| 2017-11-02 20:15:00 | a1    | b1    | c1    | d1    |
| 2017-11-02 20:30:00 | a2    | b2    | c2    | d2    |
| 2017-11-02 20:45:00 | a3    | b3    | c3    | d3    |
| 2017-11-02 21:00:00 | a4    | b4    | c4    | d4    |
| 2017-11-02 21:15:00 | a5    | b5    | c5    | d5    |
| 2017-11-02 21:30:00 | a6    | b6    | c6    | d6    |
| 2017-11-02 21:45:00 | a7    | b7    | c7    | d7    |
| 2017-11-02 22:00:00 | a8    | b8    | c8    | d8    |
| 2017-11-02 22:15:00 | a9    | b9    | c9    | d9    |