如果存在，则选择 ELSE INSERT 然后选择 [英] IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

问题描述

以下内容在 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.

这篇关于如果存在，则选择 ELSE INSERT 然后选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！