检测由于违反唯一约束而导致的 mysql 更新/插入失败 [英] Detect mysql update/insertion failure due to violated unique constraint

问题描述

这有点类似于这个问题:

This is kind of similar to this question:

PHP MySQL INSERT 由于唯一约束而失败

但我有不同的看法.假设我有一张只有一列的表格.该列的名称是title"，并且在其上放置了一个唯一约束.

but I have a different twist. Let's say I have a table with only one column. The column's name is "title" and it has a unique constraint placed on it.

首先我插入一行，其中 title = "something".下次我尝试插入某物"时，由于唯一的键约束(这很好)，它会失败.我想要做的是让它失败，并检查 mysql 提供的错误代码以确保它由于唯一键约束而失败.(即让数据库处理唯一性，我只处理错误代码并在结果返回时告诉用户标题已经存在).

First I insert a row where title = "something". The next time I try to insert "something" it will fail due to a unique key constraint (which is good). What I'd like to do is allow it to fail, and check the error code provided by mysql to ensure it failed due to a unique key constraint. (i.e. let the database handle the uniqueness, and I just handle the error code and tell the user that title already exists when the result comes back).

有没有办法做到这一点?

Is there a way to do this?

推荐答案

http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

http://php.net/manual/en/function.mysql-errno.php

我过去不得不这样做，这并不好玩:

I've had to do this in the past, and it's not fun:

if( mysql_errno() == 1062) {
    // Duplicate key
} else {
    // ZOMGFAILURE
}

关于编程风格的说明(来自 jensgram 的致谢://stackoverflow.com/a/3146986/3184087">这个答案)
您应该始终设法避免使用幻数.相反，您可以将已知错误代码 (1062) 分配给一个常量(例如 MYSQL_CODE_DUPLICATE_KEY).这将使您的代码更易于维护，因为 if 语句中的条件在 1062 的含义从记忆中消失后的几个月内仍然可读:)

A note on programming style (Credits to jensgram from this answer)
You should always seek to avoid the use of magic numbers. Instead, you could assign the known error code (1062) to a constant (e.g. MYSQL_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

这篇关于检测由于违反唯一约束而导致的 mysql 更新/插入失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！