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.
- 在
before insert
触发器中中断insert
操作 - 可能会被
PDO
捕获
- interrupt a
insert
operation when it's inbefore insert
trigger - 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屋!