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

Question

你好，我对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中有一个“唯一约束”，它可以使用空值。

Answer 1

即使此查询只返回一个值，也不能将查询结果SET到变量。将B.b3rn4rd语法与返回一行的查询一起使用。(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
...

Answer 2

使用INTO语句。

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