防止 MySQL 重复插入自动递增
使用 MySQL 5.1.49，我正在尝试实现一个标记系统我遇到的问题是一个有两列的表:id(autoincrement), tag(unique varchar) (InnoDB)
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)
使用查询时，INSERT IGNORE INTO tablename SET tag="whatever"，即使插入被忽略，自动递增 id 值也会增加.
When using query, INSERT IGNORE INTO tablename SET tag="whatever", the auto increment id value increases even if the insert was ignored.
通常这不会是一个问题，但我希望有很多可能的尝试为这个特定的表插入重复项，这意味着我的新行的 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 id field of a new row will be jumping way too much.
例如，我最终会得到一个包含 3 行但 id 的表格
For example I'll end up with a table with say 3 rows but bad id's
1 | test
8 | testtext
678 | testtextt
另外，如果我不执行 INSERT IGNORE 而只是执行常规 INSERT INTO 并处理错误，自动增量字段仍然会增加，因此下一个真正的插入是仍然是错误的自动增量.
Also, if I don't do INSERT IGNORE and just do regular INSERT INTO and handle the error, the auto increment field still increases so the next true insert is still a wrong auto increment.
如果有 INSERT 重复行尝试，有没有办法停止自动递增?
Is there a way to stop auto increment if there's an INSERT duplicate row attempt?
据我了解，对于 MySQL 4.1，此值不会增加，但我想做的最后一件事是提前执行大量 SELECT 语句以检查标签是否存在，或者更糟糕的是，降级我的 MySQL 版本.
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 SELECT statements in advance to check if the tags exist, or worse yet, downgrade my MySQL version.
推荐答案
你可以修改你的 INSERT 是这样的:
You could modify your INSERT to be something like this:
INSERT INTO tablename (tag)
SELECT $tag
FROM tablename
WHERE NOT EXISTS(
SELECT tag
FROM tablename
WHERE tag = $tag
)
LIMIT 1
$tag 是您想要添加的标签(正确引用或作为占位符)，如果它不存在的话.如果标签已经存在，这种方法甚至不会触发 INSERT(以及随后的自动增量浪费).您可能会想出比这更好的 SQL，但上面应该可以解决问题.
Where $tag is 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.
如果您的表被正确索引，那么用于存在检查的额外 SELECT 将很快，并且无论如何数据库都必须执行该检查.
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.
这种方法不适用于第一个标签.您可以为您的标签表添加一个您认为最终会被使用的标签，或者您可以单独检查一个空表.
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.
相关文章