太多的自增值与 ON DUPLICATE KEY UPDATE 相关

Question

我有一个基本的表格，包含以下列：

• id（主键，自增）
• name（唯一性）
• 等等

如果唯一列不存在，则插入该行；否则更新该行......

INSERT INTO pages (name, etc)
VALUES
  'bob',
  'randomness'
ON DUPLICATE KEY UPDATE
 name = VALUES(name),
 etc = VALUES(etc)

问题在于，如果执行UPDATE操作，id列上的auto_increment值会增加。因此，如果执行了一堆UPDATE操作，id auto_increment的值就会飙升。

显然这是一个bug：http://bugs.mysql.com/bug.php?id=28781，但我在共享主机上使用的是MySQL 5.5.8 InnoDB。

其他人多年前也遇到了类似的问题，但没有解决方案：prevent autoincrement on MYSQL duplicate insert和Why does MySQL autoincrement increase on failed inserts? 有什么修复的想法吗？也许我构建数据库时出了问题？

******编辑****：看起来将innodb_autoinc_lock_mode = 0添加到my.ini文件中可以解决问题，但对于共享主机，我有什么选择？

******编辑2******：好吧，我想我的唯一选择就是更改为MyISAM作为存储引擎。作为一个超级MySQL新手，我希望这不会引起太多问题。是吗？

- Nathan Waters

---

MyISAM 的一个大问题是它不支持定义数据库关系，而 InnoDB 支持。 - bakkerjoeri

innodb_autoinc_lock_mode = 0 对我很有帮助。谢谢。 - Uğur Özpınar

我切换到了Aria（MariaDB中的MyISAM），这可以防止自动增量。 - Daniel W.

innodb_autoinc_lock_mode = 0 会因为使用表锁来处理自增字段而降低并发的 INSERT 速度。这可能是不好的。 - Enyby

Answer 1

我认为没有办法绕过INSERT ... ON DUPLICATE KEY UPDATE的行为。

但是，你可以在一个事务中放置两个语句，一个是UPDATE，另一个是INSERT：

START TRANSACTION ;

UPDATE pages
SET etc = 'randomness'
WHERE name = 'bob' ;

INSERT INTO pages (name, etc)
SELECT 
      'bob' AS name
    , 'randomness' AS etc 
FROM dual 
WHERE NOT EXISTS
      ( SELECT *
        FROM pages p
        WHERE p.name = 'bob'
      ) ;

COMMIT ;

- ypercubeᵀᴹ

---

谢谢，伙计。我最终采用了这种方法。自增问题真是个烦恼。不需要 "AS name" 和 "AS etc" 部分。 - Nathan Waters

不需要那些别名。 - ypercubeᵀᴹ

@NathanWaters：添加了一个关于MySQL事务的文档链接。 - ypercubeᵀᴹ

你能否也做这个？INSERT IGNORE INTO pages (name, etc) SELECT 'bob' AS name , 'randomness' AS etc FROM dual - Timo Huovinen

@TimoHuovinen INSERT IGNORE 与 ON DUPLICATE KEY UPDATE 一样会忽略掉重复的 ID。 - Damian Yerrick

@tepples 是的。此外，根据设置，它还会忽略其他错误。这通常不是好事。 - ypercubeᵀᴹ

Answer 2

MySQL的“on duplicate key”功能与执行两个独立查询完全相同，一个是选择记录，另一个是要么更新所选的记录，要么插入新的记录。在程序上这样做同样快，并且可以避免以后出现此类问题，并使您的代码更具可移植性。

- PFY

---

根据我所看到的，它的速度远不及那么快。 - Timo Huovinen