假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
假设MyTable的表结构(KEY, datafield1, datafield2…)
通常,我希望更新现有记录,或者插入不存在的新记录。
从本质上讲:
IF (key exists)
run update command
ELSE
run insert command
最好的写法是什么?
当前回答
你可以使用:
INSERT INTO tableName (...) VALUES (...)
ON DUPLICATE KEY
UPDATE ...
使用这个,如果已经有一个特定键的条目,那么它将更新,否则,它将插入。
其他回答
做一个if存在…其他的……至少要做两个请求(一个检查,一个采取行动)。下面的方法在记录存在时只需要一个,如果需要插入则需要两个:
DECLARE @RowExists bit
SET @RowExists = 0
UPDATE MyTable SET DataField1 = 'xxx', @RowExists = 1 WHERE Key = 123
IF @RowExists = 0
INSERT INTO MyTable (Key, DataField1) VALUES (123, 'xxx')
假设您想要插入/更新单行,最优的方法是使用SQL Server的REPEATABLE READ事务隔离级别:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION
IF (EXISTS (SELECT * FROM myTable WHERE key=@key)
UPDATE myTable SET ...
WHERE key=@key
ELSE
INSERT INTO myTable (key, ...)
VALUES (@key, ...)
COMMIT TRANSACTION
当当前运行的事务处于打开状态时,此隔离级别将阻止/阻止后续可重复读事务访问同一行(WHERE key=@key)。 另一方面,对另一行的操作不会被阻塞(WHERE key=@key2)。
如果您首先尝试更新,然后插入,那么竞态条件真的重要吗? 假设你有两个线程想要为key key设置一个值:
线程1:value = 1 线程2:value = 2
竞态条件场景示例
键没有定义 线程1更新失败 线程2更新失败 线程1或线程2中的一个成功插入。例如线程1 另一个线程插入失败(错误重复键)-线程2。 结果:要插入的两个步骤中的“第一个”决定值。 期望结果:最后一个线程写入数据(更新或插入)应该决定值
但;在多线程环境中,操作系统调度器决定线程执行的顺序——在上面的场景中,我们有这个竞争条件,是操作系统决定执行的顺序。从系统的观点来看,说“线程1”或“线程2”是“第一个”是错误的。
当线程1和线程2的执行时间非常接近时,竞态条件的结果并不重要。唯一的要求应该是其中一个线程应该定义结果值。
对于实现:如果更新后插入导致错误的“重复键”,这应该被视为成功。
此外,我们当然不应该假定数据库中的值与您最后写入的值相同。
请看我对之前一个非常相似的问题的详细回答
@Beau Crawford's在SQL 2005及以下是一个很好的方法,尽管如果你授予rep它应该去第一个人SO它。唯一的问题是对于插入,它仍然是两个IO操作。
MS Sql2008引入了SQL:2003标准的合并:
merge tablename with(HOLDLOCK) as target
using (values ('new value', 'different value'))
as source (field1, field2)
on target.idfield = 7
when matched then
update
set field1 = source.field1,
field2 = source.field2,
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, source.field1, source.field2, ... )
现在它真的只是一个IO操作,但糟糕的代码:-(
Before everyone jumps to HOLDLOCK-s out of fear from these nafarious users running your sprocs directly :-) let me point out that you have to guarantee uniqueness of new PK-s by design (identity keys, sequence generators in Oracle, unique indexes for external ID-s, queries covered by indexes). That's the alpha and omega of the issue. If you don't have that, no HOLDLOCK-s of the universe are going to save you and if you do have that then you don't need anything beyond UPDLOCK on the first select (or to use update first).
scproc通常在非常受控的条件下运行,并假设有可信的调用者(中间层)。这意味着如果一个简单的upsert模式(更新+插入或合并)看到重复的PK,这意味着在中间层或表设计中存在错误,SQL将在这种情况下调用错误并拒绝记录。在这种情况下,放置HOLDLOCK除了降低性能外,还等于吃掉异常并接收潜在的错误数据。
话虽如此,使用MERGE或UPDATE然后INSERT在您的服务器上更容易,而且更容易出错,因为您不必记得在第一次选择时添加(UPDLOCK)。此外,如果您正在小批量地进行插入/更新,则需要了解数据,以便决定某个事务是否合适。如果它只是一个不相关记录的集合,那么额外的“包络”交易将是有害的。