MySQL插入与批量操作实战：IGNORE、REPLACE及高效更新技巧

核心去重插入语句解析

默认情况下，当向MySQL表插入包含主键或唯一索引重复值的记录时，数据库会抛出错误并终止事务。为了应对这一情况，我们可以采用以下三种策略来灵活处理冲突：

第1步：忽略重复插入（INSERT IGNORE INTO）

目的：遇到重复键时直接跳过当前行，不报错也不修改原有数据，适用于“只增不减”的场景。

前提条件：目标字段必须已设置 PRIMARY KEY 或 UNIQUE INDEX。

操作示例：

INSERT IGNORE INTO user (username, sex, address) 
VALUES ('hanpang', 'boy', 'HongKong');

完成状态：若 username='hanpang' 已存在，该语句执行后无变化；若不存在，则正常插入新记录。

第2步：冲突即更新（ON DUPLICATE KEY UPDATE）

目的：如果记录存在则更新指定字段，否则插入新记录。这是实现“Upsert”（Insert/Update合并）最常用的方式。

前提条件：同上，依赖主键或唯一索引。

操作示例：

INSERT INTO user (username, sex, address) 
VALUES ('hanpang', 'boy', 'HongKong')
ON DUPLICATE KEY UPDATE 
sex = VALUES(sex), 
address = VALUES(address);

注意：原文中使用了旧式写法，现代标准建议使用 VALUES() 函数或直接赋值变量名。此语句若存在则更新 sex 和 address，不存在则全字段插入。

第3步：删除后重新插入（REPLACE INTO）

目的：如果记录存在，先删除旧记录（触发 DELETE 触发器），再插入新记录。这意味着自增 ID 可能会发生变化。

前提条件：必须有主键或唯一索引。

操作示例：

REPLACE INTO user (username, sex, address) 
VALUES ('hanpang', 'boy', 'HongKong');

风险提示：由于涉及先删后加的操作，REPLACE 的性能低于前两种，且可能影响外键关联或导致主键 ID 不连续。请谨慎用于高并发或有严格审计需求的场景。

无索引条件下的去重插入

如果表中未设置主键或唯一索引，上述三种快捷语法均无效。此时需使用子查询进行显式判断。

第4步：条件检查插入（INSERT INTO ... WHERE NOT EXISTS）

目的：通过查询判断数据是否已存在，仅在不存在时执行插入。

操作示例：

INSERT INTO user (username, sex, address)
SELECT 'hanpang', 'boy', 'HongKong' FROM user
WHERE NOT EXISTS (
    SELECT username FROM user WHERE username = 'hanpang'
);

原理：外层 INSERT 从派生表中选取值，但派生表仅在不满足内部 NOT EXISTS 条件时才返回行，从而实现防重。

高效批量操作指南

在处理大量数据时，单条循环插入效率极低。以下是几种高效的批量处理方案。

第5步：多值批量插入（VALUES List）

目的：将多条数据合并为一条 SQL 语句执行，大幅减少网络交互和解析开销。

操作示例：

CREATE TABLE example (
    example_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    value VARCHAR(50) NOT NULL,
    other_value VARCHAR(50) NOT NULL
);

INSERT INTO example 
VALUES 
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

注意事项： 1. max_allowed_packet：如果数据量极大（如百万级），拼接的 SQL 字符串可能超过 MySQL 配置的 max_allowed_packet 大小限制，导致报错。建议分批提交或在代码层控制单次插入行数。 2. 实际生产中，推荐在应用层使用事务包裹批量插入，以确保原子性。

第6步：批量更新技巧

当需要更新多条记录的特定字段时，以下几种方法各有优劣：

方案 A：使用 INSERT ... ON DUPLICATE KEY UPDATE 批量更新

适合有明确主键或唯一键约束的表，兼具插入与更新功能。

INSERT INTO T(A, B, C, D) VALUES 
(a1, b1, c1, d1), 
(a2, b2, c2, d2)
ON DUPLICATE KEY UPDATE 
C = VALUES(C), 
D = VALUES(D);

方案 B：使用 CASE WHEN 构建动态 UPDATE

适合只需根据 ID 更新少量列的场景，无需依赖唯一索引作为触发点。

UPDATE yourtable
SET dingdan = CASE id
    WHEN 1 THEN 3
    WHEN 2 THEN 4
    WHEN 3 THEN 5
END
WHERE id IN (1, 2, 3);

方案 C：创建临时表进行关联更新

适合超大规模数据的更新，将新数据存入临时表，再通过 JOIN 更新原表，利用索引加速。

-- 1. 创建临时表
CREATE TEMPORARY TABLE tmp (
    id INT(4) PRIMARY KEY,
    dr VARCHAR(50)
);

-- 2. 插入临时数据（可大批量）
INSERT INTO tmp VALUES (0,'gone'), (1,'xx'), ...;

-- 3. 关联更新原表
UPDATE test_tbl, tmp 
SET test_tbl.dr = tmp.dr 
WHERE test_tbl.id = tmp.id;

-- 4. 清理临时表
DROP TEMPORARY TABLE tmp;

总结：选择合适的插入与更新策略取决于业务对数据一致性、性能以及主键完整性的要求。对于常规去重，优先使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE；对于高性能大批量写入，务必结合事务和多值语法进行优化。

以上就是MySQL中几种插入和批量语句的详细内容，更多关于MySQL插入和批量语句的资料请关注本站其它相关文章！