如何在SQL中让MySQL抛出有条件的运行时异常

Question

我正在编写一个迁移测试，以确保迁移创建了一个用户。如果该用户不存在，则测试应该抛出一个错误。起初，我认为我可以只使用除零错误来获得我想要的结果：

SET SESSION sql_mode = 'error_for_division_by_zero';
SELECT 1/COUNT(*) FROM mysql.user WHERE user = 'foo';

然而，如果foo不存在，这并不会引发错误。事实证明，error_for_division_by_zero仅影响INSERT和UPDATE语句。

然后，我想也许我可以只调用一些带有错误数量参数的函数：

SELECT IF(COUNT(*) = 1, 1, date_format(1, 2, 3))
  FROM mysql.user WHERE user = 'foo';

即使foo存在，但由于解析器注意到参数计数不正确，因此甚至在该情况下也会失败。

我可以编写一个模拟引发异常的函数，但我试图避免这样做。是否有没有办法强制MySQL有条件地抛出运行时异常？

- theory

---

听起来像是中间层的功能。我不会让数据库做这样的事情，因为这并不是“异常”的情况。在数据库中不存在的用户不是错误，而是潜在的新用户。 - duffymo

请查看这个问题。 - Vatev

@Vatev，我看不到如何使SIGNAL仅在SQL条件（IF()，CASE或类似条件）的情况下运行。你知道有没有例子吗？ - theory

那里还有一个到手册的链接。 - Vatev

Answer 1

很遗憾，这无法完成而不使用存储过程或函数。但是，我已经想出如何在我的应用程序中支持函数。借鉴这个答案的基本过程思路，我想出了以下代码：

DELIMITER |

CREATE FUNCTION checkit(doit INTEGER, message VARCHAR(256)) RETURNS INTEGER DETERMINISTIC
BEGIN
    IF doit IS NULL OR doit = 0 THEN
        SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = message;
    END IF;
    RETURN doit;
END;
|

这个想法是将函数用于触发器，就像CHECK约束或者SQL语句中的内联使用。回到我最初需要的情况——如果用户不存在，我现在可以像这样使用checkit()函数：

SELECT checkit(COUNT(*), 'User "foo" does not exist')
  FROM mysql.user WHERE user = 'foo';

如果用户 foo 存在，此查询将返回一个整数。如果用户不存在，则会抛出带有定义消息的错误。

想要将该函数用于检查约束吗？这里是一个示例（模仿 这个答案），向 @rouland-bouman 致敬：

CREATE TRIGGER mytabletriggerexample BEFORE INSERT FOR EACH ROW
BEGIN
    SET @dummy := checkit(
        NEW.important_value) >= (fancy * dancy * calculation),
        'Your meaningful error message goes here'
    );
END;

我宁愿使用DO，而不是设置一个虚拟变量，但MySQL的一个错误导致这种方法不能工作，唉。

- theory

Answer 2

您可以通过运行返回多于一个行但预期仅有一个行的子查询来抛出异常，而不需要使用函数/过程。通常情况下，数据库中已经有一个表来处理这种情况，但我在这里提供了一个表格：

create table t (a int);
insert into t values(1);
insert into t values(1);
select * from t;

下面的选择语句会引发异常，因为外部选择语句期望一个值，但内部选择语句返回多个值。

select (select a from t);

你可以将其应用到你的情况中：

select case when 
    2*2 = 4 
    then 'OK' 
    else (select a from t)
    end;

select case when 
    2*2 = 5 
    then 'OK' 
    else (select a from t)
    end;

请参见：https://dba.stackexchange.com/questions/78594/how-to-conditionally-raise-an-error-in-mysql-without-stored-procedure。

- riskop

Answer 3

大卫，

你为什么需要这个过程？你可以在函数内部发出SIGNAL（并且你仍然可以在触发器中调用该函数）

delimiter go

create function f_raise(p_message_text varchar(255)) 
returns int 
begin 
  signal sqlstate '45000' set message_text = p_message_text; 
  return null; 
end;
go

select f_raise('bla');
go

ERROR 1644 (45000): bla

- Roland Bouman

---

根据我们在Twitter上的聊天，为了让其他人也知道，触发器需要执行f_raise()，但是DO似乎会吞掉异常。看起来像是一个错误。如果你不需要在触发器中使用它，那么这很完美，但这种做法有点违背了使用它来模拟CHECK约束的初衷。 - theory

一点说明：只要将函数分配给一个变量，就仍然可以在触发器中使用该函数。例如：SET v_dummy = f_raise('bla'); - Roland Bouman

MySQL和“dummy”这个词有什么关系？;-P - theory

更新了我的答案，以消除该过程。谢谢！ - theory