同事阿漠在批量导入 10 万条用户数据时遇到了棘手问题：既有新增用户又有重复记录。使用标准 INSERT 语句时，第一条重复数据就导致整个导入流程中断，只能采用低效的循环方式——先查询是否存在，再决定是插入还是更新，结果数据库往返了 20 万次，耗时半小时还差点打满连接池。

其实，MySQL 提供了几种 INSERT 变体语法，可以优雅地处理有则更新，无则插入的场景，一条 SQL 就能搞定，性能大幅提升。

标准 INSERT

在介绍进阶用法之前，先回顾一下最基础的插入语法。INSERT INTO 是 MySQL 中最常用的数据操作语句之一，用于向表中添加新记录。无论是单个用户注册、批量数据导入，还是系统初始化，都离不开这条基础语法。掌握标准 INSERT 的特点和局限性，有助于我们更好地理解为什么需要各种变体用法，以及在实际开发中如何选择最合适的方案。

-- 插入单条记录
INSERT INTO users (id, name, email)
VALUES (1, 'FunTester', 'tester@example.com');

-- 插入多条记录
INSERT INTO users (id, name, email)
VALUES
    (2, 'Alice', 'alice@example.com'),
    (3, 'Bob', 'bob@example.com'),
    (4, 'FunTester_User', 'funtester@example.com');

标准 INSERT 语句的核心特点是严格的数据完整性控制：一旦遇到主键或唯一索引冲突就会立即报错并终止执行，甚至会导致整个事务回滚。这种"宁可报错也不妥协"的策略在对数据质量要求极高的场景（如金融交易、订单系统）中非常有用，能有效防止数据污染。但在日常业务开发中，这种严格性往往显得过于僵化，很多时候我们需要更灵活的处理机制来应对重复数据、批量导入等实际问题。

INSERT IGNORE

INSERT IGNORE 是 MySQL 中的一种 INSERT 变体语法，用于在插入数据时忽略主键或唯一索引冲突。当遇到冲突时，INSERT IGNORE 会静默跳过该记录，继续执行后续的插入操作，而不会抛出错误。这种灵活性在处理批量导入、数据去重等场景中非常有用。

语法

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

工作原理

当插入数据时遇到主键冲突、唯一索引冲突等错误，INSERT IGNORE 会：

静默跳过这条记录
继续执行后续的插入操作
不抛出错误，不影响事务

使用场景

场景 1：数据去重导入

-- 从临时表导入数据，自动跳过重复记录
INSERT IGNORE INTO products (product_id, name, price)
SELECT product_id, name, price FROM temp_products;

场景 2：幂等性操作

-- 记录用户登录日志，重复执行也不会出错
INSERT IGNORE INTO login_logs (user_id, login_time)
VALUES (123, NOW());

场景 3：多来源数据合并

INSERT IGNORE INTO contacts (email, name)
VALUES 
    ('alice@example.com', 'Alice'),
    ('bob@example.com', 'Bob'),
    ('alice@example.com', 'Alice Duplicate');  -- 会被跳过

优点

代码简洁，无需先查询再判断：相比传统的"SELECT + IF + INSERT/UPDATE"模式，一条 SQL 就能搞定，大大简化了业务代码逻辑，减少了代码量和维护成本
批量操作时不会因个别冲突而中断：在大批量数据处理时，如果某条记录重复，整个导入流程不会中断，其他有效数据可以正常插入，提高了数据处理的稳定性和成功率
性能较好，减少数据库往返次数：传统做法需要为每条记录进行查询判断，网络开销大；INSERT IGNORE 将判断逻辑交给数据库处理，显著提升在高并发和大批量场景下的性能表现

缺点

静默失败：你不知道哪些记录被忽略了，无法进行有效的监控和日志记录。在批量导入场景中，如果大量数据被跳过，你无法区分是正常去重还是数据质量问题，这对数据完整性审计非常不利
可能掩盖问题：真正的数据错误也会被忽略，例如数据类型不匹配、外键约束冲突等。这种"忽略一切"的策略可能会让真实的业务逻辑错误被隐藏，增加后期排查问题的难度
不适合严格场景：金融、订单等关键数据不建议使用，因为这些场景要求对每笔数据都要严格验证和记录。使用 INSERT IGNORE 可能会跳过重要的业务校验，导致数据不一致或业务逻辑错误

REPLACE INTO

REPLACE INTO 是 MySQL 中的一种 INSERT 变体语法，用于在插入数据时替换主键或唯一索引冲突的记录。当遇到冲突时，REPLACE INTO 会先删除冲突的旧记录，再插入新记录。这种灵活性在处理配置表更新、缓存刷新等场景中非常有用。

语法

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

工作原理

REPLACE 的行为是：

