MySQL 中 INSERT IGNORE 和 INSERT ON DUPLICATE

sql INSERT IGNORE INTO table_name (col1, col2, ...) VALUES (value1, value2, ...);

你也可以用它配合 `SELECT` 语句插入多行：

```sql
INSERT IGNORE INTO table_name (col1, col2, ...)
SELECT colA, colB, ... FROM another_table;

工作原理

当 MySQL 执行 INSERT IGNORE 时，一旦遇到会导致唯一索引或主键冲突的行，它并不会报错，而是：

1. 忽略这一行的插入操作。
2. 给出一个警告（SHOW WARNINGS 可以查看）。
3. 继续执行后续的其他行（如果存在批处理）。

对于包含多条数据的语句，只有冲突的行被跳过，其他行正常插入。这种方式非常适合“只插入不重复数据”的场合。

示例演示

-- 示例表：用户邮箱唯一
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

-- 插入初始数据
INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');

-- 使用 INSERT IGNORE 尝试插入重复邮箱
INSERT IGNORE INTO users (username, email) VALUES ('Bob', 'alice@example.com');
-- 结果：Query OK, 0 rows affected, 1 warning. 该行被忽略，不会新增Bob

-- 一次插入多行，其中一行冲突，其他正常插入
INSERT IGNORE INTO users (username, email) VALUES
('Charlie', 'charlie@example.com'),
('Dave', 'alice@example.com'),    -- 冲突，被忽略
('Eve', 'eve@example.com');
-- 最终表中有 Alice, Charlie, Eve，Dave 被跳过

注意事项

• INSERT IGNORE 忽略的是所有错误，而不仅仅是键冲突。比如数据类型转换失败（将 'abc' 插入 INT 列）也会被忽略并转为插入默认值或最接近的值，这可能带来非预期的静默数据丢失。因此它更适合你确信只会发生键冲突的场景。
• 对于自增主键列，即使行被忽略，自增值仍会消耗掉（与后续的 ON DUPLICATE 相同），因为 MySQL 在判断冲突前已经分配了自增ID。
• 可以通过 ROW_COUNT() 函数查看实际影响的行数（被忽略的行不计入）。

INSERT ON DUPLICATE KEY UPDATE：冲突时执行更新

基本语法

当插入行与现有唯一键或主键冲突时，ON DUPLICATE KEY UPDATE 允许你直接更新该行，而不是忽略它。语法为在 INSERT 语句末尾添加更新子句：

INSERT INTO table_name (col1, col2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2);

VALUES(col_name) 函数用来引用本次 INSERT 试图插入的那行数据中的对应列值。如果你希望在某些条件下才更新，也可以使用条件判断。

工作原理

1. MySQL 尝试插入新行。
2. 如果插入导致唯一/主键冲突，则把该行变为更新操作，使用 UPDATE 子句中指定的表达式修改现有行。
3. 若没有冲突，则普通插入。

它就像一条智能的 IF 存在 THEN UPDATE ELSE INSERT 语句，但在数据库层面执行，更高效且具备原子性。

示例演示

-- 继续使用之前的 users 表
-- 假设已有 Alice: alice@example.com

INSERT INTO users (username, email) VALUES ('Alice_new', 'alice@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);
-- 因为邮箱冲突，该语句不会新增行，而是将 Alice 的 username 更新为 'Alice_new'

-- 插入新用户 Charlie 并预设冲突时行为
INSERT INTO users (username, email) VALUES ('Charles', 'charles@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);
-- 没有冲突，直接插入 Charles

-- 更实用的例子：计数器累加
CREATE TABLE page_views (
    page VARCHAR(100) PRIMARY KEY,
    view_count INT NOT NULL DEFAULT 0
);

INSERT INTO page_views (page, view_count) VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;
-- 每执行一次，'/home' 的 view_count 就加1；首次执行插入计数1（通常初值为1）。

使用 VALUES() 函数

UPDATE 子句中，VALUES(col) 返回的是当前插入尝试中该列的值。你还可以做运算：

ON DUPLICATE KEY UPDATE 
    price = VALUES(price) * 1.1,          -- 更新为原值的110%
    stock = stock + VALUES(stock);        -- 累加库存

从 MySQL 8.0.20 开始，VALUES() 被标记为弃用，推荐使用列别名（如通过 AS 为插入值命名）或直接引用插入的列名。但在 8.0 以下版本中它依然是最清晰的方式。

批量插入中的应用

当插入多条数据时，ON DUPLICATE 同样工作良好，每一条都会根据冲突情况独立决策：

INSERT INTO users (username, email) VALUES
('UserA', 'a@example.com'),
('UserB', 'b@example.com'),
('UserC', 'c@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);
-- 每行根据是否冲突，执行插入或更新

这对于数据同步、ETL 流水线非常有用。

INSERT IGNORE 与 ON DUPLICATE KEY UPDATE 的区别

行为对比

性能考量

• INSERT IGNORE 因为无需执行额外的更新操作，在纯跳过场景更快。
• ON DUPLICATE 在冲突时需要实际执行 UPDATE 操作，会多一些写开销。但若需要更新，这是最直接的方式。
• 两者在检查唯一键时都会产生索引查询开销，对大批量数据建议使用 LOAD DATA 配合相应参数，或分批处理。

选择指南

• 使用 INSERT IGNORE 当你只想插入不存在的数据，对已存在的记录完全不动。例如：向已存在的集合添加新标签，重复标签自动忽略。
• 使用 ON DUPLICATE KEY UPDATE 当你希望新数据覆盖旧数据，或执行累加、合并等逻辑。例如：更新用户信息最后登录时间、累加访问次数、同步外部数据源。
• 绝对不要使用 INSERT IGNORE 来实现“存在则跳过，否则插入”+“需要传回ID”的复杂场景，因为被忽略的行无法返回自增ID，可能需要额外查询。

常见问题与最佳实践

如何知道插入是被更新了还是忽略了？

可以通过 ROW_COUNT() 函数：

• INSERT IGNORE 时，成功插入的行数为 1，忽略的行数为 0。
• ON DUPLICATE 时，若发生更新返回值 2，新插入返回值 1。 同时查询 SHOW WARNINGS 可以查看被忽略行的具体警告信息。

无唯一键的表可以使用这两个语句吗？

不可以。INSERT IGNORE 和 ON DUPLICATE 都依赖唯一索引或主键来检测冲突。如果表没有这些约束，所有行都视为不冲突，这两个语句退化为普通 INSERT。

插入大量数据时，如何优化性能？

• 使用批量插入语法（一条 INSERT 多行 VALUES）减少客户端与服务器的往返。
• 如果表上有多个唯一索引，冲突检测开销会增大，考虑只保留业务需要的唯一键。
• 对于数据同步任务，可以先用 REPLACE INTO（注意 REPLACE 是先删除再插入，会引发触发器问题，一般不推荐）或开启事务临时移除索引再重建。

更新时能使用外部的变量或子查询吗？

可以。UPDATE 子句支持表达式、函数甚至子查询：

ON DUPLICATE KEY UPDATE 
    update_time = NOW(),
    total = total + (SELECT COUNT(*) FROM logs WHERE user_id = user_id);