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

发布于 2024-09-15 03:12:35 字数 653 浏览 8 评论 0原文

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

原文

My create table command in mysql is

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`);

Intial value, like:

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

The next insert is city('Medan'), so it's error because city column is unique. The next insert is city('Bengkulu'), and the final table result is

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

It's not id(3) but id(4) instead. So how could I keep sequential primary key eventhough there was/were insert error before?

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

万劫不复 2024-09-22 03:12:35

这看起来像 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

That looks like InnoDB behaviour. InnoDB rolls back the transaction, but autoincrement key can not be restored because it might be used by someone else. This doesn't happen in MyISAM tables, but with InnoDB you have the power to prevent it using transactions. I'll write this in php, but you can repeat it in any language you want:

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

回复收藏 0 原文

~没有更多了~