raise error mysql引发异常

来源：互联网发布：邮件地址搜索软件编辑：程序博客网时间：2024/06/12 00:51

一、MySQL 5.5

mysql> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Custom error';ERROR 1644 (45000): Custom error

二、MySQL 早期版本

DROP PROCEDURE IF EXISTS raise_application_error;DROP PROCEDURE IF EXISTS get_last_custom_error;DROP TABLE IF EXISTS RAISE_ERROR;DELIMITER $$CREATE PROCEDURE raise_application_error(IN CODE INTEGER, IN MESSAGE VARCHAR(255)) SQL SECURITY INVOKER DETERMINISTICBEGIN  CREATE TEMPORARY TABLE IF NOT EXISTS RAISE_ERROR(F1 INT NOT NULL);  SELECT CODE, MESSAGE INTO @error_code, @error_message;  INSERT INTO RAISE_ERROR VALUES(NULL);END;$$CREATE PROCEDURE get_last_custom_error() SQL SECURITY INVOKER DETERMINISTICBEGIN  SELECT @error_code, @error_message;END;$$DELIMITER ; 使用如下：CALL raise_application_error(1234, 'Custom message');CALL get_last_custom_error();例子：DROP TABLE IF EXISTS ex1;DROP TRIGGER IF EXISTS ex1_bi;DROP TRIGGER IF EXISTS ex1_bu;CREATE TABLE ex1(only_odd_numbers INT UNSIGNED);DELIMITER $$CREATE TRIGGER ex1_bi BEFORE INSERT ON ex1 FOR EACH ROWBEGIN   IF NEW.only_odd_numbers%2 != 0 THEN    CALL raise_application_error(3001, 'Not odd number!');  END IF;END$$CREATE TRIGGER ex1_bu BEFORE UPDATE ON ex1 FOR EACH ROWBEGIN   IF NEW.only_odd_numbers%2 != 0 THEN    CALL raise_application_error(3001, 'Not odd number!');  END IF;END$$DELIMITER ;使用:mysql> INSERT INTO ex1 VALUES(2);Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO ex1 VALUES(3);ERROR 1048 (23000): Column 'F1' cannot be nullmysql> CALL get_last_custom_error();+-------------+-----------------+| @error_code | @error_message  |+-------------+-----------------+| 3001        | Not odd number! | +-------------+-----------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> SELECT \* FROM ex1;+------------------+| only_odd_numbers |+------------------+|                2 | +------------------+1 row in set (0.00 sec)

0 0