mysql - sql REPLACE WHERE 两个外部字段

Question

当表中存在非主字段(user1 和 user2)的组合时，我需要替换“文本”。或者插入新行 user1、user2、text - 当没有时。

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    |
+----+-----------+-----------+----------+

更新

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";