MySQL在哪里使用DUPLICATE UPDATE? [英] MySQL ON DUPLICATE UPDATE with WHERE?

问题描述

我在下面的查询中遇到了一些问题.如果数据库中已经有用户行，我希望它运行UPDATE查询.我希望它能插入带有值的行，或更新带有值的行，就像在查询中所说的那样.

I am having some issues with the query below. I want it to run the UPDATE query if theres already a row for the user in the database. I expect for it to insert a row with the values, or update a row with the values just as it says in the query.

我遇到以下错误:

语法错误或访问冲突:1064您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册，以在'SET balance = '40'，xplevel = '36'WHERE username ='nibblenews'AND server ='Factions'在第1行附近使用正确的语法

syntax error or access violation: 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 'SET balance='40', xplevel='36' WHERE username='nibblenews' AND server='Factions'' at line 1'

$stmt = $db->prepare('INSERT INTO stats (balance, xplevel, username, server) VALUES (:balance, :xp, :username, :server) ON DUPLICATE KEY UPDATE stats SET balance=:balance2, xplevel=:xp2 WHERE username=:username2 AND server=:server2');
$stmt->bindParam(':balance', $balance, PDO::PARAM_STR);
$stmt->bindParam(':balance2', $balance, PDO::PARAM_STR);
$stmt->bindParam(':xp', $xp, PDO::PARAM_STR);
$stmt->bindParam(':xp2', $xp, PDO::PARAM_STR);
$stmt->bindParam(':username', $username, PDO::PARAM_STR);
$stmt->bindParam(':username2', $username, PDO::PARAM_STR);
$stmt->bindParam(':server', $server, PDO::PARAM_STR);
$stmt->bindParam(':server2', $server, PDO::PARAM_STR);
$stmt->execute();

推荐答案

您不能在INSERT ... ON DUPLICATE KEY UPDATE查询中使用显式的WHERE语句.正如 MySQL文档所说:

You cannot use an explicit WHERE statement in INSERT ... ON DUPLICATE KEY UPDATE queries. As the MySQL docs say:

如果指定ON DUPLICATE KEY UPDATE，并且插入一行会导致UNIQUE索引或PRIMARY KEY中的值重复，则MySQL对旧行执行UPDATE.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

换句话说:您指定为UNIQUE或PRIMARY KEY的列用于检查该行是否已存在.如果是这样，则更新该行.

In other words: the columns you specified as UNIQUE or PRIMARY KEY are used to check whether the row already exists. If so, that row is updated.

stats SET(在ON DUPLICATE KEY UPDATE之后)无效.您应该将查询更改为以下内容:

Furthermore stats SET in your query (after ON DUPLICATE KEY UPDATE) is invalid. You should change your query to something like this:

INSERT INTO stats (balance, xplevel, username, server)
VALUES (:balance, :xp, :username, :server)
ON DUPLICATE KEY UPDATE balance=:balance2, xplevel=:xp2;

这篇关于MySQL在哪里使用DUPLICATE UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！