MySQL实现新增或更新操作去重操作

ゝ一纸荒年。 2023-10-09 23:18 31阅读 0赞

#### MySQL新增时数据重复则更新或不操作，不重复则新增 ####

*  应用场景
 *  实现方案
 *   *  1. REPLACE INTO 语句：
     *  2. INSERT INTO ... ON DUPLICATE KEY UPDATE 语句结合事务：
     *  3. INSERT INTO ... SELECT ... FROM ... ON DUPLICATE KEY UPDATE 语句：
     *  4. 根据出主键外其他唯一字段实现更新或新增
 *  总结

![在这里插入图片描述][cb4bd01f654949548f9bb2036a9816df.png]

## 应用场景 ##

以下是一些 MySQL 新增时存在相同数据则更新，否则新增的常见应用场景：

1.  用户偏好设置：在应用程序中，用户可能有自己的偏好设置，如主题、语言偏好等。通过将用户ID作为唯一索引，可以在用户更新其偏好设置时执行更新操作，以确保偏好设置始终是最新的。如果用户尚未存在，则可以执行插入操作以创建新用户的偏好设置。
2.  统计数据更新：在需要实时更新统计数据的应用中，可以使用插入时存在冲突则更新的方式来更新统计数据。例如，每当用户执行某个操作时，可以将相关数据插入到统计表中，并使用唯一索引或主键来保证数据的唯一性。如果数据已存在，则可以通过更新操作来更新相关统计信息。
3.  在线购物车：在电子商务应用中，用户可能会将商品添加到购物车中。使用用户ID和商品ID作为唯一索引，可以在商品已存在于购物车时执行更新操作（如更新商品数量），否则执行插入操作将商品添加到购物车。
4.  实时更新缓存：在缓存系统中，可以使用插入时存在冲突则更新的方式来更新缓存数据。当数据发生变化时，可以将变化的数据插入到缓存表中，并使用唯一索引或主键来保证数据的唯一性。如果数据已存在，则可以通过更新操作来更新缓存数据。

这些是一些常见的应用场景，其中在插入数据时根据是否存在相同数据进行更新或新增。通过利用数据库的唯一约束和索引，可以在这些场景中实现数据的动态更新和插入操作。这样可以保持数据的一致性，并确保数据始终处于最新状态。

## 实现方案 ##

### 1. REPLACE INTO 语句： ###

REPLACE INTO 表名 (ID, 列1, 列2, ...)
    VALUES (值1, 值2, ...);

这将尝试插入数据，如果存在相同的 ID，则会删除原有的行并插入新的数据。

需要注意的是，使用 `REPLACE INTO` 语句时，表必须有一个唯一索引或主键来标识每一行的 ID，否则无法正常执行替换操作。

在高并发情况下，使用 `REPLACE INTO` 语句可能会导致锁冲突。这是因为 `REPLACE INTO` 语句实际上是先删除原有行，然后插入新的行，这涉及到对数据表的写操作，可能会引发锁定问题。

### 2. INSERT INTO … ON DUPLICATE KEY UPDATE 语句结合事务： ###

START TRANSACTION;
    INSERT INTO 表名 (ID, 列1, 列2, ...)
    VALUES (值1, 值2, ...)
    ON DUPLICATE KEY UPDATE 列1=新值1, 列2=新值2, ...;
    COMMIT;

通过将插入操作置于事务中，可以确保并发情况下的数据一致性，避免锁冲突的问题。

**批量操作：**

INSERT INTO 表名 (ID, 列1, 列2, ...)
    VALUES (值1, 值2, ...),
           (值3, 值4, ...),
           ...
    ON DUPLICATE KEY UPDATE 列1=VALUES(列1), 列2=VALUES(列2), ...;

### 3. INSERT INTO … SELECT … FROM … ON DUPLICATE KEY UPDATE 语句： ###

INSERT INTO 表名 (ID, 列1, 列2, ...)
    SELECT 值1, 值2, ...
    FROM dual
    ON DUPLICATE KEY UPDATE 列1=新值1, 列2=新值2, ...;

这种方法利用 SELECT 查询与 INSERT 结合，避免了直接的写操作，也能实现相同 ID 更新行的效果。

### 4. 根据出主键外其他唯一字段实现更新或新增 ###

确保目标表中除了主键外的字段具有唯一约束，可以通过创建唯一索引或唯一约束来实现。

使用 INSERT INTO … ON DUPLICATE KEY UPDATE 语句进行新增或更新操作，并在 ON DUPLICATE KEY UPDATE 子句中指定需要更新的字段。

以下是一个示例：

假设有一个表名为 your\_table，包含字段 id（主键）和 unique\_field（其他唯一字段），以及其他字段 value1 和 value2。

INSERT INTO your_table (id, unique_field, value1, value2)
    VALUES (1, 'unique_value', 'new_value1', 'new_value2')
    ON DUPLICATE KEY UPDATE value1 = VALUES(value1), value2 = VALUES(value2);

在上述示例中，如果具有相同 unique\_field 值的记录已存在，则会执行更新操作，将 value1 和 value2 字段更新为 ‘new\_value1’ 和 ‘new\_value2’。如果不存在具有相同 unique\_field 值的记录，则会执行插入操作，新增一条记录。

**注意：**  
在使用 INSERT INTO … ON DUPLICATE KEY UPDATE 语句进行新增或更新操作时，MySQL会首先根据唯一索引或唯一约束来判断是否存在冲突。具体的判断顺序如下：

首先，MySQL会根据主键判断是否存在冲突。如果存在与要插入的记录具有相同主键的记录，则执行更新操作。

如果不存在主键冲突，MySQL会继续根据其他唯一索引或唯一约束进行判断。在这种情况下，MySQL会根据这些唯一字段的值来判断是否存在冲突。如果存在与要插入的记录具有相同唯一字段值的记录，则执行更新操作。

因此，在上述的例子中，如果 unique\_field 字段具有唯一索引或唯一约束，且存在与要插入的记录具有相同 unique\_field 值的记录，则会执行更新操作。如果不存在相同 unique\_field 值的记录，则会执行插入操作。

## 总结 ##

总结起来，MySQL会首先根据主键进行判断，然后再根据其他唯一索引或唯一约束进行判断。如果存在冲突，则执行更新操作；如果不存在冲突，则执行插入操作。

请确保在实际使用中，除了主键之外的字段具有唯一约束，以确保数据的唯一性。根据您的具体需求，可以修改示例中的表名、字段名和条件来适应您的场景。

[cb4bd01f654949548f9bb2036a9816df.png]: https://img-blog.csdnimg.cn/cb4bd01f654949548f9bb2036a9816df.png