数据库 - 处理唯一约束违规

I have a user creation screen that takes various user details along with first name and mobile number. I have a corresponding USER table in which the First Name and the Mobile number form a composite unique key. There are other integrity constraints defined on this table as well.

我有一个用户创建屏幕,其中包含各种用户详细信息以及名字和手机号码。我有一个相应的USER表,其中First Name和Mobile号形成一个复合唯一键。此表还定义了其他完整性约束。

When user data is entered on the Create User screen that violates this constraint, user needs to be shown an 'user friendly' error message.

在“创建用户”屏幕上输入违反此约束的用户数据时,需要向用户显示“用户友好”错误消息。

When such a violation occurs, the exception that I get from the MySQL database is:

当发生这种违规时,我从MySQL数据库获得的异常是:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1-1' for key `uk_FIRST_NAME__MOBILE_idx`

There are two options to show a meaningful message (for example: "ERROR: User name already exists for the given mobile number, please change either one of them").

有两个选项可以显示有意义的消息(例如:“错误:给定手机号码已存在用户名,请更改其中任何一个”)。

Option 1: In the catch block of this exception, parse the MySQL exception's message and look for 'uk_FIRST_NAME__MOBILE_idx'. If present, show the user friendly message as mentioned above.

选项1:在此异常的catch块中,解析MySQL异常的消息并查找“uk_FIRST_NAME__MOBILE_idx”。如果存在,请显示如上所述的用户友好消息。

Option 2: Write a DAO level API that will take first name and mobile number as the only two parameters, fire a database query to see if there is an existing record matching this first name/mobile combination. If true, show the error message to the user; else, run an insert query to insert the user of record into the USER table.

选项2:编写DAO级API,将名字和手机号码作为唯一的两个参数,触发数据库查询以查看是否存在与此名字/移动组合匹配的现有记录。如果为true,则向用户显示错误消息;否则,运行插入查询以将记录用户插入USER表。

I do NOT like the option 1, as it needs me to 'parse' the exception message, which is not a clean solution. I do NOT like the Option 2 as well, as it needs me to run 'two queries' on the database which is less efficient than the option 1, which is a single query solution.

我不喜欢选项1,因为它需要我“解析”异常消息,这不是一个干净的解决方案。我也不喜欢选项2,因为它需要我在数据库上运行“两个查询”,这比选项1(一个查询解决方案)效率低。

Question: Are there any other options which are better than these two? If not, which one is the right approach among the above two?

问题:还有其他选择比这两个更好吗?如果不是,哪一个是上述两个中的正确方法?

2 个解决方案

#1

I think "option 2" (manually checking constraint before attempting to insert) is horrible, not just because of the race hazard (which could be avoided with locking reads), but also (as you note) because of the additional load on the database: after all, to manually check the constraints completely negates the purpose and benefit of using constraints within the database.

我认为“选项2”(在尝试插入之前手动检查约束)是可怕的,不仅仅是因为种族危险(可以通过锁定读取避免),而且(因为你注意到)因为数据库上的额外负载:毕竟,手动检查约束完全否定了在数据库中使用约束的目的和好处。

I agree that parsing error message strings feels "dirty", but the strings are well defined. One could even refer to the underlying errmsg.txt or source header files.

我同意解析错误消息字符串感觉“脏”,但字符串定义良好。甚至可以引用底层的errmsg.txt或源头文件。

Once one has extracted the key name from the error message, one can use the KEY_COLUMN_USAGE information schema to identify the offending columns:

一旦从错误消息中提取了密钥名称,就可以使用KEY_COLUMN_USAGE信息模式来识别违规列:

public static final int ER_DUP_ENTRY = 1062;
public static final int ER_DUP_ENTRY_WITH_KEY_NAME = 1586;

public static final String REGEX_DUP_ENTRY_WITH_KEY_NAME =
  "Duplicate entry '(.*)' for key '(.*)'";

// ...


try {
// ...
} catch (MySQLIntegrityConstraintViolationException e) {
  switch (e.getErrorCode()) {
    case ER_DUP_ENTRY:
    case ER_DUP_ENTRY_WITH_KEY_NAME:
      Pattern p = Pattern.compile(REGEX_DUP_ENTRY_WITH_KEY_NAME);
      Matcher m = p.matcher(e.getMessage());

      SQLQuery query = session.createSQLQuery(
      " SELECT COLUMN_NAME" +
      " FROM   INFORMATION_SCHEMA.KEY_COLUMN_USAGE" +
      " WHERE  CONSTRAINT_SCHEMA = :schema" +
      "    AND CONSTRAINT_NAME   = :key"
      );
      query.setString("schema", "my_schema");
      query.setString("key"   , m.group(2));

      showDuplicateError(query.list());

      break;
  }
}

#2

-1

Here's a PHP version of eggyal's answer, using MySQLi.

这是使用MySQLi的eggyal回答的PHP版本。

// Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY)               Message: Duplicate entry '%s' for key %d
// Error: 1586 SQLSTATE: 23000 (ER_DUP_ENTRY_WITH_KEY_NAME) Message: Duplicate entry '%s' for key '%s'
if($mysqli->errno === 1062 || $mysqli->errno === 1586)
{
    if(preg_match("/Duplicate entry '(.*)' for key '(.*)'/", $mysqli->error, $matchArray) === 1)
    {
        $duplicatedValue = $matchArray[1];
        $uniqueKeyName = $matchArray[2];

        if(!($stmt = $mysqli->prepare('SELECT COLUMN_NAME'
                                    . '  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE'
                                    . ' WHERE CONSTRAINT_SCHEMA = ?'
                                    . '   AND CONSTRAINT_NAME = ?')))
        {
            die;    // Error? Check $mysqli->errno and $mysqli->error;
        }
        $schemaName = // Name of the schema (string).
        if(!$stmt->bind_param('ss', $schemaName, $uniqueKeyName))
        {
            die;    // Error? Check $mysqli->errno and $mysqli->error;
        }
        if(!$stmt->execute())
        {
            die;    // Error? Check $mysqli->errno and $mysqli->error;
        }
        $res = $stmt->get_result();
        if(!$res)
        {
            die;    // Error? Check $mysqli->errno and $mysqli->error;
        }
        $row = $res->fetch_assoc();
        if($row === null)
        {
            die;    // No results?
        }
        $columnName = $row['COLUMN_NAME'];
    }
}

#1