MySQL中重复键错误不会取消/回滚MySQL事务的解决方案

在MySQL数据库编程中，事务是一组原子性操作，它们要么全部成功完成，要么全部取消。当我们在MySQL事务中执行操作时，如果出现重复键错误，它将引发一场异常，但是MySQL并不会自动取消或回滚事务。在本文中，我们将介绍如何处理这个问题。

阅读更多：MySQL 教程

重复键错误是什么？

重复键错误是指当我们向表中插入或更新数据时，如果指定的主键或唯一索引已经存在相同的值，则会发生重复键错误。例如，我们在一个用户表中创建了一个名为”user_name”的唯一索引：

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX idx_user_name (user_name)
);

然后我们尝试向表中插入两条记录，”user_name”都为”john”，这将引发一个重复键错误：

START TRANSACTION;

INSERT INTO user (user_name, password, email) VALUES ('john', 'secret', 'john@example.com');
INSERT INTO user (user_name, password, email) VALUES ('john', 'others', 'other@example.com');

COMMIT;

当我们执行以上代码时，第二个INSERT语句将抛出一个重复键错误：

ERROR 1062 (23000): Duplicate entry 'john' for key 'idx_user_name'

事务和重复键错误

当我们在事务中执行以上代码时，重复键错误会导致异常，但是MySQL并不会自动回滚事务。例如，如果我们将以上代码放在一个事务中：

START TRANSACTION;

INSERT INTO user (user_name, password, email) VALUES ('john', 'secret', 'john@example.com');
INSERT INTO user (user_name, password, email) VALUES ('john', 'others', 'other@example.com');

COMMIT;

代码将成功执行，第一条INSERT语句将插入一个记录。但是，第二条INSERT语句将引发一个重复键错误。但是MySQL仍将提交事务，尽管我们本来希望事务应该取消或回滚。

处理重复键错误

为了避免出现这种情况，我们需要对重复键错误进行处理。以下是几种处理重复键错误的方式：

1. 显式回滚事务

当我们遇到重复键错误时，可以通过显式回滚事务来取消所有操作，如下所示：

START TRANSACTION;

INSERT INTO user (user_name, password, email) VALUES ('john', 'secret', 'john@example.com');
INSERT INTO user (user_name, password, email) VALUES ('john', 'others', 'other@example.com');

ROLLBACK;

这样，第二条INSERT语句将引发一个重复键错误，事务将回滚，没有任何记录插入到表中。

2. 使用IGNORE关键字

当我们向表中插入记录时，可以使用IGNORE关键字，它将忽略重复键错误而不会抛出异常，如下所示：

START TRANSACTION;

INSERT IGNORE INTO user (user_name, password, email) VALUES ('john', 'secret', 'john@example.com');
INSERT IGNORE INTO user (user_name, password, email) VALUES ('john', 'others', 'other@example.com');

COMMIT;

这样，第一条INSERT语句将插入一个记录，在第二条INSERT语句中使用IGNORE关键字，MySQL将忽略重复键错误，并且不会抛出异常。事务将成功提交，只有一条记录插入到表中。

3. 使用REPLACE关键字

另一种处理重复键错误的方法是使用REPLACE关键字而不是INSERT。REPLACE在键冲突时删除原来的记录并插入新的记录，如下所示：

START TRANSACTION;

REPLACE INTO user (user_name, password, email) VALUES ('john', 'secret', 'john@example.com');
REPLACE INTO user (user_name, password, email) VALUES ('john', 'others', 'other@example.com');

COMMIT;

这样，第一条REPLACE语句将插入一个记录，并且没有重复键错误。第二条REPLACE语句将删除第一条记录，并插入一条新记录，同时也没有重复键错误。事务将成功提交，只有一条记录插入到表中。

4. 创建触发器

另一种处理重复键错误的方法是创建一些触发器。触发器是一种特殊类型的存储过程，当指定的事件发生时，它将自动执行。我们可以创建一个触发器，在重复键错误发生时自动取消事务。以下是创建触发器的示例代码：

CREATE TRIGGER trg_user_duplicate_key
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
    DECLARE msg VARCHAR(255);
    IF (SELECT COUNT(*) FROM user WHERE user_name = NEW.user_name) > 0 THEN
        SET msg = CONCAT('Duplicate key error on user_name = ', NEW.user_name);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
END;

这个触发器将在每次插入记录之前执行。如果重复键错误发生，它将用SIGNAL语句抛出异常，这将自动取消事务。