插入忽略仍然自动增量，替代SQL查询？

Question

$q = $db->prepare('INSERT IGNORE INTO Cities (Name,Stamp) VALUES (?,?)');
$q->execute(array($city,$stamp));

我运行此代码是为了在城市表中插入新的城市。我只是注意到每个查询都会增加id字段，这不是我想要的。这并不是说我依赖的id值是无懈可击的，但是每次运行代码时增加值都是荒谬的。

那么，在不执行两个查询(首先选择，然后插入)而不执行auto_increment的情况下，我可以运行什么样的智能SQL查询来提供相同的功能呢？

表说明：

    Field       Type         Null   Key Default Extra
    id          int(11)      NO     PRI NULL    auto_increment
    Name        varchar(100) YES    UNI NULL     
    countryid   int(11)      YES        NULL     
    Stamp       int(11)      YES        NULL     
    uid         int(11)      YES        NULL    

守则：

INSERT INTO Cities (Name,Stamp) VALUES (?,?) ON DUPLICATE KEY UPDATE Name = Name

增加表上的值。MySQL版本为5.5.9。数据库引擎是innoDB。

可能是这个bug：http://bugs.mysql.com/bug.php?id=28781，如果你向下滚动到底部，你会发现它仍然是一个问题，三年后.

Answer 1

请参见使用INSERT忽略时使用InnoDB增加间隙 (它实际上不是一个bug)。

您需要配置innodb_autoinc_lock_mode=0，或者更改语句。有关更多信息，请参见相关的InnoDB中的增量处理文档。

这是你所看到的行为的总结。在InnoDB中引入了在MySQL 5.1.22中分配自动增量值的新方法：

对于在处理语句开始时已知要插入的行数的INSERT语句，InnoDB会在不使用任何锁的情况下快速分配所需的自动增量值数量，但只有在没有并发会话已经持有表级自动-INC锁的情况下(因为其他语句将一个接一个地分配自动增量值)。更准确地说，这样的INSERT语句在互斥(一种重量较轻的锁)的控制下获得了自动增量值，直到语句完成，但只在分配过程的持续时间内保持。

简而言之，MySQL能够确定语句将插入多少行，并预先分配自动增量值。这样可以防止多个并发语句交织自动增量值.然而，一旦分配了这个数字，就不能再使用它了。这就引入了在自动增量值中出现空白的可能性。

以下语句适用于我，而不增加自动增量值，并使用innodb_autoinc_lock_mode = 1 (默认设置)：

INSERT INTO Cities (Name, Stamp)
SELECT $Name, $Stamp
  FROM Cities
 WHERE NOT EXISTS (
    SELECT NULL
      FROM Cities
     WHERE Name = $Name
);

Answer 2

名字或邮票是唯一的吗？

否则，值会增加，因为插入了相同的行。但是我想你应该注意到如果你的桌子上有那么多行？

Answer 3

$q = $db->prepare('INSERT INTO Cities (Name,Stamp) VALUES (?,?) ON DUPLICATE KEY UPDATE Name = Name');
$q->execute(array($city,$stamp));