SQL幂等性

Question

我在SQL数据库项目中有一个部署后脚本，用于注册应该作为数据库一部分的默认值。我希望这个脚本能够多次运行而不改变结果，这样如果我需要添加一点修改，就可以手动运行它。

INSERT INTO [UserRole] ([Name])
SELECT 'Regular'
UNION ALL
SELECT 'Admin'

INSERT INTO [UserRight] ([Name])
SELECT 'CanAccessApplicationLogs'
UNION ALL
SELECT 'CanAccessApplicationJobs'

INSERT INTO [UserRoleRight] ([UserRoleId], [UserRightId])
SELECT  [Ro].[Id], [Ri].[Id] FROM [UserRight] Ri, [UserRole] Ro
 WHERE ([Ro].[Name] = 'Admin' AND
        [Ri].[Name] = 'CanAccessApplicationLogs')
    OR ([Ro].[Name] = 'Admin' AND
        [Ri].[Name] = 'CanAccessApplicationJobs')

我认为性能不会成为问题，因为这个脚本只会插入角色和权限。那么，最清晰的方式是什么来检查每一行是否存在，并且只插入那些尚未在表中的行呢？

- bevacqua

Answer 1

在每个插入语句外加上IF语句应该是可行的，不过可能不是最干净的方法。

因此，您的第一个INSERT语句将变成：

IF NOT EXISTS(SELECT * FROM UserRole WHERE Name = 'Regular')
BEGIN
  INSERT INTO [UserRole] ([Name])
  SELECT 'Regular'
  UNION ALL
  SELECT 'Admin'
END

- Jon Collins

Answer 2

MERGE操作可以更加简洁，它会逐行检查而无需在每一行周围添加if语句。

http://coding.abel.nu/2012/08/idempotent-db-update-scripts/有一个很好的例子。

- Chris Woodward

---

这很酷！它似乎甚至可以容纳您已删除的条目。我的MySQL有点生疏，所以我需要想办法将其概括。 - sdanzig

等等，不对，这不是MySQL。我认为ON DUPLICATE KEY是正确的方法。我想没有容纳删除的条目。 - sdanzig