MariaDB中SET类型字段触发器异常问题求助

问题现象 #

创建触发器时无语法报错，但向notification_types表插入数据时触发#1054错误：Unknown column 'available_methods' in 'field list'。该问题仅针对SET/ENUM类型字段出现，不同MariaDB版本表现有差异：

• MariaDB 10.3：仅AFTER UPDATE触发器触发该错误，AFTER INSERT触发器可正常运行
• MariaDB 11.x：AFTER INSERT触发器也出现该错误，必须通过声明变量中转NEW.available_methods才能正常执行

触发代码对比 #

报错的触发器代码 #

CREATE TRIGGER `add_notification_type` AFTER INSERT ON `notification_types`
 FOR EACH ROW BEGIN
    INSERT IGNORE INTO unit_templates (unit, idkey, method)
    SELECT u.id AS unit, NEW.idkey, m.method
    FROM units AS u
    JOIN (
            SELECT 'sms' as method
            UNION ALL
            SELECT 'push'
            UNION ALL
            SELECT 'email'
            UNION ALL
            SELECT 'chat'
            UNION ALL
            SELECT 'info'
    ) AS m ON FIND_IN_SET(m.method, NEW.available_methods) > 0;
END

正常运行的触发器代码（变量中转） #

CREATE TRIGGER `add_notification_type` AFTER INSERT ON `notification_types`
 FOR EACH ROW BEGIN
    DECLARE available_methods_var VARCHAR(255);

    SET available_methods_var = NEW.available_methods;

    INSERT IGNORE INTO unit_templates (unit, idkey, method)
    SELECT u.id AS unit, NEW.idkey, m.method
    FROM units AS u
    JOIN (
        SELECT 'sms' as method
        UNION ALL
        SELECT 'push'
        UNION ALL
        SELECT 'email'
        UNION ALL
        SELECT 'chat'
        UNION ALL
        SELECT 'info'
    ) AS m ON FIND_IN_SET(m.method, available_methods_var) > 0;
END

相关表结构 #

-- notification_types表结构
CREATE TABLE `notification_types` (
  `idkey` varchar(40) NOT NULL,
  `title` varchar(100) NOT NULL,
  `editable` tinyint(1) NOT NULL DEFAULT 1,
  `has_days` tinyint(1) NOT NULL DEFAULT 0,
  `default_days` int(3) NOT NULL DEFAULT 0,
  `has_sessions` tinyint(1) NOT NULL DEFAULT 0,
  `default_sessions` int(3) NOT NULL DEFAULT 0,
  `available_methods` set('sms','push','email','chat','info') NOT NULL,
  `default_methods` set('sms','push','email','chat','info') NOT NULL,
  `class` set('announcements','info','marketing','system') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- units表结构
CREATE TABLE `units` (
  `id` int(11) NOT NULL,
  `name` varchar(40) NOT NULL,
  `active` int(1) NOT NULL DEFAULT 1,
  `waiting` tinyint(1) NOT NULL DEFAULT 0,
  `suspended` int(1) NOT NULL DEFAULT 0,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_time` int(20) NOT NULL DEFAULT 0,
  `acl` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `custom_fields` varchar(1000) NOT NULL DEFAULT '',
  `code` varchar(15) DEFAULT NULL,
  `settings` text NOT NULL DEFAULT '{}'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- unit_templates表结构
CREATE TABLE `unit_templates` (
  `unit` int(11) NOT NULL,
  `idkey` varchar(40) NOT NULL,
  `method` set('sms','push','email','chat','info') NOT NULL,
  `template` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

问题原因 #

这是MariaDB查询解析器的已知bug：当在触发器的JOIN关联条件中直接引用NEW.set_column（SET/ENUM类型字段）时，解析器错误地将其识别为当前查询上下文（如子查询或关联表）中的字段，而非触发器的NEW对象属性，从而抛出字段不存在的错误。

通过变量中转时，局部变量会被明确解析为触发器作用域内的变量，绕过了查询上下文的解析混淆。该bug在MariaDB 10.3中仅存在于UPDATE触发器场景，后续版本（如11.x）调整了查询解析逻辑，导致INSERT触发器也触发了同样的解析问题。

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