修改MySQL生成列时出现SQL语法错误求助

问题场景 #

尝试将表t1的criteria列修改为STORED类型的生成列，通过嵌套CASE语句编写生成逻辑，但执行ALTER TABLE时触发#1064语法错误，错误指向GENERATED ALWAYS AS部分。

原ALTER语句 #

ALTER TABLE
    `t1` MODIFY COLUMN `criteria` GENERATED ALWAYS AS(
        CASE WHEN `status` = 'Active' THEN
            CASE WHEN `exempt` = 'FALSE' AND `age` < 35 THEN
                CASE WHEN `experience` = "Pro" AND `rule` = 'Descendent' THEN 6 
                WHEN `experience` = "Pro" AND `rule` != 'Descendent' THEN 5 
                WHEN `experience` = 'Division I' AND `rule` = 'Descendent' THEN 5 
                WHEN `experience` = 'Division I' AND `rule` != 'Descendent' THEN 4 
                WHEN `experience` = 'Division II' AND `rule` = 'Descendent' THEN 4 
                WHEN `experience` = 'Division II' AND `rule` != 'Descendent' THEN 3 
                WHEN `experience` = 'Division III' AND `rule` = 'Descendent' THEN 3 
                WHEN `experience` = 'Division III' AND `rule` != 'Descendent' THEN 2 
                WHEN `experience` = 'NAIA' AND `rule` = 'Descendent' THEN 3 
                WHEN `experience` = 'NAIA' AND `rule` != 'Descendent' THEN 2 
                WHEN `experience` = 'Junior College' AND `rule` = 'Descendent' THEN 2 
                WHEN `experience` = 'Junior College' AND `rule` != 'Descendent' THEN 1 
                WHEN `experience` = 'High School' AND `rule` = 'Descendent' THEN 1 
                ELSE 0
                END
         ELSE 0
         END
 ELSE 0
 END
) STORED

错误信息 #

MySQL said: Documentation

#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 'GENERATED ALWAYS AS(
CASE WHEN status = 'Active' THEN
CA' at line 2

问题排查与修正 #

核心错误点 #

1. 缺少生成列数据类型：MySQL要求生成列必须明确指定数据类型（如INT），原语句中MODIFY COLUMN criteria``后直接写GENERATED ALWAYS AS，未定义数据类型，这是触发语法错误的主要原因。
2. 引号混用：SQL字符串需使用单引号，原语句中"Pro"这类双引号写法会被MySQL识别为标识符（除非修改sql_mode），需统一改为单引号。
3. HTML转义字符：原语句中的<是HTML转义格式，实际SQL中应使用<作为比较运算符。

修正后的语句 #

ALTER TABLE
    `t1` MODIFY COLUMN `criteria` INT GENERATED ALWAYS AS(
        CASE WHEN `status` = 'Active' THEN
            CASE WHEN `exempt` = 'FALSE' AND `age` < 35 THEN
                CASE WHEN `experience` = 'Pro' AND `rule` = 'Descendent' THEN 6 
                WHEN `experience` = 'Pro' AND `rule` != 'Descendent' THEN 5 
                WHEN `experience` = 'Division I' AND `rule` = 'Descendent' THEN 5 
                WHEN `experience` = 'Division I' AND `rule` != 'Descendent' THEN 4 
                WHEN `experience` = 'Division II' AND `rule` = 'Descendent' THEN 4 
                WHEN `experience` = 'Division II' AND `rule` != 'Descendent' THEN 3 
                WHEN `experience` = 'Division III' AND `rule` = 'Descendent' THEN 3 
                WHEN `experience` = 'Division III' AND `rule` != 'Descendent' THEN 2 
                WHEN `experience` = 'NAIA' AND `rule` = 'Descendent' THEN 3 
                WHEN `experience` = 'NAIA' AND `rule` != 'Descendent' THEN 2 
                WHEN `experience` = 'Junior College' AND `rule` = 'Descendent' THEN 2 
                WHEN `experience` = 'Junior College' AND `rule` != 'Descendent' THEN 1 
                WHEN `experience` = 'High School' AND `rule` = 'Descendent' THEN 1 
                ELSE 0
                END
         ELSE 0
         END
 ELSE 0
 END
) STORED;

额外说明 #

如果原criteria列已有数据，修改为生成列前需确保原列数据与生成逻辑的计算结果一致；若允许丢失原数据，也可先删除原列再添加生成列。

内容的提问来源于stack exchange，提问作者Jared