Mysql
如何在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>