在MySQL触发器中使用Prepared语句的替代方法 [英] Alternative to using Prepared Statement in Trigger with MySQL

问题描述

我正在尝试使用以下代码创建一个MySQL插入前触发器，如果​​我能找到一种方法来执行由触发器生成的准备好的语句，它将执行我想要的事情.

I'm trying to create a MySQL Before Insert trigger with the following code which would do what I want it to do if I could find a way to execute the prepared statement generated by the trigger.

是否有其他方法可以从触发器内部执行准备好的语句?谢谢

Are the any alternative ways to execute prepared statements from within triggers? Thanks

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

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

UPDATE / CLARIFICATION: The data will be stored in a table as below.

+------------+-----+------+----------------+
| 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()，然后重新哈希整个字符串，从而为该行生成哈希值.这将创建一个哈希值的运行记录，以供审核/在应用程序的另一部分中使用.

On each insert command the table will generate a Hash value for that row by appeding the pervious row's Hash value to a CONCAT() of the entire new row, then re-hashing the entire string. This will create a running record of Hash values for auditing purposes / use in another part of the application.

我的约束是必须在INSERT之前完成此操作，因为以后不能再更新行.

My constraints are that this has to be done before the INSERT as rows cannot be updated afterwards.

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

UPDATE: I'm currently using the following code until I can find a way to pass the column names to CONCAT dynamically:

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

推荐答案

简而言之，就是您不能在TRIGGER中使用动态SQL.

The short answer is that you can't use dynamic SQL in a TRIGGER.

我对auto_increment值的查询感到困惑，并为ID列分配了一个值.我不明白为什么您需要设置ID列的值.那不是定义为AUTO_INCREMENT的列吗?数据库将处理分配.

I'm confused by the query of the auto_increment value, and assigning a value to the ID column. I don't understand why you need to set the value of the ID column. Isn't that the column that is defined as the AUTO_INCREMENT? The database will handle the assignment.

也不清楚您的查询是否保证返回唯一值，尤其是在运行并发插入时. (我尚未测试，因此可能会起作用.)

It's also not clear that your query is guaranteed to return unique values, especially when concurrent inserts are run. (I've not tested, so it might work.)

但是代码很特殊.

看起来您要完成的工作似乎是从最近插入的行中获取列的值.我认为在查询定义触发器的同一表上存在一些限制. (我肯定知道Oracle中有此功能； MySQL可能会更宽松.)

It looks as if what you're trying to accomplish is to get the value of a column from the most recently inserted row. I think there are some restrictions on querying the same table the trigger is defined on. (I know for sure there is in Oracle; MySQL may be more liberal.)

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

If I needed to do something like that, I would try something like this:

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

 SET NEW.hash = @prev_hash; 

但是同样，我不确定这是否可行(我需要测试).如果它只适用于简单情况，则不能证明它在一直插入的情况下，在扩展插入的情况下都一直有效.

But again, I'm not sure this will work (I would need to test). If it works on a simple case, that's not proof that it works all the time, in the case of concurrent inserts, in the case of an extended insert, et al.

我以这种方式编写了查询，以便可以利用ID列上的索引来执行反向扫描操作.如果它不使用索引，我将尝试重写该查询(可能以JOIN的形式进行，以获得最佳性能.

I wrote the query the way I did so that it can make use of an index on the ID column, to do a reverse scan operation. If it doesn't use the index, I would try rewriting that query (probably as a JOIN, to get the best possible performance.

 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对存储程序的限制

---

Excerpt from MySQL 5.1 Reference Manual
E.1 Restrictions on Stored Programs

SQL准备好的语句(PREPARE，EXECUTE，DEALLOCATE PREPARE)可以是 用于存储过程，但非存储函数或 触发器.因此，存储的函数和触发器无法使用 动态SQL(在其中将语句构造为字符串，然后 执行它们). [原文]

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]

这篇关于在MySQL触发器中使用Prepared语句的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！