gpt4 book ai didi

mysql - 创建自定义错误消息 MySQL

转载 作者:可可西里 更新时间:2023-11-01 07:08:22 24 4
gpt4 key购买 nike

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

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))

也许使用触发器?有人可以举个例子吗?

谢谢你的帮助

最佳答案

我不认为它可以在 TRIGGER 中完成,但可以在 MySQL 5.5 中使用存储过程来完成。

这是默认的错误信息:

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)

这是我们的存储过程:

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 ;

这是我们的自定义错误消息:

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)

顺便问一下,你为什么要这个?

关于mysql - 创建自定义错误消息 MySQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11752745/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com