你好,我是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)

但是,此处不需要准备好的声明,您可以使用null-safe comparison operator：

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
...