SQL 替换 WHERE 两个外来字段

Question

当我的非主要字段(user1和user2(的组合存在于表中时，我需要替换"文本"。或者插入新的行用户 1、用户 2、文本 - 当不插入时。

CREATE TABLE IF NOT EXISTS opinions 
(id INT AUTO_INCREMENT PRIMARY KEY, 
user1 INT, user2 INT, text TEXT);
+----+-----------+-----------+----------+
| id | user1     | user2     | text     |
+----+-----------+-----------+----------+
|  1 | 141015727 | 627964361 | nice guy |
|  2 | 141015727 | 375392538 | hello    |
+----+-----------+-----------+----------+ 

预期产出：

+----+-----------+-----------+----------+
| id | user1     | user2     | text     |
+----+-----------+-----------+----------+
|  1 | 141015727 | 627964361 | bad guy  |
|  2 | 141015727 | 375392538 | hello    |
+----+-----------+-----------+----------+

UPD

Mysql>从意见中选择*;

+----+-----------+-----------+--------+
| id | user1     | user2     | text   |
+----+-----------+-----------+--------+
|  1 | 141015727 | 627964361 | hello1 |
+----+-----------+-----------+--------+

预期产出

+----+-----------+-----------+--------+
| id | user1     | user2     | text   |
+----+-----------+-----------+--------+
|  1 | 141015727 | 627964361 | hello2 |
+----+-----------+-----------+--------+

试过(对不起，好像我误删了这个答案(

IF EXISTS(SELECT user1, user2 FROM opinions WHERE user1='141015727' 
AND user2 = '627964361') THEN UPDATE opinions SET text = 'hello2' 
WHERE user1 = '141015727' AND user2 = '627964361' 
ELSE INSERT INTO opinions (user1, user2, text) 
VALUES ('141015727', '627964361', 'hello2') END IF;' 

出现错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT user1, user2 FROM opinions WHERE user1='141015727' AND user2 = ' 

Answer 1

选项 1(

可以创建一个存储过程来检查是否存在user_1值和user_2值，如果重复，则进行更新，否则进行插入。我不打算在这里进行详细说明，如果您有兴趣，可以阅读有关存储过程的信息。对于您的基本示例，我将像下一个选项一样。

选项 2(

首先，我们在您拥有的表上创建一个唯一的索引：

CREATE TABLE IF NOT EXISTS opinions (
id INT AUTO_INCREMENT PRIMARY KEY,
user1 INT,
user2 INT,
text TEXT,
CONSTRAINT uniqueUsersTuple UNIQUE (user1, user2)
);

然后，您可以执行以下操作：

INSERT INTO opinions (user1, user2, text)
VALUES (userID1, userID2, "newOpinion")
ON DUPLICATE KEY UPDATE text = "newOpinion";

Answer 2

您的 where 条件不在主键上。 不幸的是，您无法在 1 个查询中执行。

请参阅这篇文章： MySQL UPSERT 没有重复键

Answer 3

或者这个坏男孩方法，如果你不想创建一个额外的唯一键。
你知道每个索引都会减慢 UPDATE 和 DELETE 查询的速度，所以如果你对表的写入做了很多事情，这可能会在某种程度上优于 D.Smania 的答案。

当记录存在时

查询

REPLACE INTO 
opinions (
id
, user1
, user2
, opinion
)
SELECT 
id
, user1
, user2
, opinion
FROM (
SELECT
id
, user1
, user2
, 'bad guy' AS opinion
, (@found := 1) AS found
FROM
opinions
WHERE
user1 = 141015727
AND
user2 = 627964361
UNION ALL 
SELECT
DISTINCT
null AS id
, 141015727 AS user1
, 627964361 AS user2
, 'bad guy' AS opinion
, (@found := 0) AS found  
FROM
opinions
WHERE 
@found <> 1  
) AS alias
CROSS JOIN (SELECT @found := 0) AS init_var_params 

结果

| id  | user1     | user2     | opinion |
| --- | --------- | --------- | ------- |
| 1   | 141015727 | 627964361 | bad guy |
| 2   | 141015727 | 375392538 | hello   |

在DB Fiddle上查看

当记录不存在时

查询

REPLACE INTO 
opinions (
id
, user1
, user2
, opinion
)
SELECT 
id
, user1
, user2
, opinion
FROM (
SELECT
id
, user1
, user2
, 'bad guy' AS opinion
, (@found := 1) AS found
FROM
opinions
WHERE
user1 = 100
AND
user2 = 100
UNION ALL 
SELECT
DISTINCT
null AS id
, 100 AS user1
, 100 AS user2
, 'bad guy' AS opinion
, (@found := 0) AS found  
FROM
opinions
WHERE 
@found <> 1  
) AS alias
CROSS JOIN (SELECT @found := 0) AS init_var_params 

结果

| id  | user1     | user2     | opinion  |
| --- | --------- | --------- | -------- |
| 1   | 141015727 | 627964361 | nice guy |
| 2   | 141015727 | 375392538 | hello    |
| 3   | 100       | 100       | bad guy  |

在DB Fiddle上查看

编辑

我利用了MySQL用户变量并进行了一些调整，也可以在没有MySQL用户变量的情况下做到这一点。因此，具有 REPLACE 或其他 UPSET 方法的其他数据库系统也可以使用此方法。

现有的
没有 MySQL 用户变量 View on DB Fiddle

不存在的
没有 MySQL 用户变量 在数据库小提琴上查看

Answer 4

如果我不是新手，我只会提出 2 个请求，因为我不需要唯一的 ID 来保持不变。现在想通了。

CREATE TABLE IF NOT EXISTS opinions 
(id INT AUTO_INCREMENT PRIMARY KEY, 
user1 INT, user2 INT, text TEXT);
DELETE FROM opinions WHERE user1='1' AND user2='2';
INSERT INTO opinions (user1, user2, text) VALUES ('1','2','bad guy');  

太容易了