MySQL 8.0.26触发器报错：SQLSTATE[42000] 1231错误——MESSAGE_TEXT变量无法设置为NULL值

Hey there! Let's break down why you're hitting this error and how to fix it quickly.

Root Cause of the Error #

The error SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL' happens because MySQL strictly requires the message_text parameter in the SIGNAL statement to be a non-NULL string.

Looking at your trigger code, the problematic section is the final SELECT INTO block:

select userid into userid1 from mymembers where memberid=new.memberid AND userid!=NEW.userid; 
set msg = CONCAT('Error : ', cast(userid1 as char)); 
SIGNAL sqlstate '45000' set message_text = msg; 

If the SELECT query doesn't find any matching rows (i.e., no conflicting userid for the same memberid), userid1 gets assigned NULL. Concatenating a string with NULL results in NULL—and passing this to message_text triggers the 1231 error.

Solution: Add a NULL Check for the Final Validation #

You only want to throw the error if the query actually finds a conflicting record. Add a condition to check if userid1 is not NULL before triggering the signal. Here's the corrected block:

select userid into userid1 from mymembers where memberid=new.memberid AND userid!=NEW.userid; 
-- Only trigger the error if we found a conflicting userid
if userid1 is not null then
    set msg = CONCAT('Error : ', cast(userid1 as char)); 
    SIGNAL sqlstate '45000' set message_text = msg; 
end if;

Full Corrected Trigger Code #

Here's the complete trigger with the fix applied:

CREATE DEFINER = 'admin'@'%' TRIGGER db2.members_b_u BEFORE UPDATE ON db2.mymembers FOR EACH ROW 
begin 
declare msg VARCHAR(200); 
DECLARE userid1 INT; 

if new.memberid IS null then 
    set msg = CONCAT('Error .: ', cast(new.userid as char)); 
    SIGNAL sqlstate '45000' set message_text = msg; 
end if; 

if new.memberlevel <>'Π19' AND new.memberlevel <>'ΜΜ' AND new.memberlevel is not null AND new.city IS NULL then 
    set msg = CONCAT('Error : ', cast(new.am as char)); 
    SIGNAL sqlstate '45000' set message_text = msg; 
end if; 

if LENGTH(new.postalcode1)<>5 AND new.city1 between '01' and '99' then 
    set msg = CONCAT('Error : ', cast(new.userid as char)); 
    SIGNAL sqlstate '45000' set message_text = msg; 
end if; 

select userid into userid1 from mymembers where memberid=new.memberid AND userid!=NEW.userid; 
if userid1 is not null then
    set msg = CONCAT('Error : ', cast(userid1 as char)); 
    SIGNAL sqlstate '45000' set message_text = msg; 
end if; 

END 

Quick Extra Tip #

To avoid similar issues elsewhere in your trigger, make sure any value concatenated into msg can't be NULL. For example, if new.am could ever be NULL, use COALESCE(cast(new.am as char), 'unknown value') to fall back to a valid string.

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