创建MySQL过程错误
创建MySQL过程错误
提问于 2014-03-24 14:38:20
在下面创建一个过程，它不断地给出从第一个select语句中分配值的方法，我是否做错了，任何帮助都非常感谢。
错误1064 (42000)：您的SQL语法有错误；请检查与您的MySQL服务器版本对应的手册，以获得使用“:=
m id，vCurrentVote：= corresponds FROM td_voted”的正确语法，其中id = pCid限制1；
CREATE PROCEDURE pr_update_vote (IN pCid INT, IN pVote INT, IN pMid INT, OUT pNewVote INT)
BEGIN
DECLARE vMid, vVote, vCurrentVote, vNewVote INT;
IF pVote = 1 THEN
SET vVote = 1;
ELSE
SET vVote = -1;
END IF;
SELECT vMid := `mid`, vCurrentVote := vote FROM td_voted WHERE id = pCid LIMIT 1;
IF vMid <> pMid THEN
SELECT vVoteType := vote
FROM td_votes
WHERE `mid` = vMid
AND cid = pCid
LIMIT 1;
IF vVoteType NOT NULL OR vVoteType <> vVote THEN
UPDATE td_votes
SET vote = vVote,
updated = NOW()
WHERE cid = pCid
AND `mid` = pMid
LIMIT 1;
ELSE
INSERT INTO td_votes (`mid`, cid, vote)
VALUES (pMid, pCid, vVote);
END IF;
vNewVote = vCurrentVote + vVote;
UPDATE td_voted
SET vote = vNewVote
WHERE id = pCid
LIMIT 1;
SELECT pNewVote := vote FROM td_voted WHERE id = pCid LIMIT 1;
END IF;
END回答 1
Stack Overflow用户
发布于 2014-03-24 15:29:44
这是经过多次调试后对我有效的解决方案。
CREATE PROCEDURE pr_update_vote (IN pCid INT, IN pVote INT, IN pMid INT, OUT pNewVote INT)
BEGIN
DECLARE vMid, vVote, vCurrentVote, vNewVote INT;
IF pVote = 1 THEN
SET vVote = 1;
ELSE
SET vVote = -1;
END IF;
SELECT vMid = `mid`, vCurrentVote = vote FROM td_voted WHERE id = pCid LIMIT 1;
IF vMid <> pMid THEN
SELECT vVoteType = vote
FROM td_votes
WHERE `mid` = vMid
AND cid = pCid
LIMIT 1;
IF (vVoteType IS NOT NULL) OR (vVoteType <> vVote) THEN
UPDATE td_votes
SET vote = vVote,
updated = NOW()
WHERE cid = pCid
AND `mid` = pMid
LIMIT 1;
ELSE
INSERT INTO td_votes (`mid`, cid, vote)
VALUES (pMid, pCid, vVote);
END IF;
SET vNewVote = vCurrentVote + vVote;
UPDATE td_voted
SET vote = vNewVote
WHERE id = pCid
LIMIT 1;
SELECT pNewVote = vote FROM td_voted WHERE id = pCid LIMIT 1;
END IF;
END页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/22612475
复制相关文章
相似问题
腾讯云开发者
