CREATE PROCEDURE Click(
   in  ipaddress varchar(45))
BEGIN
   DECLARE ex int;
   SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
IF ex = 0 THEN
   INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0);
END IF;   
   UPDATE clickactivity SET Clicks = Clicks + 1;
END

它像這樣抱怨第 4 行：

#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 '' at line 4

有兩件事不對

首先，使用DELIMITER $$這樣分號在儲存過程中而不是命令行中被解釋。定義儲存過程後，將分隔符切換回分號 ( ;)。

接下來，UPDATE clickactivity SET Clicks = Clicks + 1;將更新每一行，而您不希望這樣。

建議 #1

添加WHERE IP = ipaddress;到UPDATE clickactivity

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
   DECLARE ex int;
   SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
   IF ex = 0 THEN
       INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 0);
   END IF;   
   UPDATE clickactivity SET Clicks = Clicks + 1 WHERE IP = ipaddress;
END $$
DELIMITER ;

建議 #2

為什麼要註冊一次兩次或三次查詢？

更改程式碼以執行查詢，使其同時插入和更新

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
   INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
   ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;
END $$
DELIMITER ;

在這種情況下，您不需要儲存過程。只需執行 INSERT

INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;

當然，如果ipaddressis PRIMARY KEYor 是UNIQUE KEYin ，則這樣做是有效的clickactivity。

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/83183