mysql 预写策略wal,在MySQL触发器中使用预准备语句的替代方法

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

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

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' #

);

SET @PrevRecrd = @Record-1;

IF (new.ID IS NULL) THEN

SET new.ID = @Record;

END IF;

SET @PrevHash = (

SELECT Hash FROM core_Test #

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.

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

&LT剪断&GT

SQL预准备语句(PREPARE,EXECUTE,DEALLOCATE PREPARE)可以在存储过程中使用,但不能用于存储函数或触发器.因此,存储的函数和触发器不能使用动态SQL(将语句构造为字符串然后执行它们).

&LT /剪断&GT

标签：mysql,prepared-statement,triggers

来源： https://codeday.me/bug/20191012/1902533.html