mysql 触发器 获取查询结果_从MySQL触发器中获取准备好的查询结果？

你好,我是MySQL触发器的新手.我想从准备好的查询中获取结果,但无法看到如何使以下代码工作：

BEGIN

DECLARE un_quer varchar(255);

DECLARE res integer;

IF(NEW.contact_id IS NULL) THEN

IF(NEW.name IS NULL OR NEW.email IS NULL) THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Forbidden: ... information not sufficient.';

END IF;

END IF;

IF(NEW.name IS NULL OR NEW.email IS NULL) THEN

IF(NEW.contact_id IS NULL) THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Forbidden: ... information not sufficient.';

END IF;

END IF;

SET un_quer = CONCAT('SELECT COUNT(*) FROM tender_tenderer WHERE tender_id=',NEW.tender_id);

IF(NEW.contact_id IS NULL) THEN

SET un_quer = CONCAT(un_quer,' AND contact_id IS NULL');

ELSE

SET un_quer = CONCAT(un_quer,' AND contact_id = ',NEW.contact_id);

END IF;

IF(NEW.name IS NULL) THEN

SET un_quer = CONCAT(un_quer,' AND name IS NULL');

ELSE

SET un_quer = CONCAT(un_quer,' AND name = "',NEW.name,'"');

END IF;

PREPARE stmt1 FROM un_quer;

SET res = (EXECUTE stmt1);

IF (0 < res) THEN

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Forbidden: ...';

END IF;

END

我认为大多数代码都有效但行“SET res =(EXECUTE stmt1);”会产生错误.如何将stmt1的结果选择到变量$res中,以便我可以在IF子句中使用得到的整数进行比较？

注意：基本问题是我想在Mysql InnoDB中使用“唯一约束”,它使用NULL值.

解决方法:

即使此查询仅返回一个值,也无法将查询结果设置为变量.在queries returning one single row中使用b.b3rn4rd’s语法.(fyi,迭代包含多行的查询结果需要使用CURSOR)

SELECT COUNT(*) INTO res

FROM tender_tenderer

WHERE tender_id = NEW.tender_id

AND contact_id <=> NEW.contact_id

AND name <=> NEW.name;

IF (0 < res) THEN

...

标签：mysql,sql,triggers

来源： https://codeday.me/bug/20190624/1280617.html