没有自增字段的数据库上如何实现自增字段？

Question

没有自增字段的数据库上如何实现自增字段？

9

在MS Sql Server中，创建自动递增字段很容易。在我的系统中，我停止使用自动递增字段作为主键，现在我使用Guid。这真是太棒了，我通过这种改变得到了很多好处。但是，在另一些非主键字段中，我确实需要实现“软自动递增”。这是因为我的系统是DB独立的，所以我在c#中编程创建自动递增值。

我想了解没有自动递增的数据库中自动递增字段的解决方案，您使用的解决方案是什么，以及为什么？有关此事是否存在Sql Ansi语句？直接从我的c#生成，是否是更好的解决方案？

PS：我知道“select max(id)+1 from table”并不真正支持并发...

- Click Ok

1

我建议重新编写标题，使其更加清晰，例如“创建独立于数据库的自增字段”。我的担忧是，如果我搜索同一主题，我不认为我会找到您的标题。 - Elijah

你真的需要一个不断增加的数字，还是只需要唯一性？ - Don Neufeld

是的，我需要“一个不断递增的数字”，对于其他情况，我使用GUID、CRC等... - Click Ok

5个回答

2

我认为你的问题实际上是一个非常好的问题。然而，试图想出仅使用SQL的解决方案很容易迷失方向。事实上，您需要使用自动增量类型的数据库实现所提供的优化和事务安全性。

如果您需要抽象出自动增量运算符的实现，为什么不创建一个存储过程来返回您的自动增量值呢？大多数SQL方言以相对相同的方式访问存储过程，这应该更具可移植性。然后，您可以在创建sproc时创建特定于数据库的自动增量逻辑，从而消除了将许多语句更改为供应商特定的需求。

以这种方式完成，您的插入操作可以像这样简单：

INSERT INTO foo (id, name, rank, serial_number)
 VALUES (getNextFooId(), 'bar', 'fooRank', 123456);

在初始化数据库时，以特定于数据库的方式定义getNextFooId()。

- Elijah

1

大多数没有自动增量字段的数据库（例如SQL Server，我特别想到Oracle）都有序列，您可以向序列请求下一个数字。无论有多少人同时请求数字，每个人都会得到一个唯一的数字。

- mrdenny

1

传统的解决方案是拥有一个ID表格，看起来像这样：

CREATE TABLE ids (
  tablename VARCHAR(32) NOT NULL PRIMARY KEY,
  nextid INTEGER
)

在创建数据库时，每个表都会填充一个行。

然后，您可以执行选择操作以获取要插入的表的下一个ID，将其递增，然后使用新ID更新表。显然，在这里存在锁定问题，但对于具有中等插入速率的数据库，它运行良好。而且它是完全可移植的。

- anon

2

此解决方案存在竞态条件，并且每个主要的关系型数据库都有一个事务安全的替代方案。 - Bill Karwin

0

如果您需要一个非主键自增字段，一个非常好的MySQL解决方案是使用相对较少知名的last_insert_id(expr)函数来创建任意序列。

如果将expr作为参数传递给LAST_INSERT_ID()，则该参数的值将被函数返回，并记忆为LAST_INSERT_ID()要返回的下一个值。这可以用于模拟序列...

(来自http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id)

以下是一个示例，演示如何为每个帖子编号评论的次要序列：

CREATE TABLE  `post` (
  `id` INT(10) UNSIGNED NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `comment_sequence` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE  `comment` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`  INT(10) UNSIGNED NOT NULL,
  `sequence` INT(10) UNSIGNED NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO post(id, title) VALUES(1, 'first post');
INSERT INTO post(id, title) VALUES(2, 'second post');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), 'blah');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), 'foo');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), 'bar');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=2;
INSERT INTO `comment`(post_id, sequence, content) VALUES(2, Last_insert_id(), 'lorem');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=2;
INSERT INTO `comment`(post_id, sequence, content) VALUES(2, Last_insert_id(), 'ipsum');

SELECT * FROM post;
SELECT * FROM comment;

- Udi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

生成唯一id值的机制不能受事务隔离级别的影响。这是为了数据库能够为每个客户端生成不同的值，而不是使用SELECT MAX(id)+1 FROM table技巧，如果两个客户端同时尝试分配新的id值，就会导致竞争条件。

使用标准SQL查询无法模拟此操作（除非使用表锁或可串行化事务）。它必须是内置到数据库引擎中的机制。

ANSI SQL 直到 SQL:2003 才描述了用于生成代理键的唯一值的操作。在此之前，没有自动递增列的标准，因此几乎每个品牌的 RDBMS 都提供了一些专有解决方案。当然它们有很大不同，没有简单、独立于数据库的方法来使用它们。

MySQL 有 AUTO_INCREMENT 列选项，或等效于 BIGINT UNSIGNED AUTO_INCREMENT 的 SERIAL 伪数据类型；
Microsoft SQL Server 有 IDENTITY 列选项和 NEWSEQUENTIALID()，介于自动递增和 GUID 之间；
Oracle 有一个 SEQUENCE 对象；
PostgreSQL 有一个 SEQUENCE 对象，或根据命名约定隐含地创建序列对象的 SERIAL 伪数据类型；
InterBase/Firebird 有一个类似于 Oracle 中的 SEQUENCE 的 GENERATOR 对象；Firebird 2.1 还支持 SEQUENCE；
SQLite 将任何声明为主键的整数隐式视为自动递增；
DB2 UDB 几乎具备所有功能：SEQUENCE 对象，或您可以使用 "GEN_ID" 选项声明列。

所有这些机制都在事务隔离之外运作，确保并发客户端获得唯一的值。此外，在所有情况下，都有一种方法可以查询当前会话中最近生成的值。这是必须的，因为您需要使用它来插入子表中的行。