CNDBA社区
1 ON DUPLICATE KEY UPDATE 说明
在MySQL中使用 INSERT 向表中插入记录时，如果插入的数据违反了表的唯一键约束（包括主键和唯一索引），可以使用ON DUPLICATE KEY UPDATE 来规避错误，使用该语法后，如果存在冲突不是抛出错误，而是执行 UPDATE 操作来更新已存在的记录。
基本语法
INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...)ON DUPLICATE KEY UPDATE
column1 = value1_new,
column2 = value2_new,
...;
语法解释
- INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)：这部分是标准的 INSERT 语句，用于向指定的表 table_name 中插入一行新记录。你需要指定要插入数据的列名和对应的值。
- ON DUPLICATE KEY UPDATE：这是该语法的核心部分，当插入的数据违反了表中的唯一键约束（如主键、唯一索引）时，MySQL 不会抛出错误，而是执行 UPDATE 子句中的操作。
- column1 = value1_new, column2 = value2_new, …：这是 UPDATE 子句，用于指定当发生唯一键冲突时，需要更新哪些列以及更新后的值。
注意事项：
- 唯一键约束：ON DUPLICATE KEY UPDATE 只会在插入的数据违反唯一键约束时才会触发更新操作。如果没有定义唯一键，该语法将等同于普通的 INSERT 语句。
- 虽然 ON DUPLICATE KEY UPDATE 提供了方便的插入或更新功能，但在高并发场景下，频繁的冲突可能会导致锁竞争问题。当多个事务同时尝试插入或更新同一行记录时，会相互等待锁的释放，从而降低了并发性能。
- 在使用 ON DUPLICATE KEY UPDATE 时，需要确保更新操作不会破坏数据的一致性。例如，在更新某些列时，可能需要考虑相关的业务逻辑和数据依赖关系。
- 每次执行 ON DUPLICATE KEY UPDATE 时，MySQL 都需要检查唯一键约束，并且在更新记录时可能需要维护相关的索引，这会增加一定的性能开销。特别是在表中存在多个唯一索引时，性能影响会更加明显。
2 操作示例
创建测试表：
mysql> use cndba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE users (
-> id INT PRIMARY KEY,
-> name VARCHAR(50),
-> email VARCHAR(100));
Query OK, 0 rows affected (0.01 sec)
插入新记录
mysql> INSERT INTO users (id, name, email)VALUES (1, 'John Doe', 'john@example.com')ON DUPLICATE KEY UPDATE
-> name = 'John Doe',
-> email = 'john@example.com';
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+------------------+
| id | name | email |
+----+----------+------------------+
| 1 | John Doe | john@example.com |
+----+----------+------------------+
1 row in set (0.00 sec)
以为 id 为 1 的记录不存在，所以这里就是直接插入表。如果 id 为 1 的记录已经存在，可以使用ON DUPLICATE KEY UPDATE更新该记录的 name 和 email 字段。
mysql> INSERT INTO users (id, name, email)VALUES (1, 'Jane Smith', 'jane@example.com')ON DUPLICATE KEY UPDATE
-> name = VALUES(name),
-> email = VALUES(email);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
mysql> select * from users;
+----+------------+------------------+
| id | name | email |
+----+------------+------------------+
| 1 | Jane Smith | jane@example.com |
+----+------------+------------------+
1 row in set (0.00 sec)
注意这里使用VALUES() 函数来引用 INSERT 语句中指定的值，也可以直接指定为具体的值。
如果当表中存在多个唯一索引时，只要插入的数据违反了其中任何一个唯一索引，ON DUPLICATE KEY UPDATE 都会触发更新操作。
mysql> CREATE TABLE products (
-> id INT PRIMARY KEY,
-> product_code VARCHAR(20) UNIQUE,
-> name VARCHAR(50),
-> price DECIMAL(10, 2));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO products (id, product_code, name, price)VALUES (1, 'P001', 'Product 1', 9.99)ON DUPLICATE KEY UPDATE
-> name = 'Product 1',
-> price = 9.99;
Query OK, 1 row affected (0.00 sec)
mysql> select * from products;
+----+--------------+-----------+-------+
| id | product_code | name | price |
+----+--------------+-----------+-------+
| 1 | P001 | Product 1 | 9.99 |
+----+--------------+-----------+-------+
1 row in set (0.00 sec)
如果 id 或 product_code 已经存在于表中，都会触发更新操作：
mysql> INSERT INTO products (id, product_code, name, price)
-> VALUES (1, 'P001', 'Product 1', 9.99)
-> ON DUPLICATE KEY UPDATE
-> name = 'Product 1',
-> price = 8.88;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from products;
+----+--------------+-----------+-------+
| id | product_code | name | price |
+----+--------------+-----------+-------+
| 1 | P001 | Product 1 | 8.88 |
+----+--------------+-----------+-------+
1 row in set (0.00 sec)
版权声明：本文为博主原创文章，未经博主允许不得转载。
