如何在mysql觸發器聲明中使用變數作為欄位名的一部分

這是我想要做的（我的觸發器的一部分）：

BEGIN
DECLARE updatecount INT default 0;
declare number int;
declare column varchar(10);
declare i int default 1;
declare j int default 1;
while i<=6 do
   set column = "num"+i; /*will be num1 num2, so on*/
   set number = NEW.@column; /* will represent NEW.num1, num2 so on*/

我知道最後兩行不正確。但我想讓你們明白我想做什麼。誰能告訴我正確的語法？謝謝。

編輯：

我想用這個觸發器做的是當一個新行添加到 中時resultsTBL，我想檢查 中的所有行guessesTBL以找出其中有多少是相同的。

例如，我們有 2 行guessesTBL（始終按升序排列）：

num1 num2 num3 num4 num5 num6
1    5    10   15   20   26
4    8    12   16   25   40

然後我們在其中添加一個新行resultsTBL（始終按升序排列）：

num1 num2 num3 num4 num5 num6
1    8    17   20   25   40

之後，觸發器會將新添加的行與以下所有行進行比較guessesTBL：

for the 1st row the result is: 2
for the 2nd row the result is: 3

虛擬碼：

CREATE DEFINER = CURRENT_USER TRIGGER `myDB`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
for each field in resultsTBL (num1, num2, ... num6)
 number= num1...num6
 for each field in guessesTBL (num1, num2, ... num6)
   if number == guessesTBL.num1,num2,...num6
     counter++

你的語法是錯誤的。我不認為在 a 中放置一個循環TRIGGER是一個好主意，但我以 aStored Procedure為例：

程序：

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN

SET @counter=0;
SET @column='';

WHILE (@counter < 6) DO
   SET @counter=@counter+1;
   set @column = CONCAT("num",@counter); /*will be num1 num2, so on*/
   SELECT CONCAT(" SET @number = NEW.",@column,"; ");

END WHILE;
END$$

DELIMITER ;

輸出測試：

mysql> call test.new_procedure();
+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num1;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num2;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num3;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num4;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num5;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

+--------------------------------------------+
| CONCAT(" SET @number = NEW.",@column,"; ") |
+--------------------------------------------+
|  SET @number = NEW.num6;                   |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

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