insertOnDuplicateKey 和 upsert 区别

二者是一样的

insertOnDuplicateKey 实际上是 ON DUPLICATE KEY UPDATE，是MySQL的特有写法

类似的有 ON CONFICT() ，这个是 pgsql 的类似写法

在数据库操作中，INSERT ON DUPLICATE KEY UPDATE（MySQL特有语法）和UPSERT（通用概念，不同数据库实现不同）都是用于处理“如果记录存在则更新，不存在则插入”的场景。以下是它们的详细对比和关键区别：

1. 核心概念

(1)INSERT ON DUPLICATE KEY UPDATE（MySQL）

• 定义：MySQL特有的SQL语法，当插入数据时违反唯一键（UNIQUE KEY）或主键约束时，自动执行更新操作。
• 适用场景：仅适用于MySQL及其衍生数据库（如MariaDB）。
• 示例：

  sql

  INSERT INTO orders (order_id, product_id, quantity, price) VALUES (1, 100, 5, 10.0) ON DUPLICATE KEY UPDATE quantity = quantity + 1, price = VALUES(price);

  • 如果order_id和product_id的组合已存在，则更新quantity和price。
  • VALUES(price)表示引用插入时的值。

(2)UPSERT（通用概念）

• 定义：数据库操作的通用术语，表示“插入或更新”（Update orInsert），是SQL标准中定义的原子操作。
• 不同数据库的实现：
  • PostgreSQL：INSERT ... ON CONFLICT DO UPDATE
  • SQLite：INSERT OR REPLACE或ON CONFLICT
  • Oracle：MERGE语句
  • SQL Server：MERGE语句
  • JPA/Hibernate：通过@Entity的@DynamicUpdate或saveOrUpdate方法模拟。

2. 关键区别

3. 具体数据库实现对比

(1) MySQL

sql

-- 插入或更新（基于唯一键冲突） INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com') ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

• 特点：简单直接，但仅限MySQL。

(2) PostgreSQL

sql

-- 插入或更新（基于唯一约束冲突） INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

• 特点：
  • ON CONFLICT (column)指定冲突条件。
  • EXCLUDED表引用插入时的值。

(3) SQLite

sql

-- 方式1：替换整行（危险！可能丢失数据） INSERT OR REPLACE INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com'); -- 方式2：条件更新（更安全） INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com') ON CONFLICT(id) DO UPDATE SET name = excluded.name;

• 注意：OR REPLACE会删除旧行再插入新行，可能导致外键约束问题。

(4) Oracle/SQL Server

sql

-- Oracle/SQL Server使用MERGE语句 MERGE INTO users target USING (SELECT 1 AS id, 'Alice' AS name, 'alice@example.com' AS email FROM dual) source ON (target.id = source.id) WHEN MATCHED THEN UPDATE SET target.name = source.name, target.email = source.email WHEN NOT MATCHED THEN INSERT (id, name, email) VALUES (source.id, source.name, source.email);

• 特点：功能强大，但语法复杂。

4. 如何选择？

• MySQL项目：直接用INSERT ON DUPLICATE KEY UPDATE，简单高效。
• 跨数据库项目：
  • 使用JPA/Hibernate的saveOrUpdate（需实体类配置正确）。
  • 或根据数据库类型动态生成SQL（如PostgreSQL用ON CONFLICT，Oracle用MERGE）。
• 高并发场景：注意锁竞争问题（如MySQL的INSERT ... ON DUPLICATE KEY可能引发间隙锁）。

5. 代码示例（Spring Data JPA）

如果使用Spring Data JPA，可以通过以下方式模拟UPSERT：

java

@Repository public interface UserRepository extends JpaRepository<User, Long> { @Modifying @Query(value = "INSERT INTO users (id, name, email) " + "VALUES (:id, :name, :email) " + "ON CONFLICT (id) DO UPDATE SET name = :name, email = :email", nativeQuery = true) void upsertUser(@Param("id") Long id, @Param("name") String name, @Param("email") String email); }

• 注意：需启用@Modifying和事务（@Transactional）。

总结

• INSERT ON DUPLICATE KEY UPDATE：MySQL专属，简单直接。
• UPSERT：通用概念，需根据数据库选择具体实现（如PostgreSQL的ON CONFLICT、Oracle的MERGE）。
• 跨数据库兼容性：优先考虑JPA/Hibernate或抽象层，避免硬编码SQL。