php - 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)参数绑定(bind)，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-exists

WHERE EXISTS 语法在MySQL 中似乎是相同的和 MSSQL .

Derek Dieter 的示例，如果存在:

IF NOT EXISTS (SELECT 1 FROM customer_totals WHERE cust_id = @cust_id)
BEGIN
INSERT INTO customer_totals
(
cust_id,
order_amt
)
SELECT
cust_id = @cust_id
,order_amt = @order_amt
END
ELSE

UPDATE customer
SET order_amt = order_amt + @order_amt
WHERE cust_id = @cust_id

END

Derek Dieter 的例子，WHERE EXISTS:

INSERT INTO customer_totals
(
cust_id,
order_amt
)
SELECT TOP 1 — important since we’re not constraining any records
cust_id = @cust_id
,order_amt = @order_amt
FROM customer_totals ct
WHERE NOT EXISTS — this replaces the if statement
(
SELECT 1
FROM customer_totals
WHERE cust_id = @cust_id
)

SET @rowcount = @@ROWCOUNT — return back the rows that got inserted

UPDATE customer
SET order_amt = order_amt + @order_amt
WHERE @rowcount = 0
AND cust_id = @cust_id — if no rows were inserted, the cust_id must exist, so update

不过，我仍然需要在 MySQL 中对其进行测试。如果有效，我将更新这篇文章并添加代码。