MySQL如何检查查询是否成功执行？

I have 3 queries:

我有3个查询:

1. INSERT, 2. UPDATE and 3. DELETE

I want to know is there anyway so that i can check directly (i.e., without run another query for check) if query is executed successfully or not? if it doesn't (i.e., returned some error/exception) then I want to save that error/exception into some variables?

我想知道是否有任何方式,以便我可以直接检查(即,如果查询执行成功,可以不运行另一个检查查询)?如果没有(即返回一些错误/异常),那么我想将该错误/异常保存到某些变量中?

For Example, Below are my queries:

例如,以下是我的查询:

INSERT Query:

INSERT INTO `users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);

UPDATE Query:

UPDATE `test_db2`.`users`
        SET name = NEW.name,
            age = NEW.age
        WHERE id = NEW.id;

DELETE Query:

DELETE FROM `test_db2`.`users`
        WHERE id = OLD.id;

Thanks.

Here's the simple INSERT trigger using Handler (I tried):

这是使用Handler的简单INSERT触发器(我试过):

DELIMITER //

-- TRIGGER FOR INSERT
DROP TRIGGER IF EXISTS `test_db1_users_ai`;
CREATE TRIGGER `test_db1_users_ai` AFTER INSERT ON `users` FOR EACH ROW 
BEGIN
    -- Perform the insert
    INSERT INTO `test_db2`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);
    -- Handler if this ^ above INSERT fails. 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
        --SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
        INSERT INTO `test_db1`.`errors` (error_code, error_message) VALUES (@errno, @text);
        --SELECT @full_error;
    END;
END; //

2 个解决方案

#1

In MySQL you can declare error / warning handlers that can capture various errors and warnings. If you combine a handler with get diagnostics command, then you can capture even the exact error / warning in a general handler as well.

在MySQL中,您可以声明可以捕获各种错误和警告的错误/警告处理程序。如果将处理程序与get diagnostics命令结合使用,那么您甚至可以捕获常规处理程序中的确切错误/警告。

I found the following blog post on Improve your Stored Procedure Error Handling with GET DIAGNOSTICS that summarises how to capture the error message in stored programs within MySQL:

我发现以下关于使用GET DIAGNOSTICS改进存储过程错误处理的博文,总结了如何捕获MySQL中存储程序中的错误消息:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
    SELECT @full_error;
END;

Obviously, you can log the error in @full_error in a table, if required. If no error handlers kick in, then the sql statement successfully ran.

显然,如果需要,您可以在表中记录@full_error中的错误。如果没有错误处理程序启动,则sql语句成功运行。

With inserts and updates you can also use the row_count() function to determine the number of records affected. If this number is 0, that could be an indication of an error.

通过插入和更新,您还可以使用row_count()函数来确定受影响的记录数。如果此数字为0,则可能表示存在错误。

#2

Please refer to over here: https://*.com/a/37853801/5228251

请参考这里:https://*.com/a/37853801/5228251

Check this out below (i.e., Copied from above source):

请在下面查看(例如,从上面复制来源):

-- TRIGGER FOR INSERT
DROP TRIGGER IF EXISTS `test_db1_users_ai`;
CREATE TRIGGER `test_db1_users_ai` AFTER INSERT ON `users` FOR EACH ROW 
BEGIN
    -- Declare variables to hold diagnostics area information
    DECLARE errorCode CHAR(5) DEFAULT '00000';
    DECLARE errorMessage TEXT DEFAULT '';

    -- Declare exception handler for failed insert
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
    END;

    -- Perform the insert
    INSERT INTO `test_db2`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);

    -- Check whether the insert was successful
    IF errorCode != '00000' THEN
        INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'insert', NEW.id, 'test_db2', 'users');
    END IF;
END; //

#1