创建自定义错误消息MySQL [英] Create custom error message MySQL

问题描述

在MySQL中，如何为此错误消息创建自定义消息：

In MySQL, how to create custom message for this error message:

无法删除或更新父行：外键约束失败
（数据库。 jenis_fasum ，CONSTRAINT jenis_fasum_ibfk_1 FOREIGN KEY
（ id_kategori ）参考 kategori_fasum （ id_kategori ））

Cannot delete or update a parent row: a foreign key constraint fails (database.jenis_fasum, CONSTRAINT jenis_fasum_ibfk_1 FOREIGN KEY (id_kategori) REFERENCES kategori_fasum (id_kategori))

使用触发器吗？有人可以提供一个例子吗？

Using trigger maybe? Can someone provide an example?

感谢您的帮助

推荐答案

我不认为它可以在 TRIGGER 中完成，但它可以通过MySQL 5.5中的存储过程来完成。

I don't think it can be done in a TRIGGER, but it can be done with a stored procedure in MySQL 5.5.

以下是默认的错误信息：

Here's the default error message:

mysql> INSERT INTO area SET location_id = 'invalid';
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`rates`.`area`, CONSTRAINT `area_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`))

mysql> SHOW ERRORS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                              |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1452 | Cannot add or update a child row: a foreign key constraint fails (`rates`.`area`, CONSTRAINT `area_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这里是我们的存储过程：

And here's our stored procedure:

DROP PROCEDURE IF EXISTS test1;

DELIMITER //

CREATE PROCEDURE test1()
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '23000'
  BEGIN
    SIGNAL SQLSTATE '23000' SET 
      MYSQL_ERRNO = 1452,
      MESSAGE_TEXT = 'Yo! Error 23000!';
  END;

  INSERT INTO area SET location_id = 'invalid';
END;
//

DELIMITER ;

这是我们的自定义错误信息：

And here's our custom error message:

mysql> CALL test1();
ERROR 1452 (23000): Yo! Error 23000!

mysql> SHOW ERRORS;
+-------+------+------------------+
| Level | Code | Message          |
+-------+------+------------------+
| Error | 1452 | Yo! Error 23000! |
+-------+------+------------------+
1 row in set (0.00 sec)

顺便问一下，你为什么要这样？

By the way, why do you want this?

这篇关于创建自定义错误消息MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！