1. 什么是 UPSERT？
概述
“UPSERT” 指的是数据库功能，它结合了“INSERT”和“UPDATE”操作。换句话说，如果数据不存在，则插入；如果相同数据已存在，则更新。通过使用此功能，您可以执行高效操作，同时保持数据一致性。
在 MySQL 中，此功能使用 INSERT ... ON DUPLICATE KEY UPDATE 语法实现。此功能允许您避免重复键错误，并在发生重复键时更新现有记录。
使用案例
- 客户管理系统 : 如果客户数据不存在，则添加新客户数据；当客户信息更改时，更新现有客户信息。
- 产品库存管理 : 添加新产品，同时更新现有产品的库存数量。
MySQL 中 UPSERT 的优势
- 避免重复键错误
- 简化 SQL 查询
- 维护数据完整性
2. MySQL 中 UPSERT 的基本用法
在 MySQL 中，UPSERT 操作使用 INSERT ... ON DUPLICATE KEY UPDATE 语法执行。使用此语法，如果发生重复键，您可以更新现有数据的部分或全部，而不是插入新数据。
基本语法
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
column1 = value1, column2 = value2;
解释：
- 使用
INSERT INTO插入数据。 - 如果插入的数据已存在于表中，则执行
ON DUPLICATE KEY UPDATE子句，并更新现有数据。
示例：
INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON DUPLICATE KEY UPDATE
name = 'Taro Tanaka';
在上例中，如果用户 user_id 为 1 已存在，则将 name 字段更新为 ‘Taro Tanaka’。如果用户不存在，则插入新记录。
3. UPSERT 的详细 SQL 语法和示例
更新多个列
使用 UPSERT 时，可能有只想更新特定列的情况。在这种情况下，您可以在 ON DUPLICATE KEY UPDATE 子句中仅指定必要的列。
INSERT INTO products (product_id, name, price)
VALUES (100, 'Laptop', 50000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
在此示例中，如果 product_id 为 100 的产品已存在，则仅更新 price 列，而其他列（如 name）保持不变。
4. 与其他数据库的区别
除了 MySQL 之外的数据库也提供类似功能。例如，PostgreSQL 和 SQLite 使用 INSERT ... ON CONFLICT 或 MERGE 语句来实现类似 UPSERT 的行为。
PostgreSQL 示例
INSERT INTO users (user_id, name)
VALUES (1, 'Taro Tanaka')
ON CONFLICT (user_id) DO UPDATE SET
name = 'Taro Tanaka';
在 PostgreSQL 和 SQLite 中，使用 ON CONFLICT 子句来控制发生重复键错误时的行为。相比之下，MySQL 使用 ON DUPLICATE KEY UPDATE 子句。
MySQL 特定特性
- MySQL 使用
INSERT ... ON DUPLICATE KEY UPDATE，由于语法与其他数据库不同，在系统之间迁移时需要特别注意。
5. 高级 UPSERT 技术
批量 UPSERT（多记录批处理）
UPSERT 不仅可以针对单个记录执行，还可以一次性执行多个记录。这显著提高了数据库操作的效率。
INSERT INTO products (product_id, name, price)
VALUES
(100, 'Laptop', 50000),
(101, 'Smartphone', 30000)
ON DUPLICATE KEY UPDATE
price = VALUES(price);
在此示例中，一次性插入多个产品记录。如果存在重复键，则仅更新相应记录的 price 字段。
使用存储过程进行 UPSERT
要优化 UPSERT 处理，您也可以使用存储过程。这使您能够在数据库内部创建可重用的逻辑，从而提升代码的可读性和可维护性。
6. 常见陷阱与重要注意事项
事务与死锁
在使用 UPSERT——尤其是处理大批量数据时——可能会出现死锁。如果 MySQL 的事务隔离级别设置为 REPEATABLE READ，则更容易出现间隙锁（gap lock）。
避免间隙锁
- 通过将事务隔离级别改为
READ COMMITTED可以降低死锁的可能性。 - 如有必要，考虑将大型 UPSERT 操作拆分为更小的批次，并执行多条查询，而不是一次性执行大语句。
7. 结论
MySQL 的 UPSERT 功能在简化数据插入和更新、避免键冲突错误方面非常有用。但实现 UPSERT 时必须仔细考虑潜在的死锁和事务设置。正确使用时，它能够使数据库操作更简洁、更高效。
