mysql 5.1发出错误信号导致PDO异常 [英] mysql 5.1 signal an error to cause PDO exceptions

问题描述

我知道mysql 5.5允许使用SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: ...';引发用户定义的错误.如果在某些表的BEFORE INSERT TRIGGER中放置了INSERT操作，此错误将停止该操作.而且PDO捕获PDOException并输出errorinfo()也很方便，即SIGNAL SQLSTATE中定义的MESSAGE_TEXT.
但是，我租用的服务器上的mysql版本是mysql 5.1.而且我想知道如何使用mysql 5.5中的SIGNAL SQLSTATEMENT之类的功能引发用户定义的错误.

I know mysql 5.5 allows using SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error: ...'; to raise a user defined error. And this error will stop an INSERT operation if it was put in the BEFORE INSERT TRIGGER on some tables. And it's also convenient for PDO to catch the PDOException and output the errorinfo() i.e. MESSAGE_TEXT defined in SIGNAL SQLSTATE.
However, the version of mysql on the server I rent is mysql 5.1. And I want to know how can I raise a user defined error with the features like the SIGNAL SQLSTATEMENT in mysql 5.5.

1. 在before insert触发器中中断insert操作
2. 可能会被PDO
捕获

1. interrupt a insert operation when it's in before insert trigger
2. can be caught by PDO

我发现了一些类似问题的话题，并且已经尝试过:

I've found some topics on similar problems, and I've tried these:

• 调用一个不存在的过程

• call a nonexist procedure

致电`sp_raise_error`;

call `sp_raise_error`;

使用函数引发错误

https://blogs.oracle.com/svetasmirnova/entry/how_to_raise_error_in

PDO都无法抓住这两个.那么有什么解决方案呢? (我在MySQL5.5上进行了测试)

Both can't be caught by PDO. So what's a solution? ( I tested on MySQL5.5 )

推荐答案

如果在较低版本的MySQL(如5.1)中使用SIGNAL SQLSTATE，则会出现1064错误.因此，为了使用SIGNAL SQLSTATE这样的功能，您可以尝试以下步骤.

If you use SIGNAL SQLSTATE in a lower version of MySQL (like 5.1), you will get a 1064 error. So, in order to use the function like SIGNAL SQLSTATE does, you can try the following steps.

CREATE TABLE IF NOT EXISTS `TBL_DUMMY`  
(
    `error` VARCHAR(256)
);

2.在TBL_DUMMY上创建INSERT触发器之前

delimiter $$  
CREATE TRIGGER `TRIG_BI_DUMMY` BEFORE INSERT ON `TBL_DUMMY`  
    FOR EACH ROW
BEGIN  
    SET NEW = NEW.`error`;
END $$  

3.创建名为"SP_RAISE_ERROR"的过程

delimiter $$  
CREATE PROCEDURE `SP_RAISE_ERROR` (IN P_ERROR VARCHAR(256))  
BEGIN  
    DECLARE V_ERROR VARCHAR(300);
    SET V_ERROR := CONCAT('[ERROR: ', P_ERROR, ']');
    INSERT INTO `TBL_DUMMY` VALUES (V_ERROR);
END $$

4.用法

只需执行SP_RAISE_ERROR而不是SIGNAL SQLSTATE.例如，CALL SP_RAISE_ERROR ('Password incorrect.')将引发异常，并且消息为:

4. Usage

Just execute SP_RAISE_ERROR instead of SIGNAL SQLSTATE. For instance, CALL SP_RAISE_ERROR ('Password incorrect.') will throw an exception and the message is:

0 15:40:23 CALL SP_RAISE_ERROR ('Password incorrect.') Error Code: 1231. Variable 'new' can't be set to the value of '[ERROR: Password incorrect.]'.

您可以在过程中使用它:

And you can use it in procedures:

IF V_ID IS NOT NULL AND V_ID <> P_ID THEN  
    CALL `SP_RAISE_ERROR` ('Title duplicated.');
END IF; 

然后，您可以从外部程序中的消息中提取错误文本.

After that, you can extract error texts from the messages in an external program.

这篇关于mysql 5.1发出错误信号导致PDO异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！