MySQL (mariaDB) 觸發器語法總是錯誤的

我正在做一個小遊戲，我需要在我的members桌子上有一個觸發器，它將檢查experience列，並且只要它變得大於某些值，level就會變成level+1. 這是觸發器：

DELIMITER $$
CREATE TRIGGER update_level AFTER UPDATE ON members
FOR EACH ROW BEGIN
   IF experience < 1000 THEN 
       SET members.level = 1
   ELSE IF experience < 2400 THEN 
       SET level = 2
   ELSE IF (experience < 4000) THEN 
       SET level=3
   ELSE IF (experience < 8000) THEN 
       SET level=4
   ELSE IF (experience < 15000) THEN 
       SET level=5
   ELSE IF (experience < 24000) THEN 
       SET level=6
   ELSE IF (experience < 35000) THEN 
       SET level=7
   ELSE IF (experience < 50000) THEN 
       SET level=8
   END IF
END
$$
DELIMITER ;

phpMyAdmin 總是用這個錯誤程式碼響應：

#1064 - 您的 SQL 語法有錯誤；檢查與您的 MariaDB 伺服器版本相對應的手冊，以了解要使用的正確語法

我花了大約 2 個小時試圖弄清楚，但看起來我自己無法做到。我嘗試過的是將IF語句更改為CASE語句（簡單開關）。我已經試過了WHEN xxx THEN SET。還與UPDATE. 我嘗試過的另一件事是在列名和值周圍添加“”和“”——根本沒有幫助。

你能告訴我什麼是實現這一目標的最佳方法嗎？我很確定那裡有一些小錯誤，如果不是這樣，那麼我的方法一定是錯誤的。

在嘗試編譯原始觸發器程式碼時，我們遇到了很多問題。正如一些同事所指出的，應該調整時間（“之後”）。也有一些句法和邏輯問題（例如 < 而不是 >）。也許使用 BETWEEN … AND … 來定義“級別”會更好。

--  test data

create table members (member_id int, experience int, level_ int);

insert into members values (1,2200,1),(2,3800,2),(3,7800,3);

觸發器 (MySQL 5.7)

DELIMITER $$
create trigger update_level 
 before update on members
 for each row
begin
 if new.experience between 0 and 999 then
   set new.level_ = 1;  
 elseif new.experience between 1000 and 2399 then
   set new.level_ = 2;
 elseif new.experience between 2400 and 3999 then
   set new.level_ = 3 ;
 elseif new.experience between 4000 and 7999 then
   set new.level_ = 4 ;
 elseif new.experience between 8000 and 14999 then
   set new.level_ = 5 ;
 elseif new.experience between 15000 and 23999 then
   set new.level_ = 6 ;
 elseif new.experience between 24000 and 34999 then
   set new.level_ = 7 ;
 elseif new.experience between 35000 and 49999 then
   set new.level_ = 8 ;
 elseif new.experience >= 50000 then
   set new.level_ = 9 ;    
 end if;
end;
$$
DELIMITER ;

測試

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |       2200 |      1 |
|         2 |       3800 |      2 |
|         3 |       7800 |      3 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

update members set experience = 8500 where member_id = 1 ;
update members set experience = 16000 where member_id = 2 ;
update members set experience = 25000 where member_id = 3 ;

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |       8500 |      5 |
|         2 |      16000 |      6 |
|         3 |      25000 |      7 |
+-----------+------------+--------+
3 rows in set (0.00 sec)

update members set experience = 36000 where member_id = 1 ;
update members set experience = 55000 where member_id = 2 ;
update members set experience = 10 where member_id = 3 ;

mysql> select * from members;
+-----------+------------+--------+
| member_id | experience | level_ |
+-----------+------------+--------+
|         1 |      36000 |      8 |
|         2 |      55000 |      9 |
|         3 |         10 |      1 |
+-----------+------------+--------+
3 rows in set (0.01 sec)

選擇

對於這種情況，視圖可能是更好的解決方案。我們可以為 MEMBERS 使用不同的表佈局，並避免儲存級別（“級別”取決於“經驗”，而不是 member_id …），就像這樣（MariaDB 10.2）：

-- test data
create table members (member_id int, experience int);

insert into members values (1,2200),(2,3800),(3,7800);

現在我們可以通過從 VIEW 中選擇來找到與 member_id 關聯的目前級別。無需觸發器。

create or replace view member_level
as
select
 member_id
, experience
, case
   when experience between 0 and 999 then 'level 1'
   when experience between 1000 and  2399 then 'level 2' 
   when experience between 2400 and  3999 then 'level 3' 
   when experience between 4000 and  7999 then 'level 4' 
   when experience between 8000 and 14999 then 'level 5' 
   when experience between 15000 and 23999 then 'level 6' 
   when experience between 24000 and  34999 then 'level 7' 
   when experience between 35000 and  49999 then 'level 8' 
   when experience >= 50000 THEN 'level 9' 
 end as level_
from members ; 

測試

select * from members;

member_id  experience
1          2200
2          3800
3          7800

update members
set experience = 27500 
where member_id = 1 ;

select * from member_level;

member_id  experience  level_
1          27500       level 7
2           3800       level 3
3           7800       level 4

請參閱此處的dbfiddle 。

另一種選擇（MariaDB 版本 5.2+，MySQL 5.7）

使用虛擬列，讓 DDL 完成工作。

create table members (
 member_id int
, experience int
, level_ varchar(32) as (
   case
     when experience between 0 and 999 then 'level 1'
     when experience between 1000 and  2399 then 'level 2' 
     when experience between 2400 and  3999 then 'level 3' 
     when experience between 4000 and  7999 then 'level 4' 
     when experience between 8000 and 14999 then 'level 5' 
     when experience between 15000 and 23999 then 'level 6' 
     when experience between 24000 and  34999 then 'level 7' 
     when experience between 35000 and  49999 then 'level 8' 
     when experience >= 50000 THEN 'level 9' 
   end ) virtual
);

insert into members (member_id, experience) values (1,2200),(2,3800),(3,7800);

select * from members;

member_id  experience  level_
1          2200        level 2
2          3800        level 3
3          7800        level 4

Dbfiddle 在這裡。

NEW.level，不是members.level。也許它應該是BEFORE UPDATE。

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