MySQL 和 MSSQL 中用于 IF INSERT ELSE UPDATE 的通用语法

Question

我有一个 PHP 7.3 项目，它通过 PDO 连接到MySQL数据库或MSSQL数据库，具体取决于在 Linux 或 Windows 上运行。

如果唯一值尚未在该表中，我想将新值插入表中。如果它已经在表中，我想更新非唯一值。

我也搜索了很多文档和 SO 帖子，但我找不到一种语法，它在一个查询中对两种数据库类型都执行此操作。

SQL Server 查询：

IF (EXISTS (SELECT * FROM failed_logins_ip_address WHERE ip_address = 'xxx'))

  BEGIN

    UPDATE failed_logins_ip_address

    SET attempts_count = attempts_count + 1, attempt_datetime = CURRENT_TIMESTAMP

    WHERE ip_address = 'xxx'

  END

  ELSE

  BEGIN

    INSERT INTO failed_logins_ip_address (ip_address, attempts_count, attempt_datetime)

    VALUES ('xxx', 1, CURRENT_TIMESTAMP)

  END

MySQL查询：

INSERT INTO failed_logins_ip_address (ip_address, attempts_count, attempt_datetime)

  VALUES ('xxx', 1, CURRENT_TIMESTAMP)

  ON DUPLICATE KEY

  UPDATE attempts_count = attempts_count + 1, attempt_datetime = CURRENT_TIMESTAMP

'ip_addess' 列是唯一的，MSSQL 和 MySQL 的表结构相同。

是否有可以在两种数据库类型中执行 IF INSERT ELSE UPDATE 的语法？

是的，我做（PDO）参数绑定，xxx只是为了缩短代码片段。

是的，如果我在两个查询（首先选择，然后插入或更新）中执行相同的语法，我可以使用相同的语法，但我想避免（希望）不必要的查询。

不，我不想插入每次登录尝试，因此我不再需要更新，因为我不需要这些数据。

如果 REPLACE 方法可行：这不会更新，它会删除和插入，这也是我不想要的。

我当前的解决方案：我在 PHP 中检查当前数据库类型并切换/大小写查询字符串。它很干净，但一根绳子更不臭;-)

更新：

我改变了 MSSQL 查询：从 IF NOT EXISTS 到 IF EXISTS 以提高效率。UPDATE 将比 INSERT 更频繁地发生，因此在大多数情况下，只会执行第一个（子）查询。

Answer 1

在深入挖掘之后，我发现了 Derek Dieter 的这篇文章，它描述了如何将 SQL Server 的 IF EXISTS ELSE 替换为 WHERE EXISTS：https://sqlserverplanet.com/optimization/avoiding-if-else-by-using-where-existsMySQL和MSSQL 中的WHERE EXISTS 语法似乎相同。Derek Dieter 的例子，如果存在：IF NOT EXISTS (SELECT 1 FROM customer_totals WHERE cust_id = @cust_id)BEGININSERT INTO customer_totals(cust_id,order_amt)SELECTcust_id = @cust_id,order_amt = @order_amtENDELSEUPDATE customerSET order_amt = order_amt + @order_amtWHERE cust_id = @cust_idENDDerek Dieter 的例子，在 WHERE EXISTS 中：INSERT INTO customer_totals(cust_id,order_amt)SELECT TOP 1 — important since we’re not constraining any recordscust_id = @cust_id,order_amt = @order_amtFROM customer_totals ctWHERE NOT EXISTS — this replaces the if statement(SELECT 1FROM customer_totalsWHERE cust_id = @cust_id)SET @rowcount = @@ROWCOUNT — return back the rows that got insertedUPDATE customerSET order_amt = order_amt + @order_amtWHERE @rowcount = 0AND cust_id = @cust_id — if no rows were inserted, the cust_id must exist, so update不过，我仍然需要在 MySQL 中对其进行测试。如果可行，我将更新这篇文章并添加代码。

Answer 2

如果您使用的是 PHP，那么您正在通过接口调用代码。您可以执行以下操作：在 上创建唯一索引ip_address。尝试插入新行。如果该行已经存在，这将失败。如果插入失败（特别是重复键错误），则更新现有行。但是，您尝试在两个数据库中使用相同代码的目标是 . . . 只是不会很好地工作。这两个数据库是相当不同的。也许您应该考虑在每个数据库中构建存储过程来执行您想要的操作，然后调用这些存储过程。