同事阿漠在批量导入 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 原创精华

• 从 Java 开始性能测试【完结】
• AI 探索笔记
• 故障测试与混沌工程
• 测试开发专题
• 性能测试专题
• Web 前端
• Java、Groovy、Go
• 测试理论、FunTester 风采
• 视频专题