首先，REPLACE INTO 会尝试执行标准的 INSERT 操作。这个过程中会进行完整的数据验证，包括检查数据类型是否匹配、约束条件是否满足、外键关系是否正确。如果所有验证都通过，数据就会准备插入到表中。

如果在这个过程中遇到主键或唯一索引冲突，REPLACE INTO 不会简单地报错停止，而是会先删除冲突的旧记录。这个删除操作会级联触发相关的 DELETE 触发器，同时释放旧记录占用的存储空间。然后，再执行完整的 INSERT 过程，包括触发器触发、索引更新等操作，确保新数据能够完全写入。

当然，如果没有遇到任何冲突，REPLACE INTO 就会正常执行 INSERT 操作。新记录会被添加到表中，不会影响任何现有的数据，整个过程与标准的 INSERT 语句完全一致。

使用场景

场景 1：配置表更新

-- 更新用户设置，有就覆盖，没有就创建
REPLACE INTO user_settings (user_id, theme, language)
VALUES (123, 'dark', 'zh-CN');

场景 2：缓存表刷新

-- 刷新缓存数据，确保是最新的
REPLACE INTO cache_products (product_id, data, expire_time)
VALUES (1001, '{"name":"Product A"}', '2025-01-20 10:00:00');

与 INSERT IGNORE 的区别

特性	INSERT IGNORE	REPLACE INTO
遇到冲突	跳过，保留旧数据	删除旧数据，插入新数据
执行结果	可能什么都不做	确保新数据被写入
影响行数	0（如果跳过）	1（插入）或 2（删除 + 插入）

注意事项

⚠️ 重要警告：

REPLACE 会删除整条旧记录，如果新数据缺少某些字段，这些字段会变成默认值或 NULL
会触发 DELETE 和 INSERT 相关的触发器
自增 ID 会变化

-- 危险示例
-- 假设原记录：id=1, name='John', email='old@example.com', phone='123456'
REPLACE INTO users (id, name, email)
VALUES (1, 'John', 'new@example.com');
-- 结果：phone 字段会丢失！变成 NULL 或默认值

INSERT ... ON DUPLICATE KEY UPDATE

语法

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE 
    column1 = new_value1,
    column2 = new_value2;

工作原理

这是最灵活的方法：INSERT ... ON DUPLICATE KEY UPDATE 会首先尝试执行标准的插入操作，如果遇到主键或唯一索引冲突，则不会删除旧记录而是执行 UPDATE 操作更新指定字段，确保数据完整性不受损；如果没有冲突，则正常插入新记录，与标准 INSERT 语句完全一致。

使用场景

场景 1：积分累加

-- 如果用户存在就累加积分，不存在就创建新用户
INSERT INTO users (user_id, username, points)
VALUES (123, 'john_doe', 100)
ON DUPLICATE KEY UPDATE 
    points = points + 100;  -- 累加而非覆盖

场景 2：访问计数

-- 记录文章浏览量
INSERT INTO article_stats (article_id, views, last_view)
VALUES (1001, 1, NOW())
ON DUPLICATE KEY UPDATE 
    views = views + 1,
    last_view = NOW();

场景 3：库存更新

-- 更新库存，保留其他字段不变
INSERT INTO inventory (product_id, stock, updated_at)
VALUES (2001, 50, NOW())
ON DUPLICATE KEY UPDATE 
    stock = VALUES(stock),      -- 使用新值
    updated_at = VALUES(updated_at);

优点

精确控制：只更新需要更新的字段，可以精确指定哪些列需要更新、哪些保持不变，避免了不必要的字段覆盖，提供最细粒度的数据控制能力
不丢数据：不会删除记录，其他字段保持不变，确保历史数据和关联信息不会丢失，这一点比 REPLACE INTO 更安全，特别适合包含重要业务字段的表
支持复杂逻辑：可以累加、拼接、条件判断等复杂的更新操作，例如积分累加、状态合并、时间戳更新等，支持在 UPDATE 子句中使用 VALUES() 函数或复杂的表达式
最推荐的方案：适合大多数"有则更新，无则插入"的场景，平衡了性能和数据完整性，是处理重复数据最安全、最灵活的首选方法

实战对比

场景对比表

需求	推荐用法	原因
数据导入，允许跳过重复	`INSERT IGNORE`	简单高效，不影响已有数据
配置更新，要完全覆盖	`REPLACE INTO`	确保新数据完整替换旧数据
积分累加、计数器	`ON DUPLICATE KEY UPDATE`	可以累加而非覆盖
部分字段更新	`ON DUPLICATE KEY UPDATE`	其他字段保持不变
大批量数据导入	批量 `INSERT`	性能最优
数据备份/迁移	`INSERT ... SELECT`	直接从查询结果插入
严格要求唯一性	标准 `INSERT`	冲突时报错，便于发现问题

FunTester 原创精华

↙↙↙阅读原文可查看相关链接，并与作者交流