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 值会上升.因此，如果执行了一大堆 UPDATES，则 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?
******EDIT****:似乎在 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?
******EDIT 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，放在一个 交易:
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 ;
相关文章