在新行中添加 max(value)+1，这会不会有问题? [英] Adding max(value)+1 in new row, can this be a problem?

问题描述

在 SQL Server 表中，我有以下 2 列:

In a SQL Server table I have the following 2 columns:

RowId:主键、数字、标识列和自动插入.
MailId:非键、数字、非标识和非自动插入.

RowId: primary key, numaric, identity column and auto insert.
MailId: Non key, numaric, non identity and non auto insert.

邮件 ID 可以重复.如果是新的 MailId，我将检查 max(MailId)+1 并将其插入新行，如果出现重复值，将作为参数出现.

Mail Id can be duplicate. In case of new MailId I will check max(MailId)+1 and insert it in new row and in case of duplication value will be coming as parameter.

逻辑看起来不错，但这里有一个问题，我只是在考虑(但准确率很低)同时可能有两个不同的新 MailId 请求.这会导致逻辑错误吗?例如，当代码检查 max(MailId)+1 是 101 并且我将它存储在一个变量中但可能在下一个插入语句执行插入表中的新记录之前.现在表中的 max(MailId)+1 将是 102 但变量中的值将是 101 ?

Logic looks fine but here is an issue, I was just considering (yet chacnes of accurance are ver low) In the same time there can be two different new MailId requests. Can this casue logical error ? For example when code checked max(MailId)+1 was 101 and I stored it in a variable but may be before next insert statment executs a new record inserted in table. Now max(MailId)+1 in table will be 102 but value in variable will be 101 ?

任何建议，我也想控制这个错误机会.

Any suggestion please I want to control this error chances as well.

编辑

(我没有使用 identity(1,1) 因为我还必须在其中传递自定义值)

(I am not using identity(1,1) because I also have to pass custom values in it)

推荐答案

既然 SQL Server 中已经有这么棒的Identity 字段，为什么还要使用自定义滚动字段?

Why would you use a custom-rolled Identity field when there is such a great one already in SQL Server?

只需将 INT Identity (1,1) 用于您的 ID 字段，它会在每次插入行时自动递增.它还比几乎任何您可以手动实现的东西都更好地处理并发性.

Just use INT Identity (1,1) for your ID field and it will automatically increment each time a row is inserted. It also handles concurrency much better than pretty much anything you could implement manually.

手动 ID 值示例:

SET IDENTITY_INSERT MyTable ON

INSERT INTO MyTable (IdField, Col1, Col2, Col3,...)
VALUES
(1234, 'Col1', 'Col2', 'Col3',...)

SET IDENTITY_INSERT MyTable OFF

您需要为 INSERT 包含一个明确的字段列表.

You need to include an explicit field list for the INSERT.

这篇关于在新行中添加 max(value)+1，这会不会有问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！