防止 MySQL 重复插入的自动增量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5924762/
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
Prevent auto increment on MySQL duplicate insert
提问by robert
Using MySQL 5.1.49, I'm trying to implement a tagging system
the problem I have is with a table with two columns: id(autoincrement), tag(unique varchar)(InnoDB)
使用 MySQL 5.1.49，我正在尝试实现一个标记系统，我遇到的问题是带有两列的表：id(autoincrement), tag(unique varchar)(InnoDB)
When using query, INSERT IGNORE INTO tablename SET tag="whatever", the auto increment idvalue increases even if the insert was ignored.
使用查询时，即使插入被忽略INSERT IGNORE INTO tablename SET tag="whatever"，自动增量id值也会增加。
Normally this wouldn't be a problem, but I expect a lot of possible attempts to insert duplicates for this particular table which means that my next value for idfield of a new row will be jumping way too much.
通常这不会成为问题，但我预计会有很多可能的尝试为这个特定的表插入重复项，这意味着我id的新行字段的下一个值将跳跃太多。
For example I'll end up with a table with say 3 rows but bad id's
例如，我最终会得到一个有 3 行但坏id的表
1 | test
8 | testtext
678 | testtextt
Also, if I don't do INSERT IGNOREand just do regular INSERT INTOand handle the error, the auto increment field still increases so the next true insert is still a wrong auto increment.
另外，如果我不这样做INSERT IGNORE而只是做常规INSERT INTO并处理错误，自动增量字段仍然会增加，因此下一个真正的插入仍然是错误的自动增量。
Is there a way to stop auto increment if there's an INSERTduplicate row attempt?
如果有INSERT重复的行尝试，有没有办法停止自动增量？
As I understand for MySQL 4.1, this value wouldn't increment, but last thing I want to do is end up either doing a lot of SELECTstatements in advance to check if the tags exist, or worse yet, downgrade my MySQL version.
正如我对 MySQL 4.1 的理解，这个值不会增加，但我想做的最后一件事是SELECT提前做很多语句来检查标签是否存在，或者更糟糕的是，降级我的 MySQL 版本。
采纳答案by mu is too short
You could modify your INSERT to be something like this:
您可以将 INSERT 修改为如下所示：
INSERT INTO tablename (tag)
SELECT $tag
FROM tablename
WHERE NOT EXISTS(
SELECT tag
FROM tablename
WHERE tag = $tag
)
LIMIT 1
Where $tagis the tag (properly quoted or as a placeholder of course) that you want to add if it isn't already there. This approach won't even trigger an INSERT (and the subsequent autoincrement wastage) if the tag is already there. You could probably come up with nicer SQL than that but the above should do the trick.
如果$tag标签不存在，您要添加的标签（正确引用或作为占位符）在哪里。如果标签已经存在，这种方法甚至不会触发 INSERT（以及随后的自动增量浪费）。您可能会想出比这更好的 SQL，但以上应该可以解决问题。
If your table is properly indexed then the extra SELECT for the existence check will be fast and the database is going to have to perform that check anyway.
如果您的表被正确索引，那么用于存在检查的额外 SELECT 将很快，并且数据库无论如何都必须执行该检查。
This approach won't work for the first tag though. You could seed your tag table with a tag that you think will always end up being used or you could do a separate check for an empty table.
不过，这种方法不适用于第一个标签。你可以用一个你认为最终会被使用的标签来为你的标签表做种子，或者你可以单独检查一个空表。
回答by Jamie
I just found this gem...
我刚刚发现了这个宝石......
http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/
http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/
INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL
WHERE NOT EXISTS(
SELECT [column1] FROM [same table name]
WHERE [column1]='[value1]'
AND [column2]='[value2]' LIMIT 1
)
If affectedRows = 1 then it inserted; otherwise if affectedRows = 0 there was a duplicate.
如果受影响的行 = 1，则插入；否则，如果受影响的行 = 0，则存在重复项。
回答by Per Quested Aronsson
The MySQL documentation for v 5.5 says:
v 5.5 的 MySQL 文档说：
"If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter
is **not** incremented and LAST_INSERT_ID() returns 0,
which reflects that no row was inserted."
Ref: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id
参考：http: //dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id
Since version 5.1 InnoDB has configurable Auto-Increment Locking. See also http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...
自 5.1 版以来，InnoDB 具有可配置的自动增量锁定。另见http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...
Workaround: use option innodb_autoinc_lock_mode=0 (traditional).
解决方法：使用选项 innodb_autoinc_lock_mode=0（传统）。
回答by Landon
I found mu is too short's answer helpful, but limiting because it doesn't do inserts on an empty table. I found a simple modification did the trick:
我发现 mu 太短的答案很有帮助，但有限制，因为它不会在空表上插入。我发现一个简单的修改就成功了：
INSERT INTO tablename (tag)
SELECT $tag
FROM (select 1) as a #this line is different from the other answer
WHERE NOT EXISTS(
SELECT tag
FROM tablename
WHERE tag = $tag
)
LIMIT 1
Replacing the table in the from clause with a "fake" table (select 1) as aallowed that part to return a record which allowed the insert to take place. I'm running mysql 5.5.37. Thanks mu for getting me most of the way there ....
用“假”表替换 from 子句中的表(select 1) as a允许该部分返回允许插入发生的记录。我正在运行 mysql 5.5.37。感谢穆让我一路走来......
回答by Itay Moav -Malimovka
You can always add ON DUPLICATE KEY UPDATERead here(not exactly, but solves your problem it seems).
您可以随时在ON DUPLICATE KEY UPDATE此处添加Read（不完全是，但似乎可以解决您的问题）。
From the comments, by @ravi
来自评论，@ravi
Whether the increment occurs or not depends on the innodb_autoinc_lock_mode setting. If set to a non-zero value, the auto-inc counter will increment even if the ON DUPLICATE KEY fires
是否发生增量取决于 innodb_autoinc_lock_mode 设置。如果设置为非零值，即使 ON DUPLICATE KEY 触发，auto-inc 计数器也会增加
回答by Thomas B
I had the same problem but didn't want to use innodb_autoinc_lock_mode = 0 since it felt like I was killing a fly with a howitzer.
我有同样的问题，但不想使用 innodb_autoinc_lock_mode = 0 ，因为感觉就像我在用榴弹炮杀死一只苍蝇。
To resolve this problem I ended up using a temporary table.
为了解决这个问题，我最终使用了一个临时表。
create temporary table mytable_temp like mytable;
Then I inserted the values with:
然后我插入了值：
insert into mytable_temp values (null,'valA'),(null,'valB'),(null,'valC');
After that you simply do another insert but use "not in" to ignore duplicates.
之后，您只需执行另一个插入操作，但使用“不在”来忽略重复项。
insert into mytable (myRow) select mytable_temp.myRow from mytable_temp
where mytable_temp.myRow not in (select myRow from mytable);
I haven't tested this for performance, but it does the job and is easy to read. Granted this was only important because I was working with data that was constantly being updated so I couldn't ignore the gaps.
我还没有测试过它的性能，但它可以完成工作并且易于阅读。当然，这很重要，因为我正在处理不断更新的数据，所以我不能忽视这些差距。
回答by Joseph Bailey
The accepted answer was useful, however I ran into a problem while using it that basically if your table had no entries it would not work as the select was using the given table, so instead I came up with the following, which will insert even if the table is blank, it also only needs you to insert the table in 2 places and the inserting variables in 1 place, less to get wrong.
接受的答案很有用，但是我在使用它时遇到了一个问题，基本上如果你的表没有条目它就不会工作，因为选择使用给定的表，所以我想出了以下内容，即使表格是空白的，它也只需要你在2个地方插入表格，在1个地方插入变量，少出错。
INSERT INTO database_name.table_name (a,b,c,d)
SELECT
i.*
FROM
(SELECT
$a AS a,
$b AS b,
$c AS c,
$d AS d
/*variables (properly escaped) to insert*/
) i
LEFT JOIN
database_name.table_name o ON i.a = o.a AND i.b = o.b /*condition to not insert for*/
WHERE
o.a IS NULL
LIMIT 1 /*Not needed as can only ever be one, just being sure*/
Hope you find it useful
希望你觉得它有用
回答by Wopke
I just put an extra statement after the insert/update query:
ALTER TABLE table_nameAUTO_INCREMENT = 1
And then he automatically picks up the highest prim key id plus 1.
我只是在插入/更新查询之后添加了一个额外的语句：ALTER TABLE table_nameAUTO_INCREMENT = 1 然后他会自动选择最高的主键 ID 加 1。
