mysql觸發器/儲存過程 - 逐行更新另一個表

我已經搜尋並閱讀了 3 天有關此問題的許多答案，但無法獲得有關如何執行此操作的明確答案。

這會有點長，請耐心等待。

首先，我正在嘗試製作一個簡單的彩票應用程序。我希望你們每個人都知道 6/49 彩票的事情。

好吧，根據遊戲，首先，客戶玩彩票（lotteryTBL）一周。然後選擇 1-49 之間的 6 個數字作為幸運數字 ( resultsTBL)。

我想要做的是lotteryTBL在將新的結果行插入到resultsTBL.

結構lotteryTBL：

ID    num1  num2  num3  num4  num5  num6     draw_date      howmanyknew 
1       5    6     8    15      18    25     08-21-2015         0
2       7    15    18   30      40    45     08-21-2015         0

結構resultsTBL：

ID    num1  num2  num3  num4  num5  num6     draw_date  
1      15    18    35    38    40    47     08-21-2015 

現在，這是我想要的虛擬碼：

after insert a new row into resultsTBL
  total <- 0
  for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date
      total <- calculate how many of them are the same with the resultsTBL's row.
  update lotteryTBL set howmanyknew = total where "some condition"

首先，我嘗試用觸發器來做，有人說“它是動態sql，你不能使用觸發器”，有人說“使用儲存過程”。我也這樣做了。但是，他們從來沒有工作過。

無論如何，有人可以為此輸入一個好的虛擬碼嗎？我必須使用哪種方法？這裡的邏輯是什麼？

我已經在TRIGGER呼叫Stored Procedure內resultsTBL表中做到了。

表lotteryTBL和resultsTBL：

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           0 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           0 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test.resultsTBL;
Empty set (0.00 sec)

mysql> 

插入resultsTBL：

mysql> INSERT INTO test.resultsTBL(id, num1, num2, num3, num4, num5, num6)VALUES('1', '1', '5', '4', '8', '25', '40');
Query OK, 1 row affected (0.00 sec)

mysql> 

結果：

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           2 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           4 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> 

如果您查看INSERT：

• ID= 1: 1,5有 2 個值匹配。
• ID= 2有 4 個值匹配： 4,8,25,40。

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.resultsTBL_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
BEGIN
   CALL test.sp_lottery(NEW.id,NEW.num1,NEW.num2,NEW.num3,NEW.num4,NEW.num5,NEW.num6);
END$$
DELIMITER ;

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lottery`(
IN eID INT(5),
IN eBLAH01 INT(5),
IN eBLAH02 INT(5),
IN eBLAH03 INT(5),
IN eBLAH04 INT(5),
IN eBLAH05 INT(5),
IN eBLAH06 INT(5))
BEGIN
   # [ lotteryTBL ]
   SET @ID=1;
   # [ ITERATE FOR EVERY ROW IN lotteryTBL ]
   WHILE @ID IS NOT NULL DO
       SET @NUM1=(SELECT num1 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
       SET @NUM2=(SELECT num2 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
       SET @NUM3=(SELECT num3 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
       SET @NUM4=(SELECT num4 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
       SET @NUM5=(SELECT num5 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
       SET @NUM6=(SELECT num6 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
       SET @howmanyknew=0;
       # [ NUM1 ]
       IF (eBLAH01=@NUM1 || eBLAH02=@NUM1 || eBLAH03=@NUM1 || eBLAH04=@NUM1 || eBLAH05=@NUM1 || eBLAH06=@NUM1 ) THEN
           SET @howmanyknew=@howmanyknew+1;
       END IF;
        # [ NUM2 ]
       IF (eBLAH01=@NUM2 || eBLAH02=@NUM2 || eBLAH03=@NUM2 || eBLAH04=@NUM2 || eBLAH05=@NUM2 || eBLAH06=@NUM2 ) THEN
           SET @howmanyknew=@howmanyknew+1;
       END IF;
       # [ NUM3 ]
       IF (eBLAH01=@NUM3 || eBLAH02=@NUM3 || eBLAH03=@NUM3 || eBLAH04=@NUM3 || eBLAH05=@NUM3 || eBLAH06=@NUM3 ) THEN
           SET @howmanyknew=@howmanyknew+1;
       END IF;
       # [ NUM4 ]
       IF (eBLAH01=@NUM4 || eBLAH02=@NUM4 || eBLAH03=@NUM4 || eBLAH04=@NUM4 || eBLAH05=@NUM4 || eBLAH06=@NUM4 ) THEN
           SET @howmanyknew=@howmanyknew+1;
       END IF;
       # [ NUM5 ]
       IF (eBLAH01=@NUM5 || eBLAH02=@NUM5 || eBLAH03=@NUM5 || eBLAH04=@NUM5 || eBLAH05=@NUM5 || eBLAH06=@NUM5 ) THEN
           SET @howmanyknew=@howmanyknew+1;
       END IF;
       # [ NUM6 ]
       IF (eBLAH01=@NUM6 || eBLAH02=@NUM6 || eBLAH03=@NUM6 || eBLAH04=@NUM6 || eBLAH05=@NUM6 || eBLAH06=@NUM6 ) THEN
           SET @howmanyknew=@howmanyknew+1;
       END IF;
       # [ UPDATE ]
       IF @howmanyknew>0 THEN
           UPDATE test.lotteryTBL SET howmanyknew=@howmanyknew WHERE ID=@ID;
       END IF;
   SET @ID=(SELECT ID FROM test.lotteryTBL AS lt WHERE lt.ID>@ID ORDER BY ID ASC LIMIT 0,1);
   END WHILE;
END

引用自：https://dba.stackexchange.com/questions/107652