防止 MySQL 重复插入的自动增量 [英] Prevent auto increment on MySQL duplicate insert

问题描述

使用 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.

这篇关于防止 MySQL 重复插入的自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！