类似 SERIAL 的 INT 列
我有一个应用程序，根据添加或更新的交易类型，票号可能会也可能不会增加。我不能使用 SERIAL 数据类型作为票号，因为无论事务类型如何，它都会递增，因此我将票号定义为 INT。因此，在多用户环境中，如果用户 A 正在添加或更新事务，并且用户 B 也在执行相同操作，我会测试 tran 类型，并且如果需要下一个票号，则
LET ticket = (SELECT MAX(ticket) [WITH ADDLOCK or UPDLOCK?] FROM transactions) + 1
必须在该行被添加或更新时准确执行此操作。被承诺或麻烦将会开始。您能想出更好的方法来使用 Informix、Oracle、MySQL、SQL-Server、4Js/Genero 或其他 RDBMS 来实现此目的吗？这是决定我将使用哪种 RDBMS 重写我的应用程序的主要因素。
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
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于 Informix DBMS，SERIAL 列在插入后不会改变；事实上，您根本无法更新 SERIAL 值。您可以插入一个以 0 作为值的新值 - 在这种情况下会生成一个新值 - 或者您也可以插入一些其他值。如果另一个值已经存在并且存在唯一约束，则该操作将会失败；如果不存在，或者串行列没有唯一约束，则它将成功。如果插入的值大于先前插入的最大值，则下一个要插入的数字将再次大一。如果插入的数字较小或为负数，则对下一个数字没有影响。
因此，您可以在不更改值的情况下进行更新 - 没问题。如果需要更改数字，则必须执行删除和插入（或插入和删除），其中插入中包含零。如果您喜欢一致性并且使用事务，则可以随时删除，然后（重新）插入具有相同数字或零的行以触发新数字。假设您有一种运行 SQL 的编程语言；我认为您无法调整 ISQL 和 Perform 来自动执行此操作。
所以，目前我在 Informix 上还没有看到问题。
使用适当版本的 IDS（任何受支持的版本），您也可以使用 SEQUENCE 来控制插入的值。这是基于Oracle语法和概念的； DB2 也支持这一点。其他 DBMS 有其他等效（但不同）的机制来处理自动生成的数字。
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.
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.
So, at this point, I don't see the problem on Informix.
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.
这就是创建序列的目的，并且大多数数据库都支持序列（MySQL 是唯一没有序列的数据库 - 但对于 Informix 不是 100% 确定）
任何依赖于 SELECT MAX(id) 的算法 反模式在多用户环境中要么非常慢，要么在多用户环境中根本无法正常工作。
如果您还需要支持 MySQL，我建议在每个数据库中使用“本机”“自动增量”类型（PostgreSQL 的串行，MySQL 的 auto_increment，SQL Server 的身份，Oracle 中的序列 + 触发器等）并让驱动程序返回生成的 ID 值
在 JDBC 中，有一个 getGenerateKeys() 方法，我确信其他接口也有类似的方法。
That's what sequences were created for and which is supported by most databases (MySQL being the only one that does not have sequences - not 100% sure about Informix though)
Any algorithm that relies on the SELECT MAX(id) anti-pattern is either dead-slow in a multi-user environment or will simply not work correctly in a multi-user environment.
If you need to support MySQL as well, I'd recommend to use the "native" "auto increment" type in each database (serial for PostgreSQL, auto_increment for MySQL, identity for SQL Server, sequence + trigger in Oracle and so on) and let the driver return the generated ID value
In JDBC there is a getGeneratedKeys() method and I'm sure other interfaces have something similar.
从你的标签很难判断你正在使用什么数据库。
对于 SQL Server（因为它已列出）我建议
From your tags it's hard to tell what database you are using.
For SQL Server (since it's listed) I suggest