MySQL MySQL存储过程如果存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23652026/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL stored procedure If exists
提问by Simon R
I'm creating a stored procedure in MySQL, and having trouble using IF EXISTS
我正在 MySQL 中创建一个存储过程，但在使用 IF EXISTS 时遇到问题
My SQL is;
我的 SQL 是；
CREATE DEFINER=`##`@`%` PROCEDURE `myTestProceedure`(IN _id INT)
BEGIN
IF EXISTS (SELECT * FROM cms.variables WHERE tmplvarid = 5 and id = _id) THEN
BEGIN
UPDATE cms.variables SET value = now() WHERE id = _id and tmplvarid = 5;
END;
ELSE
BEGIN
INSERT INTO cms.variables (`tmplvarid`, `contentid`, `value`) VALUES (5, _id, now());
END;
END IF;
END
Basically, what I'm trying to do in this procedure is IF the row already exists in the DB, update it, otherwise insert it.
基本上，我在此过程中要做的是，如果该行已存在于数据库中，则对其进行更新，否则将其插入。
However despite whatever result SELECT * FROM cms.variables WHERE tmplvarid = 5 and id = _idgives, it just inserts into the database.
然而，不管结果如何SELECT * FROM cms.variables WHERE tmplvarid = 5 and id = _id，它只是插入到数据库中。
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Ronak Shah
try this:
尝试这个：
CREATE DEFINER=`##`@`%` PROCEDURE `myTestProceedure`(IN _id INT)
BEGIN
IF (SELECT count(*) FROM cms.variables WHERE tmplvarid = 5 and id = _id)>0 THEN
BEGIN
UPDATE cms.variables SET value = now() WHERE id = _id and tmplvarid = 5;
END;
ELSE
BEGIN
INSERT INTO cms.variables (`tmplvarid`, `contentid`, `value`) VALUES (5, _id, now());
END;
END IF;
END
