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

问题描述

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

理想情况下，我想要做的，例如，试图删除一个记录是任何数量的子表的外键父。外键引发异常，所以我希望能够查看每个外键表并对其进行测试，对引起异常的表和记录数提供有意义的反馈。这将被作为错误返回，以便最终用户可以引用和删除违规的记录。 解决方案

是设置我的数据库包装类，当遇到数据库错误时总是抛出一个异常。所以，例如，我可能有一个名为 MySQL 的类，它具有以下功能：

  public function query（$ query_string）
 {
 $ this-> queryId = mysql_query（$ query_string，$ this-> connectionId）; 
 if（！$ this-> queryId）{
 $ this-> _throwException（$ query_string）; 
} 
 return $ this-> queryId; 
 
 $ b private function _throwException（$ query = null）
 {
 $ msg = mysql_error（）。。Query is：\\\
\\\
 。$查询。 
\\\
\\\
错误号：.mysql_errno（）; 
抛出新的异常（$ msg，mysql_errno（））; 
 
 
 
 $ b 
任何时候查询失败都会抛出一个普通的PHP异常。请注意，我也会在其他地方抛出这些，如 connect（）函数或者 selectDb（）函数，这取决于操作是否成功。
 
 
 
随着这个设置，你很好走。任何您希望可能需要处理数据库错误的地方，请执行以下操作： 
 
 
  //假定$ db已经被设置为MySQL类的实例
 
 try {
 $ db-> query（DELETE FROM parent WHERE id = 123）; 
} catch（Exception $ e）{
 //呃 - 哦，也许外键约束失败了？ 
 if（$ e-> getCode（）=='mysql外键错误代码'）{
 //是的，它失败了。做一些东西。 
 
 
 
 
 
 编辑 
 
 
 
针对以下海报的评论，您可以获得一些有限的信息来帮助诊断外键问题。由code> mysql_error（）返回的失败外键限制创建的错误文本如下所示：
 
 
 <$ p $无法删除或更新父行：
a外键约束失败
（`dbname`.`childtable`，CONSTRAINT`FK_name_1` FOREIGN KEY 
（`fieldName `）REFERENCES`parenttable`（`fieldName`））; 
  
如果您的外键足够复杂，您无法确定可能导致外键错误对于一个给定的查询，那么你可能可以解析这个错误文本，以帮助弄清楚。命令显示引擎INNODB状态返回最近的外键错误的更详细的结果。
 
 
 
你可能不得不自己挖一些东西。下面的查询会给你一个给定表的外键列表，你可以查看信息： 
 
 
  select * from information_schema.table_constraints 
 WHERE table_schema = schema（）AND table_name ='table_name'; 
  
不幸的是，我不认为除了检查错误和约束外，非常仔细。
 
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.
解决方案
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:\n\n".$query.
                "\n\nError number: ".mysql_errno();
    throw new Exception($msg,mysql_errno());
}
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.
    }
}
Edit

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`));
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屋！