MySQL 8.0触发器中自定义变量失效问题求助

使用MySQL 8.0（mysql/mysql-server:8.0镜像）创建了books_api_db数据库与users表，定义@min_username_length等会话变量，并编写了校验用户名长度的BEFORE INSERT和BEFORE UPDATE触发器。但执行插入短用户名的SQL语句时，触发器未触发且无报错：

insert into users(username, passwd, email) values ("asd", "asdASD123!@#", "asdasd@asd");

相关建表及触发器代码如下：

CREATE DATABASE IF NOT EXISTS books_api_db;
USE books_api_db;

CREATE TABLE IF NOT EXISTS users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  passwd VARCHAR(64) NOT NULL, -- TO DO: obfuscate password 
  email VARCHAR(64) NOT NULL UNIQUE,
  access_level ENUM('admin', 'unconfirmed_user') NOT NULL DEFAULT 'unconfirmed_user',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

SET @min_username_length := 5;
SET @min_passwd_length := 5;
SET @min_email_length := 5;

DELIMITER //

-- validate username

CREATE TRIGGER IF NOT EXISTS validate_username_on_insert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.username) < @min_username_length THEN
    SET @message_text = CONCAT('Username must have a minimum of ', @min_username_length, ' characters.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text;
  END IF;
END//

CREATE TRIGGER IF NOT EXISTS validate_username_on_update BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.username) < @min_username_length THEN
    SET @message_text = CONCAT('Username must have a minimum of ', @min_username_length, ' characters.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text;
  END IF;
END//

触发器未触发的核心问题在于触发器依赖了会话级变量@min_username_length：

• @开头的变量是MySQL会话变量，仅在当前数据库连接会话内有效。如果创建触发器后断开原会话，或在新会话中执行插入操作，@min_username_length会因不存在被MySQL视为NULL。
• 当@min_username_length为NULL时，CHAR_LENGTH(NEW.username) < @min_username_length的比较结果为NULL，不满足IF执行条件，触发器不会抛出错误，表现为“未触发”。
• 即使在同一会话中，若后续误重置该变量（如SET @min_username_length := 0），同样会导致校验逻辑失效。

推荐两种替代方案，避免会话变量的依赖问题：

方案1：使用全局变量（支持动态修改） #

将会话变量改为全局变量（@@开头），确保触发器执行时变量存在有效值（需SUPER权限）：

-- 设置全局变量
SET GLOBAL min_username_length := 5;
SET GLOBAL min_passwd_length := 5;
SET GLOBAL min_email_length := 5;

-- 更新触发器逻辑
DELIMITER //

DROP TRIGGER IF EXISTS validate_username_on_insert;
CREATE TRIGGER validate_username_on_insert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.username) < @@min_username_length THEN
    SET @message_text = CONCAT('Username must have a minimum of ', @@min_username_length, ' characters.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text;
  END IF;
END//

DROP TRIGGER IF EXISTS validate_username_on_update;
CREATE TRIGGER validate_username_on_update BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.username) < @@min_username_length THEN
    SET @message_text = CONCAT('Username must have a minimum of ', @@min_username_length, ' characters.');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text;
  END IF;
END//

DELIMITER ;

方案2：使用触发器内常量（更稳定） #

如果不需要动态修改用户名最小长度，直接在触发器中硬编码数值，消除变量依赖：

DELIMITER //

DROP TRIGGER IF EXISTS validate_username_on_insert;
CREATE TRIGGER validate_username_on_insert BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.username) < 5 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username must have a minimum of 5 characters.';
  END IF;
END//

DROP TRIGGER IF EXISTS validate_username_on_update;
CREATE TRIGGER validate_username_on_update BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
  IF CHAR_LENGTH(NEW.username) < 5 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username must have a minimum of 5 characters.';
  END IF;
END//

DELIMITER ;

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