ON DUPLICATE KEY + AUTO INCREMENT 问题 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23516958/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ON DUPLICATE KEY + AUTO INCREMENT issue mysql
提问by newbie
I have table structure like this
我有这样的表结构
when I insert row to the table I'm using this query:
当我向表中插入行时，我正在使用此查询：
INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;
INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;
when I first insert data without triggering the ON DUPLICATE KEYthe id increments fine:
当我第一次插入数据而不触发ON DUPLICATE KEYid 增量时：
but when the ON DUPLICATE KEYtriggers and i INSERT A NEW ROWthe id looks odd to me:
但是当ON DUPLICATE KEY触发器和我插入一个新行时，id 对我来说看起来很奇怪：
How can I keep the auto increment, increment properly even when it triggers ON DUPLICATE KEY?
auto increment即使触发，我如何保持, 增量正确ON DUPLICATE KEY？
回答by Gordon Linoff
This behavior is documented(paragraph in parentheses):
此行为已记录在案（括号中的段落）：
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1;(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)
如果您指定 ON DUPLICATE KEY UPDATE，并且插入的行会导致 UNIQUE 索引或 PRIMARY KEY 中的重复值，则 MySQL 执行旧行的 UPDATE。例如，如果列 a 声明为 UNIQUE 并包含值 1，则以下两个语句具有类似的效果：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1; UPDATE table SET c=c+1 WHERE a=1;（对于其中 a 是自增列的 InnoDB 表，效果并不相同。对于自增列，INSERT 语句会增加自增值，但 UPDATE 不会。）
Here is a simple explanation. MySQL attempts to do the insert first. This is when the id gets auto incremented. Once increment, it stays. Then the duplicate is detected and the update happens. But the value gets missed.
这里有一个简单的解释。MySQL 尝试先执行插入操作。这是 id 自动增加的时候。一旦增加，它就会保持不变。然后检测到重复并进行更新。但是价值被错过了。
You should not depend on auto_incrementhaving no gaps. If that is a requirement, the overhead on the updates and inserts is much larger. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. A better solution is to calculate incremental values on output.
你不应该依赖auto_increment没有间隙。如果这是一个要求，更新和插入的开销要大得多。本质上，您需要锁定整个表，并对需要重新编号的所有内容重新编号，通常使用触发器。更好的解决方案是计算输出的增量值。
回答by Rosski
I had the same frustration and I have a solution.
我有同样的挫败感，我有一个解决方案。
Let me first talk about "overheads". When I first wrote my DB update code, it did so many separate queries that it took 5 hours. Once I put on "ON DUPLICATE KEY UPDATE" I got it down to about 50 seconds. Amazing! Anyway the way I solved it means I have to do 2 queries, so instead of 1 minute I'm extending it to 2 minutes which I think is a fair cost considering.
让我先谈谈“开销”。当我第一次编写我的数据库更新代码时，它做了很多单独的查询，花了 5 个小时。一旦我打开“ON DUPLICATE KEY UPDATE”，我就把它缩短到大约 50 秒。惊人的！无论如何，我解决它的方式意味着我必须进行 2 次查询，因此我将其从 1 分钟延长到 2 分钟，我认为考虑到这一成本是合理的。
First I did a standard sql query with all the data (updates and inserts), but I included "IGNORE" so this just bypasses the updates and only inserts the new stuff. "INSERT IGNORE INTO mytablename(stuff,stuff2) VALUES " -without the on duplicate key update. This means all the new records get put in. These new records wont break the auto_increment yet.
首先，我对所有数据（更新和插入）进行了标准的 sql 查询，但我包含了“IGNORE”，因此这只是绕过更新并仅插入新内容。“INSERT IGNORE INTO mytablename(stuff,stuff2) VALUES” - 没有重复的键更新。这意味着所有新记录都被放入。这些新记录还不会破坏 auto_increment。
Next I did the "ON DUPLICATE KEY UPDATE" version of that sql statement. This doesn't break any of the ID's because all the records already exist. The only thing it breaks is the auto_increment value, which gets used when a new record is added. So the solution is to patch this auto_increment value before you add any new records.
接下来我做了那个 sql 语句的“ON DUPLICATE KEY UPDATE”版本。这不会破坏任何 ID，因为所有记录都已经存在。它唯一中断的是 auto_increment 值，它在添加新记录时使用。因此，解决方案是在添加任何新记录之前修补此 auto_increment 值。
To patch the auto increment value use this sql in your php: "ALTER TABLE mytablename AUTO_INCREMENT = " . ($TableCount + 1);
要修补自动增量值，请在您的 php 中使用此 sql: "ALTER TABLE mytablename AUTO_INCREMENT = "。($TableCount + 1);
remember to actually set $TableCount to the table count, then we add 1 and that's ready for any further updates.
记住实际上将 $TableCount 设置为表计数，然后我们添加 1 并准备好进行任何进一步的更新。
This is cheap and dirty but I like it. Make sure you don't write to the database at the same time using another function etc, because it could give errors. I think there is a way to lock the table? But I don't need that in my case.
这又便宜又脏，但我喜欢它。确保不要同时使用另一个函数等写入数据库，因为它可能会出错。我认为有办法锁定表吗？但就我而言，我不需要那个。
回答by aatofighian
This question is a fairly old one, but I answer it maybe it helps someone, to solve the auto-incrementing problem use the following code before insert/on duplicate update part and execute them all together:
这个问题是一个相当古老的问题，但我回答它可能对某人有所帮助，要解决自动递增问题，请在插入/重复更新部分之前使用以下代码并一起执行它们：
SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL;
together and in one statement it should be something like below:
总之，在一个声明中，它应该如下所示：
SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL;
INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
ON DUPLICATE KEY UPDATE `the_col` = "the_value";
回答by Naxia
I just thought I'd add, as i was trying to find an answer to my problem. I could not stop the duplicate warning and found it was because I had it set it to TINYINT which only allows 127 entries, changing to SMALL/MED/BIGINT allows for many more
我只是想我会补充，因为我试图找到我的问题的答案。我无法停止重复警告并发现这是因为我将其设置为 TINYINT 仅允许 127 个条目，更改为 SMALL/MED/BIGINT 允许更多
回答by Seti
INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE material_item = data, ... hidden = data
INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE material_item = data, ... hidden = data
Yes remove the ID=ID as it will automaticly add where PRIMARY KEY = PRIMARY KEY...
是的，删除 ID=ID，因为它会自动添加 where PRIMARY KEY = PRIMARY KEY ...
