我正在游戲服務器上建立一個自動排名，我必須通過操縱 MySQL 資料庫來做到這一點，這對我來說不是一個強大的知識領域，所以我試圖對此有點笨拙。我需要使用 SQL db 的觸發器功能。
CREATE TRIGGER `ranking_up` AFTER UPDATE ON `highscore` FOR EACH ROW
BEGIN
IF NEW.score >= '10' and <= '100' THEN
UPDATE department_members
SET rankID=1 WHERE userID = NEW.userID;
END IF;
IF NEW.score >= '100' and <= '300' THEN
UPDATE department_members
SET rankID=2 WHERE userID = NEW.userID;
END IF;
IF NEW.score >= '300' THEN
UPDATE department_members
SET rankID=3 WHERE userID = NEW.userID;
END IF;
END
我得到了標準的 MySQL #1064 并且我試圖與我的橡皮鴨交談...不起作用這對我來說很有意義但顯然不起作用。
我正在尋找ofc的答案，但我也想在這里學習我的錯誤，我做錯了什么？
uj5u.com熱心網友回復：
你不能逃脫 NEW.score >= '10' 和 >= '100' 你需要重復 new.score NEW.score >= '10' 和 new.score >= '100' 。如果 new.score 存盤為整數，您可以在兩者之間使用更容易理解...
delimiter $$
CREATE TRIGGER `ranking_up` AFTER UPDATE ON `highscore` FOR EACH ROW
BEGIN
IF NEW.score between 10 and 99 THEN
UPDATE department_members
SET rankID=1 WHERE userID = NEW.userID;
END IF;
IF NEW.score between 100 and 299 THEN
UPDATE department_members
SET rankID=2 WHERE userID = NEW.userID;
END IF;
IF NEW.score >= 300 THEN
UPDATE department_members
SET rankID=3 WHERE userID = NEW.userID;
END IF;
END $$
delimiter ;
見https://www.db-fiddle.com/f/51PoUTCMAuWFoYaAz5ubW7/0
或者您可以在更新陳述句中完全省去 ifs 和用例。
delimiter $$
CREATE TRIGGER `ranking_up` AFTER UPDATE ON `highscore` FOR EACH ROW
BEGIN
UPDATE department_members
SET RANK =
CASE
WHEN NEW.score between 10 and 99 THEN 1
WHEN NEW.score between 100 and 299 THEN 2
WHEN NEW.score >= 300 THEN 3
END
WHERE userID = NEW.userID;
END $$
delimiter ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/342406.html
標籤：mysql if 语句 触发器 mysql-错误-1064
上一篇：與Web開發中的用戶資料相關