mysql - 在 MySQL 的触发器中使用准备好的语句的替代方法

Question

我正在尝试使用以下代码创建一个 MySQL Before Insert 触发器，如果​​我能找到一种方法来执行触发器生成的准备好的语句，它将执行我希望它执行的操作。

是否有任何替代方法可以从触发器中执行准备好的语句？谢谢

BEGIN
    SET @CrntRcrd = (SELECT AUTO_INCREMENT FROM information_schema.TABLES 
              WHERE TABLE_SCHEMA=DATABASE()
              AND TABLE_NAME='core_Test');

  SET @PrevRcrd = @CrntRcrd-1;

    IF (NEW.ID IS NULL) THEN
        SET NEW.ID = @CrntRcrd;
    END IF;

    SET @PrevHash = (SELECT Hash FROM core_Test WHERE Record=@PrevRcrd);

    SET @ClmNms = (SELECT CONCAT('NEW.',GROUP_CONCAT(column_name 
                  ORDER BY ORDINAL_POSITION SEPARATOR ',NEW.'),'')
                  FROM information_schema.columns 
                  WHERE table_schema = DATABASE() 
                  AND table_name = 'core_Test');

  SET @Query = CONCAT("SET @Query2 = CONCAT_WS(',','",@PrevHash,"','", @CrntRcrd, "',", @ClmNms, ");");

  PREPARE stmt1 FROM @Query;
  EXECUTE stmt1;
  DEALLOCATE PREPARE stmt1; 

  SET NEW.Hash = @Query2;
END

更新/澄清:数据将存储在下表中。

+------------+-----+------+----------------+
| Record (AI)| ID  | Data | HASH           |
+------------+-----+------+----------------+
| 1          | 1   | ASDF | =DHFBGKJSDFHBG | (Hash Col 1)
| 2          | 2   | NULL | =UEGFRYJKSDFHB | (Hash Col 1 + Col 2)
| 3          | 1   | VBNM | =VKJSZDFVHBFJH | (Hash Col 2 + Col 3)
| 4          | 4   | TYUI | =KDJFGNJBHMNVB | (Hash Col 3 + Col 4)
| 5          | 5   | ZXCV | =SDKVBCVJHBJHB | (Hash Col 4 + Col 5)
+------------+-----+------+----------------+

在每个插入命令中，表将通过将前一行的哈希值应用到整个新行的 CONCAT() 来为该行生成一个哈希值，然后重新哈希整个字符串。这将创建哈希值的运行记录，用于审计目的/在应用程序的另一部分使用。

我的约束是这必须在 INSERT 之前完成，因为之后无法更新行。

更新:我目前正在使用以下代码，直到找到一种方法将列名动态传递给 CONCAT:

BEGIN

  SET @Record = (
    SELECT AUTO_INCREMENT FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA=DATABASE()
    AND TABLE_NAME='core_Test'    #<--- UPDATE TABLE_NAME HERE
  );
  SET @PrevRecrd = @Record-1;

    IF (new.ID IS NULL) THEN
    SET new.ID = @Record;
  END IF;

  SET @PrevHash = (
    SELECT Hash FROM core_Test    #<--- UPDATE TABLE_NAME HERE
    WHERE Record=@PrevRecrd
  );

  SET new.Hash = SHA1(CONCAT_WS(',',@PrevHash, @Record,
    /* --- UPDATE TABLE COLUMN NAMES HERE (EXCLUDE "new.Record" AND "new.Hash") --- */
    new.ID, new.Name, new.Data
  ));

END

Answer 1

简短的回答是您不能在 TRIGGER 中使用动态 SQL。

我对 auto_increment 值的查询以及为 ID 列赋值感到困惑。我不明白为什么需要设置 ID 列的值。那不是定义为 AUTO_INCREMENT 的列吗？数据库将处理分配。

您的查询是否保证返回唯一值也不清楚，尤其是在运行并发插入时。 (我没有测试过，所以它可能会起作用。)

但是代码很奇特。

看起来您要完成的似乎是从最近插入的行中获取列的值。我认为查询定义触发器的同一个表有一些限制。 (我确信在 Oracle 中有；MySQL 可能更自由。)

如果我需要做那样的事情，我会尝试这样的事情:

 SELECT @prev_hash := t.hash AS prev_hash 
   FROM core_Test t
  ORDER BY t.ID DESC LIMIT 1;

 SET NEW.hash = @prev_hash; 

但是，我不确定这是否有效(我需要测试)。如果它在一个简单的情况下有效，那并不能证明它一直有效，在并发插入的情况下，在扩展插入的情况下，等等。

我按照自己的方式编写了查询，以便它可以利用 ID 列上的索引来执行反向扫描操作。如果它不使用索引，我会尝试重写该查询(可能作为 JOIN，以获得最佳性能。

 SELECT @prev_hash := t.hash AS prev_hash
   FROM ( SELECT r.ID FROM core_Test r ORDER BY r.ID DESC LIMIT 1 ) s
   JOIN core_Test t
     ON t.ID = s.ID

---

摘自MySQL 5.1引用手册
E.1对存储程序的限制

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them). [sic]