没有主键的表的MySQL更新或插入

Question

我知道执行update或insert的简单方法是使用“替换”，但是它需要一个主键，在没有主键的表中呢？

我的桌子上有5栏：

1. Remark_id(自动增量主键)
2. user_id
3. remark_user_id
4. 备注
5. last_modified

在更新备注之前，我希望先检查这对user_id和remark_user_id是否存在，否则将创建一个新行来使用user_id和remark_user_id保存注释。

这是我的密码

INSERT INTO `users_remark` (`user_id`, `remark_user_id`, `remark`) 
SELECT 1,3 ,'testing123'
FROM dual
WHERE NOT EXISTS
    (SELECT *
     FROM `users_remark`
     WHERE `user_id` = 1
       AND `remark_user_id` = 3)

运行SQL之后，我的数据库中什么也不会发生。没有添加或更新任何记录。

编辑的

使用IF...ELSE...进行代码更改，但第一行出现了一些语法错误

#1064 - 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 * FROM users_remark WHERE user_id' at line 1

IF EXISTS (
    SELECT * FROM `users_remark` 
    WHERE `user_id`=1 AND `remark_user_id` = 3
    )
    THEN    UPDATE `users_remark` SET `remark` = 'testing123' 
            WHERE `user_id`=1 AND `remark_user_id` = 3
ELSE
    INSERT INTO `users_remark` SET `remark` = 'testing123', `user_id`=1, `remark_user_id` = 3

Answer 1

下面是带有INSERT和UPDATE子句(T语法)的查询

IF [condition here] BEGIN
     UPDATE `users_remark`
     SET `remark` = 'testing123' 
     WHERE `user_id`=1
         AND `remark_user_id` = 3
END
ELSE BEGIN
    INSERT INTO `users_remark` (`user_id`, `remark_user_id`, `remark`)
    VALUES (1, 3, 'testing123)
END

编辑:与MySQL语法相同的查询

DECLARE @numrecords INT

SELECT @numrecords = count(*)
FROM `users_remark`
WHERE `user_id` = 1
   AND `remark_user_id` = 3

IF @numrecords > 0 THEN
     UPDATE `users_remark`
     SET `remark` = 'testing123' 
     WHERE `user_id`=1
         AND `remark_user_id` = 3
ELSE
    INSERT INTO `users_remark` (`user_id`, `remark_user_id`, `remark`)
    VALUES (1, 3, 'testing123)
END IF

希望这能帮到你。

Answer 2

最后，我尝试将代码放到php中，它可以工作，而且更容易理解。

        $checkRemark = mysqli_query($GLOBALS['db_conn'], "SELECT * FROM `users_remark` WHERE `user_id`=".$data['uid']." AND `remark_user_id` = ".$data['ruid']);
        if (mysqli_num_rows($checkRemark)>0){
            $remarkSql = "UPDATE `users_remark`
                         SET `remark` = '".$data['remark']."' 
                         WHERE `user_id`=".$data['uid']."
                             AND `remark_user_id` = ".$data['ruid'];
        } else {
            $remarkSql = "INSERT INTO `users_remark` (`user_id`, `remark_user_id`, `remark`)
                            VALUES (".$data['uid'].", ".$data['ruid'].", '".$data['remark']."')";
        }