京公网安备11010502063487号ERROR 1062: Duplicate entry 看起来很直白,但真正查起来问题很多:报错里 for key 说的是哪个索引?为什么两个看起来不一样的值也冲突?INSERT IGNORE、ON DUPLICATE KEY UPDATE、REPLACE 到底有什么区别?为什么报错之后自增 id 出现了空洞?这篇在本机一个个复现。
主键冲突: insert into u(id, email, a, b) values (1, 'y@x.com', 2, 2)
-> IntegrityError: (1062, "Duplicate entry '1' for key 'u.PRIMARY'")
唯一索引冲突: insert into u(email, a, b) values ('x@x.com', 3, 3)
-> IntegrityError: (1062, "Duplicate entry 'x@x.com' for key 'u.uk_email'")
联合唯一索引冲突: insert into u(email, a, b) values ('z@x.com', 1, 1)
-> IntegrityError: (1062, "Duplicate entry '1-1' for key 'u.uk_ab'")
没起名的 unique:insert into w values (2, 'k')
-> IntegrityError: (1062, "Duplicate entry 'k' for key 'w.email2'")
唯一索引列允许多个 NULL:连续插入两行 email=NULL -> ok;表里 email 为 NULL 的行数 = 2for key 后面是“表名.索引名”,联合唯一索引的冲突值用 - 连接,没起名的 unique 用第一个列名当索引名。唯一索引允许多个 NULL。
默认字符集和排序规则: ('utf8mb4', 'utf8mb4_0900_ai_ci')
排序规则 大小写 重音 末尾空格
utf8mb4_0900_ai_ci(8.0 以后的默认) 重复(1062) 重复(1062) 不重复
utf8mb4_general_ci 重复(1062) 重复(1062) 重复(1062)
utf8mb4_bin 不重复 不重复 重复(1062)唯一索引按列的排序规则比较:默认的 utf8mb4_0900_ai_ci 不区分大小写和重音,但区分末尾空格;utf8mb4_bin 区分大小写,却忽略末尾空格。
insert into m values (1,..),(2,..),(3,..)(第 2 行冲突,表里原来有 id=2)-> IntegrityError: (1062, "Duplicate entry '2' for key 'm.PRIMARY'")
表里现在的行: ((2, 'old'),)(第 1、3 行没有写进去)
insert ignore 同样三行 -> ok,影响行数 2
show warnings: (('Warning', 1062, "Duplicate entry '2' for key 'm.PRIMARY'"),)
表里现在的行: ((1, 'n1'), (2, 'old'), (3, 'n3'))(id=2 仍然是 old,冲突的那行被悄悄丢掉)普通 INSERT 一行冲突整条回滚;INSERT IGNORE 跳过冲突行,留下 warning,新值被丢掉而不报错,需要自己检查 warning。
起始: ((1, 'a@x.com', 'A', 5),) ON DUPLICATE KEY UPDATE 改 name -> 影响行数 2;表: ((1, 'a@x.com', 'A2', 5),)(id、hits 保留) 再来一次,值没有变化 -> 影响行数 0 新邮件 b@x.com(没有冲突,走插入) -> 影响行数 1 REPLACE INTO(email 冲突) -> 影响行数 2;表: ((4, 'b@x.com', 'B', 0), (5, 'a@x.com', 'A3', 0))(a@x.com 的 id 变了,hits 回到默认 0)
ON DUPLICATE KEY UPDATE 原地更新,保留 id 和没提到的列,影响行数更新为 2、没变化为 0、插入为 1。REPLACE 先删后插,id 变,没写的列回到默认值。
innodb_autoinc_lock_mode = 2 先插 a(id=1),再让 3 次 insert k=a 报 1062,再插 b: ((1, 'a'), (5, 'b')) 再让 2 次 insert ignore k=a 被跳过,再插 c: ((1, 'a'), (5, 'b'), (8, 'c')) 再来 1 次 ON DUPLICATE KEY UPDATE(命中 b,没有变化),再插 d: ((1, 'a'), (5, 'b'), (8, 'c'), (10, 'd'))
InnoDB 默认的锁模式下,触发唯一冲突的语句无论最后报错、被忽略还是转成更新,都消耗了自增 id。id 不会因此重复,但不连续。
没有唯一索引:SELECT 看到“还没有”的次数 400 / 400,INSERT 报 1062 的次数 0,表里一共 400 行,不同的邮箱 50 个 有唯一索引:SELECT 看到“还没有”的次数 400 / 400,INSERT 报 1062 的次数 350,表里一共 50 行,不同的邮箱 50 个 结论:“先查后插”只是一次检查,不是约束;唯一索引才是。1062 在这里是数据库在拦重复,代码里捕获它(或者直接用 INSERT … ON DUPLICATE KEY UPDATE)才是正解
检查和插入之间有时间差,并发时每个线程都看到“还没有”。唯一索引在这段空隙里依然有效,1062 就是它在拦截。