我有一张表收据。我有列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:
*
由于并发事务已经通过相同的过程并提交。因为只要添加行而不删除行,
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.
更多推荐
如何在一个表中创建多个序列?
发布评论