MySQL中INSERT IGNORE、INSERT和REPLACE的区别及示例
MySQL 中处理数据插入时，若遇到主键或唯一索引冲突，INSERT、REPLACE 和 INSERT IGNORE 行为截然不同。本文通过实际测试代码对比三者差异，分析其对 ID 自增、数据完整性的影响。
三种插入方式的核心区别
当表中存在 PRIMARY KEY 或 UNIQUE 索引时，以下指令的表现如下：
| 指令 | 记录已存在 | 记录不存在 | 典型场景 |
|---|---|---|---|
insert into | 报错 (Duplicate entry) | 正常插入 | 严格确保不重复 |
insert ignore into | 静默忽略，保留原数据 | 正常插入 | 批量导入去重 |
replace into | 删除旧数据，插入新数据 | 正常插入 | 强制更新全字段 |
注意： 这三种行为均依赖于表结构中存在主键或唯一约束。若无约束，三者效果等同。此外，REPLACE 会导致主键 ID 自增，而 INSERT IGNORE 即使失败也会导致自增计数器增加。
第一步：创建测试数据表
为了直观对比，我们需要创建一个包含自增主键和唯一名称索引的表。
CREATE TABLE names(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) UNIQUE,
age INT(10)
);初始化一些基础数据：
INSERT INTO names(name, age) VALUES("小明", 24);
INSERT INTO names(name, age) VALUES("大红", 24);
INSERT INTO names(name, age) VALUES("大壮", 24);
INSERT INTO names(name, age) VALUES("秀英", 24);当前表状态如下：
+----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 小明 | 24 | | 2 | 大红 | 24 | | 3 | 大壮 | 24 | | 4 | 秀英 | 24 | +----+--------+------+
第二步：使用 INSERT（冲突测试）
INSERT 是标准插入语法。如果尝试插入已存在的唯一值，数据库将抛出错误，事务回滚，不会执行任何操作。
mysql> INSERT INTO names(name, age) VALUES("小明", 23);
ERROR 1062 (23000): Duplicate entry '小明' for key 'name'结果：插入失败，表数据无变化。
第三步：使用 REPLACE（替换逻辑）
REPLACE INTO 的工作机制是“先删后插”。它先尝试查找匹配的行，如果找到则删除该行，然后插入新行。
情况 A：记录已存在
mysql> REPLACE INTO names(name, age) VALUES("小明", 23);
Query OK, 2 rows affected (0.00 sec)查询结果显示，小明的 ID 从 1 变为了 6（因为原记录被删，新 ID 自增），年龄变为 23：
+----+--------+------+ | id | name | age | +----+--------+------+ | 2 | 大红 | 24 | | 3 | 大壮 | 24 | | 4 | 秀英 | 24 | | 6 | 小明 | 23 | +----+--------+------+
风险提醒： 由于 REPLACE 会物理删除旧记录，如果该表被其他表通过外键引用，可能会破坏参照完整性。同时，“2 rows affected” 表示删除一行、插入一行。
情况 B：记录不存在
若 name 为全新值，REPLACE 的行为等同于 INSERT，返回 “1 row affected”：
mysql> REPLACE INTO names(name, age) VALUES("大名", 23);
Query OK, 1 row affected (0.00 sec)第四步：使用 INSERT IGNORE（忽略冲突）
INSERT IGNORE 会尝试执行插入。如果遇到唯一键冲突，它会静默丢弃该新数据，但不会报错，也不改变原有记录。
情况 A：记录已存在
mysql> INSERT IGNORE INTO names(name, age) VALUES("大壮", 25);
Query OK, 0 rows affected, 1 warning (0.00 sec)查询验证，"大壮" 的年龄仍为 24，未被修改为 25：
+----+--------+------+ | id | name | age | +----+--------+------+ | 2 | 大红 | 24 | | 3 | 大壮 | 24 | | 4 | 秀英 | 24 | | 6 | 小明 | 23 | | 7 | 大名 | 23 | |...| ... | ... | +----+--------+------+
细节提示： 即使忽略成功，Auto Increment 计数器的指针也会前进。因此在批量导入数据时，ID 可能出现大量跳号。
情况 B：记录不存在
若插入全新的数据，则正常执行：
mysql> INSERT IGNORE INTO names(name, age) VALUES("壮壮", 25);
Query OK, 1 row affected (0.01 sec)总结与选用建议
- 需要严格数据准确性： 使用
INSERT。让程序捕获异常，决定是重试还是报警。 - 需要全量覆盖更新： 使用
REPLACE。适用于配置表等完全由当前数据定义的表格，但要警惕外键关联丢失的风险。 - 只需追加新数据，忽略重复： 使用
INSERT IGNORE。常用于日志收集或用户注册校验，减少异常干扰。
以上就是 MySQL 中 INSERT IGNORE、INSERT 和 REPLACE 的区别及详细说明，更多关于 MySQL 数据操作的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
