在 PHP 中处理外键异常 [英] Handling foreign key exceptions in PHP

问题描述

在 PHP 中处理 mysql 数据库上的外键异常的最佳方法是什么?有没有可以用来简化任何代码的mysql类?

What is the best way in PHP to handle foreign key exceptions on a mysql database? Is there a mysql class that can be used to simplify any code?

理想情况下，作为示例，我想做的是尝试删除一条记录，该记录是任意数量子表的外键父项.外键抛出异常，所以我希望能够查看每个外键表并对其进行测试，对表和导致异常的记录数给出有意义的反馈.然后这将作为错误返回，以便最终用户可以引用和删除有问题的记录.

Ideally, what I want to do, as an example, is to try to delete a record where it is the foreign key parent to any number of child tables. The foreign key throws the exception, so then I would like to be able to look at each foreign key table and test it, giving meaningful feedback on the tables and number of records causing the exception. This would then be returned as the error so the end user can reference and delete the offending records.

推荐答案

我处理这个问题的方法是设置我的数据库包装类在遇到数据库错误时总是抛出异常.因此，例如，我可能有一个名为 MySQL 的类，它具有以下功能:

The way I handle this is to set up my database wrapper class to always throw an exception when you encounter a database error. So, for instance, I might have a class called MySQL with the following functions:

public function query($query_string)
{
    $this->queryId = mysql_query($query_string,$this->connectionId);
    if (! $this->queryId) {
        $this->_throwException($query_string);
    }
    return $this->queryId;
}

private function _throwException($query = null)
{
    $msg = mysql_error().".  Query was:

".$query.
                "

Error number: ".mysql_errno();
    throw new Exception($msg,mysql_errno());
}

任何时候查询失败，都会抛出一个常规的 PHP 异常.请注意，我也会从其他地方抛出这些，例如 connect() 函数或 selectDb() 函数，具体取决于操作是否成功.

Any time a query fails, a regular PHP exception is thrown. Note that I would throw these from within other places too, like a connect() function or a selectDb() function, depending on whether the operation succeeded or not.

有了这个设置，你就可以开始了.任何您认为可能需要处理数据库错误的地方，请执行以下操作:

With that set up, you're good to go. Any place you expect that you might need to be handling a database error, do something like the following:

//assume $db has been set up to be an instance of the MySQL class

try {
    $db->query("DELETE FROM parent WHERE id=123");
} catch (Exception $e) {
    //uh-oh, maybe a foreign key restraint failed?
    if ($e->getCode() == 'mysql foreign key error code') {
        //yep, it failed.  Do some stuff.
    }
}

编辑

针对下面发帖者的评论，您可以获得一些有限的信息来帮助诊断外键问题.由失败的外键限制创建并由 mysql_error() 返回的错误文本如下所示:

In response to the poster's comment below, you have some limited information available to you to help diagnose a foreign key issue. The error text created by a failed foreign key restraint and returned by mysql_error() looks something like this:

Cannot delete or update a parent row:
a foreign key constraint fails
(`dbname`.`childtable`, CONSTRAINT `FK_name_1` FOREIGN KEY
(`fieldName`) REFERENCES `parenttable` (`fieldName`));

如果您的外键足够复杂，以至于您无法确定给定查询可能导致外键错误的原因，那么您可能可以解析此错误文本以帮助找出答案.命令 SHOW ENGINE INNODB STATUS 也会返回更详细的最新外键错误结果.

If your foreign keys are complex enough that you can't be sure what might cause a foreign key error for a given query, then you could probably parse this error text to help figure it out. The command SHOW ENGINE INNODB STATUS returns a more detailed result for the latest foreign key error as well.

否则，您可能不得不自己进行一些挖掘.以下查询将为您提供给定表上的外键列表，您可以检查该列表以获取信息:

Otherwise, you're probably going to have to do some digging yourself. The following query will give you a list of foreign keys on a given table, which you can examine for information:

select * from information_schema.table_constraints
WHERE table_schema=schema() AND table_name='table_name';

不幸的是，除了非常仔细地检查错误和约束之外，我认为您的解决方案没有灵丹妙药.

Unfortunately, I don't think there's a magic bullet to your solution other than examining the errors and constraints very carefully.

这篇关于在 PHP 中处理外键异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！