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)  ENDMySQL查询：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-exists

MySQL和MSSQL 中的WHERE EXISTS 语法似乎相同。

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 中对其进行测试。如果可行，我将更新这篇文章并添加代码。

Answer 2

如果您使用的是 PHP，那么您正在通过接口调用代码。您可以执行以下操作：

1. 在 上创建唯一索引ip_address。

2. 尝试插入新行。如果该行已经存在，这将失败。

3. 如果插入失败（特别是重复键错误），则更新现有行。

但是，您尝试在两个数据库中使用相同代码的目标是 . . . 只是不会很好地工作。这两个数据库是相当不同的。也许您应该考虑在每个数据库中构建存储过程来执行您想要的操作，然后调用这些存储过程。