我知道这个问题,但是它没有帮助。
我有一个包含id和值的列表。现在我需要检查id是否存在于用户数据库中。如果是,那么该行将被更新,否则我必须在另一个表 (tmp_user)中插入id 。
编辑:这是我的尝试
IF NOT EXISTS (SELECT * FROM `wcf1_user` WHERE `steamID` = 1) THEN
INSERT INTO `wcf1_points_tmp` (`steamID`, `points`) VALUES (1, 2)
ELSE
// Update stuff......
END IF;
结果:#1064 -你有一个错误的SQL语法;查看对应MySQL服务器版本的手册,在'IF NOT EXISTS (SELECT * FROM wcf1_user
WHERE steamID
= 1) THEN INSERT IN' at line 1
谢谢你的帮助。:)
如果指定ON DUPLICATE KEY UPDATE
,并且插入一行会导致UNIQUE
索引或PRIMARY
KEY
中的值重复,则执行旧行的UPDATE
。例如,如果列a被声明为UNIQUE
并且包含值1,那么以下两条语句具有相同的效果:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
这是域逻辑,不属于数据层。用PHP代替:
<?php
// connect to the database
$DSN = "mysql:dbname=$dbname;charset=utf8";
$opt = array(PDO::MYSQL_ATTR_FOUND_ROWS => true);
$dbh = new PDO($DSN, $username, $password, $opt);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// one assumes you want to perform this operation atomically
$dbh->beginTransaction();
// attempt to update the row
$update = $dbh->prepare('
UPDATE wcf1_user
SET ......
WHERE steamID = ?
');
$update->execute(array($steamID));
// if the update didn't affect any rows
if (!$update->rowCount()) {
// insert into another table instead
$insert = $dbh->prepare('
INSERT INTO wcf1_points_tmp
(steamID, points)
VALUES
(?, ?)
');
$insert->execute(array($steamID, $points));
}
// tada!
$dbh->commit();
?>
我认为您在插入语句末尾缺少分号,请尝试以下操作,
IF NOT EXISTS (SELECT * FROM `wcf1_user` WHERE `steamID` = 1) THEN
INSERT INTO `wcf1_points_tmp` (`steamID`, `points`) VALUES (1, 2);
ELSE
// Update stuff...... remember to add semicolon at the end
END IF;