数据库-唯一约束冲突的处理

Question

我有一个用户创建屏幕，它记录了各种用户详细信息以及名字和手机号码。我有一个对应的用户表，其中名字和手机号码构成一个复合唯一键。此表中还定义了其他完整性约束。

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

当这种违反发生时，我从MySQL数据库中得到的异常是：

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

有两个选项可以显示有意义的消息（例如：“错误：给定手机号码的用户名已存在，请更改其中一个”）。

选项1：在这个异常的cat块中，解析MySQL异常的消息并查找uk_FIRST_NAME__MOBILE_idx。如果存在，显示上面提到的用户友好的消息。

选项2：编写一个DAO级别的API，将名字和手机号码作为唯一的两个参数，启动数据库查询，看看是否有匹配这个名字/手机组合的现有记录。如果为true，则向用户显示错误消息；否则，运行插入查询将记录的用户插入到USER表中。

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

问：还有比这两个更好的选择吗？如果不是，在上述两种方法中，哪一种是正确的方法？

Answer 1

下面是eggyal使用MySQLi给出的答案的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'];
    }
}

Answer 2

我认为“选项2”（在尝试插入之前手动检查约束）是可怕的，这不仅是因为竞争危险（通过锁定读取可以避免），而且还因为（正如您所注意的）数据库上的额外负载：毕竟，手动检查约束完全否定了在数据库中使用约束的目的和好处。

我同意解析错误消息字符串感觉很“脏”，但是字符串定义得很好。甚至可以引用基础的errmsg.txt或源代码头文件。

从错误消息中提取密钥名称后，可以使用key\u COLUMN\u用法information模式来识别有问题的列：

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;
  }
}