使用重复密钥更新的自动增量太多

Question

我有一个包含列的基本表：

• id (带有AI的主id)
• name (unique)
• etc

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

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

问题是，如果它执行更新，id列上的auto_increment值就会上升。因此，如果执行了一大堆更新，id auto_increment就会达到顶峰。

显然，这是一个错误：http://bugs.mysql.com/bug.php?id=28781

...but我在共享主机上的mySQL 5.5.8上使用InnoDB。

其他几年前没有解决方案的人：prevent autoincrement on MYSQL duplicate insert和Why does MySQL autoincrement increase on failed inserts?

解决方案的点子？我可能以某种方式错误地构造了数据库？

*编辑*：在你的my.ini文件中添加innodb_autoinc_lock_mode =0似乎可以解决这个问题，但是我有什么共享主机的选项呢？

*EDIT 2*：好的，我想我唯一的选择就是改成MyISAM作为存储引擎。作为一个超级mySQL新手，我希望这不会引起太多问题。嗯?

Answer 1

我认为没有一种方法可以绕过INSERT ... ON DUPLICTE KEY UPDATE的这种行为。

但是，您可以在一个transaction中放置两个语句，一个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 ;

Answer 2

MySQL的on duplicate key功能与执行两个单独的查询完全相同，一个用于选择，另一个用于更新所选记录或插入新记录。以编程方式这样做同样快，并且可以防止将来出现这种问题，还可以使您的代码更具可移植性。