防止 MySQL 重复插入自动递增

Question

使用 MySQL 5.1.49，我正在尝试实现一个标记系统 我遇到的问题是一个包含两列的表：id(autoincrement),tag(unique varchar)(InnoDB)

当使用查询时， INSERT IGNORE INTO tablename SET tag="whatever"，即使插入被忽略，自动递增id值也会增加。

通常这不会是一个问题，但我预计会有很多可能的尝试为这个特定的表插入重复项，这意味着我的新行的 id 字段的下一个值将跳跃太多。

例如，我最终会得到一个包含 3 行但 id 不正确的表。

1   | test
8   | testtext
678 | testtextt

另外，如果我不执行 INSERT IGNORE 操作，而只执行常规 INSERT INTO 并处理错误，自动增量字段仍然增加，因此下一个真正的插入仍然是错误的自动增量。

如果尝试插入重复行，是否有办法停止自动增量？

据我了解，对于 MySQL 4.1，这个值不会增加，但我想做的最后一件事是最终要么提前执行大量 SELECT 语句来检查标签是否存在，或者更糟，降级我的MySQL版本。

Answer 1

您可以将 INSERT 修改为如下所示：

INSERT INTO tablename (tag)
SELECT $tag
FROM tablename
WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)
LIMIT 1

其中 $tag 是您要添加的标签（正确引用或作为占位符）（如果它尚不存在）。如果标签已经存在，这种方法甚至不会触发 INSERT（以及随后的自动增量浪费）。您可能会想出比这更好的 SQL，但上面的方法应该可以解决问题。

如果你的表被正确索引，那么用于存在性检查的额外 SELECT 将会很快，并且数据库无论如何都必须执行该检查。

但这种方法不适用于第一个标签。您可以使用您认为最终会被使用的标签来播种标签表，或者您可以对空表进行单独检查。

Answer 2

我刚刚找到了这个宝石...

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
)

如果affectedRows = 1则插入；否则，如果affectedRows = 0，则存在重复项。

Answer 3

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

从版本 5.1 开始，InnoDB 具有可配置的自动增量锁定。另请参阅http://dev.mysql。 com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc...

解决方法：使用选项 innodb_autoinc_lock_mode=0 （传统）。

Answer 4

我发现 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

用“假”表（select 1）替换from子句中的表（select 1）作为允许该部分返回允许插入发生的记录。我正在运行 mysql 5.5.37。谢谢你带我去那里的大部分路......

Answer 5

接受的答案很有用，但是我在使用它时遇到了一个问题，基本上如果您的表没有条目，它将无法工作，因为选择正在使用给定的表，所以我想出了以下内容，即使表是空白的，也只需要在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*/

希望你觉得它有用

Answer 6

您始终可以添加ON DUPLICATE KEY UPDATE

来自@ravi 的评论

增量是否发生取决于
innodb_autoinc_lock_mode 设置。如果设置为非零值，则
即使 ON DUPLICATE KEY 触发，自动增量计数器也会递增

Answer 7

我遇到了同样的问题，但不想使用 innodb_autoinc_lock_mode = 0 因为感觉就像我正在用榴弹炮杀死一只苍蝇。

为了解决这个问题，我最终使用了临时表。

create temporary table mytable_temp like mytable;

然后我插入了以下值：

insert into mytable_temp values (null,'valA'),(null,'valB'),(null,'valC');

之后，您只需执行另一次插入，但使用“not in”来忽略重复项。

insert into mytable (myRow) select mytable_temp.myRow from mytable_temp 
where mytable_temp.myRow not in (select myRow from mytable);

我还没有测试过它的性能，但它可以完成工作并且易于阅读。当然，这很重要，因为我正在处理不断更新的数据，所以我不能忽视这些差距。

Answer 8

修改了mu的答案太短，（只需删除一行）
由于我是新手，我无法在他的回答下发表评论。只需将其发布到此处，

下面的查询适用于第一个标签

 INSERT INTO tablename (tag)
 SELECT $tag
 WHERE NOT EXISTS(
    SELECT tag
    FROM tablename
    WHERE tag = $tag
)

Answer 9

我只是在插入/更新查询之后添加了一条额外的语句：
更改表表名 AUTO_INCRMENT = 1
然后他自动选取最高的prim key id加1。