mysql - MySQL 触发器上 MySQL 变量的算术运算

Question

我有一个表(test_table)，其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的，还有一个字段(字段 D)，我想从现有表(store_table)插入其值，但在插入前者(A、B 和 C)之后以某种方式随机。我想使用插入后触发器在每次插入其他字段的值时在字段 D 中插入值。我的问题是我不确定用于声明和设置变量和算术运算的值的正确语法。以下是我尝试过的方法以及随后生成的错误。

DELIMITER $$
CREATE TRIGGER afterinsert_test_table_ABC
AFTER INSERT
ON test_table
BEGIN 


    SET @Id:=new.Id;


    SET @Id := SELECT ((7*@Id + 19) % 11);
    if @Id==0 THEN
        INSERT INTO test_table(D)VALUE(SELECT X FROM store_table WHERE store.Id=LAST_INSERT_ID());
      ELSE
          INSERT INTO test_table(D)VALUE(SELECT X FROM store_table WHERE store_table.Id=@Id); 
        END IF;


END$$
DELIMITER ;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SET @Id:=new.Id; SET @I' at line 4

Answer 1

甚至消除你的错误

您不能插入到同一个表中。(这会导致自身成为一个新的插入触发器

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html

DELIMITER $$
CREATE TRIGGER afterinsert_test_table_ABC
AFTER INSERT
ON test_table
FOR EACH ROW 
BEGIN 

    SET @Id:=new.Id;
    SET @Id :=  ((7*@Id + 19) % 11);
    if @Id = 0 THEN
        INSERT INTO second_test_table (D) SELECT X FROM store_table WHERE store.Id=LAST_INSERT_ID();
    ELSE
        INSERT INTO second_test_table (D)  SELECT X FROM store_table WHERE store_table.Id=@Id; 
    END IF;
END$$
DELIMITER ;