如何在一个表中创建多个序列？ [英] How to create multiple sequences in one table?

问题描述

我有一张表收据。我有列customer_id（谁有收据）和receipt_number。对于每个客户，receipt_number应该从1开始，并且是序列。这意味着customer_id和receipt_number将是唯一的。我如何优雅地这样做。我可以使用CREATE SEQUENCE或类似的内置的sequeance功能吗？它似乎需要为每个客户创建一个序列，这当然不是一个优雅的解决方案。

I have a table "receipts". I have columns customer_id (who had the receipt) and receipt_number. The receipt_number should start on 1 for each customer and be a sequence. This means that customer_id and receipt_number will be unique. How can I elegantly do this. Can I use the built-in sequeance functionality with CREATE SEQUENCE or similar? It seems like I would have to create a sequence for each customer, which of course is not an elegant solution.

编辑：必须有一个线程安全和idiot-安全的方式来做到这一点。这应该是一个很简单/常见的需要。

There must be a thread-safe and idiot-secure way to do this. It should be quite a simple/common need.

推荐答案

SEQUENCE不保证没有空白。例如，一个事务可能生成一个新的数字，然后中止（由于错误或电源故障或任何...）。下一个事务将盲目获得下一个数字，而不是那个丢失的。

SEQUENCE does not guarantee there are no gaps. For example, one transaction might generate a new number and then abort (due to a bug or a power failure or whatever...). The next transaction would then blindly get the next number, not the one that was "lost".

如果你的客户端应用程序不依赖 假设在第一个地方。然而，您可以最小化这样的差距：

It would be best if your client application did not depend on "no gaps" assumption in the firs place. You could, however, minimize gaps like this:

1. SELECT MAX（receipt_number）FROM receipts WHERE customer_id =：ci


2. INSERT INTO收据（customer_id，receipt_number）VALUES（：ci，aboveresult + 1）只要在 步骤1 返回NULL时插入1.


3. 如果 >返回一个PK违例 ^* ，从头开始重试。

1. SELECT MAX(receipt_number) FROM receipts WHERE customer_id = :ci
2. INSERT INTO receipts(customer_id, receipt_number) VALUES (:ci, aboveresult+1), or just insert 1 if step 1 returned NULL.
3. If step 2 returned a PK violation^*, retry from the beginning.

^*

由于并发事务已经通过相同的过程并提交。因为只要添加行而不删除行，

As long as rows are just added and not deleted, this should prevent any gaps, even in a concurrent environment.

BTW，您可以凝聚 步骤1和2 如下：

BTW, you can "condense" steps 1 and 2 like this:

INSERT INTO receipts (customer_id, receipt_number)
SELECT :ci, COALESCE(MAX(receipt_number), 0) + 1
FROM receipts
WHERE customer_id = :ci;

[SQL Fiddle]

PK {customer_id，receipt_number}下的索引应确保此查询的SELECT部分

The index underneath the PK {customer_id, receipt_number} should ensure that the SELECT part of this query is satisfied efficiently.

这篇关于如何在一个表中创建多个序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！