mysql - 在错误插入中保留顺序主键

Question

我在mysql中的建表命令是

CREATE TABLE `map` (
  `id`  int(4) AUTO_INCREMENT NOT NULL,
  `city`    varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE UNIQUE INDEX `map_city_idx`
  ON `map`
  (`city`);

初始值，例如:

id(1),city('Banda Aceh')
id(2),city('Medan')

下一个插入是 city('Medan')，所以它是错误的，因为 city 列是唯一的。接下来插入city('Bengkulu')，最终表结果为

id(1), city('Banda Aceh')
id(2), city('Medan')
id(4), city('Bengkulu')

它不是 id(3) 而是 id(4)。那么，即使之前存在插入错误，我又该如何保留顺序主键呢？

id(1), city('Banda Aceh')
id(2), city('Medan')
id(3), city('Bengkulu')

Answer 1

这看起来像 InnoDB 的行为。 InnoDB回滚了事务，但是autoincrement key无法恢复，因为它可能被其他人使用了。这不会发生在 MyISAM 表中，但是使用 InnoDB 您可以使用事务来阻止它。我会用 php 来写，但是你可以用你想要的任何语言重复它:

mysql_query('START TRANSACTION');
//query if it exists and lock the record
$r = mysql_query('SELECT id FROM map WHERE city="Medan" FOR UPDATE'); 
if(mysql_num_rows($r)>0){
  //already exists
  // there are better ways to do this, buy you got the idea.
  list($id) = mysql_fetch_row($id);
  mysql_query('ROLLBACK'); // release the lock
}else{
  // does not exists - insert it
  mysql_query('INSERT INTO map(city) VALUES("Medan")');
  $id =  mysql_insert_id();
  mysql_query('COMMIT'); //commit and release the lock
}
//... here $id will be id of 'Medan' city regardless if it is inserted now
//  or it is duplicate and there will be no autoincrement key holes