微软SQL Server - 更新或插入的最佳方法

Question

微软SQL Server - 更新或插入的最佳方法

sqlsql-server

21

我一直在寻找这个问题的答案，但有些信息存在冲突或模棱两可，很难找到确定的答案。

我的背景：我正在使用“mssql”npm包中的node.js。我的SQL服务器是Microsoft SQL Server 2014。

我有一条记录可能已经存在于表中——如果存在，我想要更新它，否则我想要插入它。我不确定最佳的SQL是什么，或者是否应该在mssql中运行某种“事务”。我找到了一些看起来不错的选项，但我对它们都不确定：

选项1： 如何在存在时更新或插入

这个选项的问题是我甚至不确定这是否是MSSQL中的有效语法。虽然我喜欢它，而且它似乎也支持同时处理多行，这一点我很喜欢。

INSERT INTO table (id, user, date, points)
    VALUES (1, 1, '2017-03-03', 25),
           (2, 1, '2017-03-04', 25),
           (3, 2, '2017-03-03', 100),
           (4, 2, '2017-03-04', 150)
    ON DUPLICATE KEY UPDATE points = VALUES(points)

选项2：

不知道这个选项是否存在问题，只是不确定它是否最优。似乎不支持多个同时的行。

update test set name='john' where id=3012
IF @@ROWCOUNT=0
   insert into test(name) values('john');

选项 3：合并，https://dba.stackexchange.com/questions/89696/how-to-insert-or-update-using-single-query

有些人说这个方法有点问题？不过它显然支持同时多个操作，我很喜欢。

MERGE dbo.Test WITH (SERIALIZABLE) AS T
USING (VALUES (3012, 'john')) AS U (id, name)
    ON U.id = T.id
WHEN MATCHED THEN 
    UPDATE SET T.name = U.name
WHEN NOT MATCHED THEN
    INSERT (id, name) 
    VALUES (U.id, U.name);

- TKoL

“MERGE”在早期版本的SQL Server中有一些“功能”。仍然存在一些“功能”，但其中一些已经修复。使用像这样的简单语句，您应该没问题。不过，我很惊讶您为什么没有选择一个简单的“UPSERT”过程：使用数据运行一个“UPDATE”，然后使用“NOT EXISTS”运行一个“INSERT”。 - Thom A

2

请阅读以下内容：https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/ 和 https://sqlperformance.com/2020/09/locking/upsert-anti-pattern，以及两篇文章的评论。 - Milney

选项2会抛出错误，如果无效。如何处理？ - Mayank Kumar Chaudhari

3个回答

7

它们每一个都有不同的目的、优点和缺点。

选项1适用于多行插入/更新。然而，它只检查主键约束。

选项2适用于小数据集合。单个记录的插入和更新。它更像脚本。

选项3最适合大查询。比如从一张表中读取并相应地插入/更新到另一张表中。您可以定义需要满足哪些条件才能进行插入和/或更新操作。您不仅限于主键/唯一约束。

- Derviş Kayımbaşıoğlu

1

谢谢你的回答。我最喜欢选项1，但是我已经确认这种语法在我的SQL服务器上不起作用，所以我倾向于选择选项3。 - TKoL

我可以接受限制于主键约束，毕竟这正是我要使用的。但我希望MSSQL有一个选项1的版本。 - TKoL

1

在此扩展我的评论。SQL Server中有已知的MERGE问题，但是，对于您在此处所做的操作，您很可能会没问题。Aaron Bertrand在这个主题上有一篇文章，您可以在这里找到：使用SQL Server的MERGE语句时要小心。

然而，你在这里可以使用一个"UPSERT"的替代方法；UPDATE现有行，然后INSERT不存在的行。这涉及两个单独的语句，但是在MERGE之前这是使用的方法:

UPDATE T
SET T.Name = U.Name
FROM dbo.Test T
     JOIN (VALUES (3012, 'john')) AS U (id, name) ON T.id = U.id;

INSERT INTO dbo.Test (Name) --I'm assuming ID is an `IDENTITY` here
SELECT U.name
FROM (VALUES (3012, 'john')) AS U (id, name)
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.Test T
                  WHERE T.ID = U.ID);

注意，这个例子中我没有声明任何锁定或事务，但你在任何实现的解决方案中都应该这样做。

- Thom A

谢谢。我看了你提供的注意事项链接，我不认为我的使用情况会有这些问题。当我进行更新时，我要插入/更新的这些表格将不会被“同时使用”——除了我的更新之外，它们将保持静止。如果是这种情况，我觉得我应该选择合并。 - TKoL

幸运的是，@TKoL，你没有使用SQL Server 2008，在那里MERGE应该被避免。 - Thom A

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Milney · Accepted Answer

如果您的系统高度并发，性能很重要，您可以尝试以下模式，如果更新比插入更常见：

BEGIN TRANSACTION;
 
UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;
 
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END
 
COMMIT TRANSACTION;

参考资料：https://sqlperformance.com/2020/09/locking/upsert-anti-pattern

另外还可以阅读：https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

如果插入操作更为常见：

BEGIN TRY     
  INSERT INTO dbo.AccountDetails (Email, Etc) VALUES (@Email, @Etc);       
END TRY     
BEGIN CATCH     
  -- ignore duplicate key errors, throw the rest.
  IF ERROR_NUMBER() IN (2601, 2627) 
    UPDATE dbo.AccountDetails
       SET Etc = @Etc
     WHERE Email = @Email;     
END CATCH

我不会使用合并操作，虽然大部分的漏洞已经被修复 - 我们以前在生产环境中遇到过重大问题。

编辑 ---

是的，上面的答案适用于单行记录。对于多行记录，您可以这样做：锁定的思路是相同的。

BEGIN TRANSACTION;
 
  UPDATE t WITH (UPDLOCK, SERIALIZABLE) 
    SET val = tvp.val
  FROM dbo.t AS t
  INNER JOIN @tvp AS tvp
    ON t.[key] = tvp.[key];
 
  INSERT dbo.t([key], val)
    SELECT [key], val FROM @tvp AS tvp
    WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]);
 
COMMIT TRANSACTION;