如果存在，则选择 ELSE INSERT 然后选择

本文介绍了如果存在，则选择 ELSE INSERT 然后选择的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下内容在 Microsoft SQL Server 2005 中怎么说:

How do you say the following in Microsoft SQL Server 2005:

IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN SELECT TableID FROM Table WHERE FieldValue='' ELSE INSERT INTO TABLE(FieldValue) VALUES('') SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY() END IF

我想要做的是查看是否已经存在空白字段值，如果存在则返回该 TableID，否则插入一个空白字段值并返回相应的主键.

What I'm trying to do is to see if there is a blank fieldvalue already, and if there is then return that TableID, else insert a blank fieldvalue and return the corresponding primary key.

推荐答案

您需要在事务中执行此操作，以确保两个同时的客户端不会两次插入相同的 fieldValue:

You need to do this in transaction to ensure two simultaneous clients won't insert same fieldValue twice:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION DECLARE @id AS INT SELECT @id = tableId FROM table WHERE fieldValue=@newValue IF @id IS NULL BEGIN INSERT INTO table (fieldValue) VALUES (@newValue) SELECT @id = SCOPE_IDENTITY() END SELECT @id COMMIT TRANSACTION

您还可以使用双重检查锁定来减少锁定开销

you can also use Double-checked locking to reduce locking overhead

DECLARE @id AS INT SELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValue IF @id IS NULL BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @id = tableID FROM table WHERE fieldValue=@newValue IF @id IS NULL BEGIN INSERT INTO table (fieldValue) VALUES (@newValue) SELECT @id = SCOPE_IDENTITY() END COMMIT TRANSACTION END SELECT @id

至于为什么 ISOLATION LEVEL SERIALIZABLE 是必要的，当你在一个可序列化的事务中时，命中表的第一个 SELECT 会创建一个范围锁，覆盖记录应该在的地方，所以没有其他人可以插入相同的记录，直到这个交易结束.

As for why ISOLATION LEVEL SERIALIZABLE is necessary, when you are inside a serializable transaction, the first SELECT that hits the table creates a range lock covering the place where the record should be, so nobody else can insert the same record until this transaction ends.

如果没有 ISOLATION LEVEL SERIALIZABLE，默认的隔离级别 (READ COMMITTED) 不会在读取时锁定表，因此在 SELECT 和 UPDATE 之间，仍然可以插入.具有 READ COMMITTED 隔离级别的事务不会导致 SELECT 锁定.具有 REPEATABLE READS 的事务会锁定记录(如果找到)，但不会锁定间隙.

Without ISOLATION LEVEL SERIALIZABLE, the default isolation level (READ COMMITTED) would not lock the table at read time, so between SELECT and UPDATE, somebody would still be able to insert. Transactions with READ COMMITTED isolation level do not cause SELECT to lock. Transactions with REPEATABLE READS lock the record (if found) but not the gap.