SERIAL INT列 [英] SERIAL-like INT column

问题描述

我有一个应用程序，其中根据要添加或更新的交易类型，票证编号可能会或可能不会增加.我不能将SERIAL数据类型用于票证号码，因为无论交易类型如何，它都会增加，因此我将票证号码定义为INT.因此，在多用户环境中，如果用户A正在添加或更新交易，并且用户B也正在这样做，则我会测试tran类型，如果需要下一个票证号，则

I have an app where depending on the type of transaction being added or updated, the ticket number may or may not increment. I can't use a SERIAL datatype for ticket number because it would increment regardless of the transaction type, so I defined ticket number as an INT. So in a multi-user environment if user A is adding or updating a transaction and user B is also doing the same, I test for tran type and if next ticket number is required, then

LET ticket = (SELECT MAX(ticket) [WITH ADDLOCK or UPDLOCK?] FROM transactions) + 1

但是，必须在提交该行时完全执行此操作，否则麻烦就会开始.您能想到一种更好的方法来执行此操作吗:Informix，Oracle，MySQL，SQL-Server，4Js/Genero或其他RDBMS?这是一个主要因素，它将决定我要在其中重新编写应用程序的RDBMS.

However this has to be done exactly when the row is being committed or troubles will begin. Can you think of a better way of doing this with: Informix, Oracle, MySQL, SQL-Server, 4Js/Genero or other RDBMS? This is one main factor which will determine what RDBMS I'm going to re-write my app in.

推荐答案

对于Informix DBMS，SERIAL列在插入后将不会更改.实际上，您根本无法更新SERIAL值.您可以插入一个值为0的新值-在这种情况下，将生成一个新值-或可以插入其他值.如果另一个值已经存在并且存在唯一约束，那么它将失败；否则，将失败.如果它不存在，或者序列列上没有唯一约束，那么它将成功.如果插入的值大于先前插入的最大值，则下一个要插入的数字将再次大一个.如果插入的数字较小或为负，则对下一个数字没有影响.

With the Informix DBMS, the SERIAL column will not change after it is inserted; indeed, you cannot update a SERIAL value at all. You can insert a new one with either 0 as the value - in which case a new value is generated - or you can insert some other value. If the other value already exists and there is a unique constraint, that will fail; if it does not exist, or if there is no unique constraint on the serial column, then it will succeed. If the value inserted is larger than the largest value previously inserted, then the next number to be inserted will be one larger again. If the number inserted is smaller, or negative, then there is no effect on the next number.

因此，您可以在不更改值的情况下进行更新-没问题.如果需要更改编号，则必须执行删除并插入(或插入并删除)操作，其中插入的位置为零.如果您希望保持一致性并使用事务，则可以始终删除，然后(重新)插入具有相同编号或零的行以触发新编号.假设您有一种运行SQL的编程语言.我认为您无法调整ISQL和Perform以自动执行该操作.

So, you could do your update without changing the value - no problem. If you need to change the number, you will have to do a delete and insert (or insert and delete), where the insert has a zero in it. If you prefer consistency and you use transactions, you could always delete, and then (re)insert the row with the same number or with a zero to trigger a new number. This assume you have a programming language running the SQL; I don't think you can tweak ISQL and Perform to do that automatically.

因此，在这一点上，我看不到Informix上的问题.

So, at this point, I don't see the problem on Informix.

使用适当版本的IDS(受支持的任何版本)，您也可以使用SEQUENCE来控制插入的值.这基于Oracle语法和概念.DB2也支持这一点.其他DBMS具有其他等效(但不同)的机制来处理自动生成的数字.

With the appropriate version of IDS (anything that is supported), you can use SEQUENCE to control the values inserted too. This is based on the Oracle syntax and concept; DB2 also supports this. Other DBMS have other equivalent (but different) mechanisms for handling the auto-generated numbers.

这篇关于SERIAL INT列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！