Сохранить последовательный первичный ключ во вставке ошибки

Моя команда создания таблицы в 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')

Следующая вставка — город («Медан»), так что это ошибка, потому что столбец города уникален. Следующая вставка — город('Бенгкулу'), а итоговый результат таблицы

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')

mysql unique
person gwijayas    schedule 19.08.2010    source источник
comment
Почему вам нужно, чтобы ваши ключи были последовательными? У вас будут пробелы в ваших идентификаторах, если/когда вы все равно выполняете удаления, поэтому вам не следует рассчитывать на то, что ваши ключи будут смежными.   -  person Dave McClelland    schedule 19.08.2010

Ответы (1)


arrow_upward
0
arrow_downward

Это похоже на поведение InnoDB. InnoDB откатывает транзакцию, но ключ автоинкремента не может быть восстановлен, поскольку он может быть использован кем-то другим. Этого не происходит в таблицах 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
person NickSoft    schedule 17.04.2011