带有ON DUPLICATE KEY UPDATE的自动增量太多 [英] Too many auto increments with ON DUPLICATE KEY UPDATE

问题描述

我有一个带有列的基本表:

I have a basic table with columns:

• id(主要使用AI)
• 名称(唯一)
• 等

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

If the unique column doesn't exist, INSERT the row, otherwise UPDATE the row....

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

问题在于，如果它执行UPDATE，则id列上的auto_increment值会增加.因此，如果执行了大量更新，则id auto_increment会通过屋顶.

The problem is that if it performs an UPDATE, the auto_increment value on the id column goes up. So if a whole bunch of UPDATES are performed, the id auto_increment goes through the roof.

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

...但是我在共享主机上在MySQL 5.5.8上使用InnoDB.

...but I'm using InnoDB on mySQL 5.5.8 on shared hosting.

几年前没有解决方案的其他人: 防止MYSQL重复插入的自动增量， 为什么在失败的插入中MySQL自动增加?

Other people having issues with no solution years ago: prevent autoincrement on MYSQL duplicate insert and Why does MySQL autoincrement increase on failed inserts?

想法解决了吗?我是否可能以某种方式不正确地构造了数据库?

Ideas on a fix? Have I maybe structured the database incorrectly somehow?

******编辑****:似乎在您的my.ini文件中添加了innodb_autoinc_lock_mode = 0可以解决此问题，但是对于共享主机我有什么选择?

******EDIT****: It appears adding innodb_autoinc_lock_mode = 0 to your my.ini file fixes the problem but what options do I have for shared hosting?

******编辑2 ******:好的，我认为我唯一的选择是更改为MyISAM作为存储引擎.作为一个大型的mySQL新手，我希望不会引起很多问题.是吗?

******EDIT 2******: OK, I think my only option is to change to MyISAM as the storage engine. Being a mega mySQL newbie, I hope that doesn't cause many issues. Yeah?

推荐答案

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

I don't think there is a way to bypass this behaviour of INSERT ... ON DUPLICTE KEY UPDATE.

但是，您可以在一个UPDATE和一个INSERT. ="noreferrer">交易:

You can however put two statements, one UPDATE and one INSERT, in one transaction:

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 ;

这篇关于带有ON DUPLICATE KEY UPDATE的自动增量太多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！