MariaDB 10.5版本UNION查询搭配INTO子句赋值报1064语法错误

MariaDB 10.4及以上版本调整了UNION联合查询语句的INTO赋值子句的语法解析规则：

• 10.3及更早版本：允许将INTO子句放在UNION最后一个SELECT的字段列表与FROM关键字之间，你此前的写法可以正常执行
• 10.4及更高版本：要求INTO子句必须放在整个UNION语句的最末尾，不能内嵌到某一个单独的SELECT子句中，因此旧写法触发1064语法错误

简化测试语句修正示例 #

原错误写法：

SET @ID = '';
SELECT id FROM table1 
UNION 
SELECT id 
 INTO @ID 
FROM table2;

修正后兼容所有版本的写法：

SET @ID = '';
SELECT id FROM table1 
UNION 
SELECT id FROM table2
INTO @ID;

触发器修正代码 #

将INTO @tire, @ID, @Name从最后一个SELECT子句的字段列表后，挪到整个UNION语句的末尾即可：

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER CRPInsertTrigger
BEFORE INSERT ON CRP
FOR EACH ROW
BEGIN
    SET @msg = '';
    SET @tire = @ID = @Name = '';

    SELECT 'coMem' `tire`, coMemID `ID`, coMemName `Name` FROM coMem 
    WHERE institute = NEW.institute and registration = NEW.registration and bookNo = NEW.bookNo
    UNION
    SELECT 'commercialMem' `tire`, commercialMemID `ID`, commercialMemName `Name` FROM commercialMem 
    WHERE institute = NEW.institute and registration = NEW.registration and bookNo = NEW.bookNo
    UNION
    SELECT 'communityGeneralMem' `tire`, communityGeneralMemID `ID`, communityGeneralMemName `Name` FROM communityGeneralMem 
    WHERE institute = NEW.institute and registration = NEW.registration and bookNo = NEW.bookNo
    UNION
    SELECT 'CRP' `tire`, CRPID `ID`, CRPName `Name` FROM CRP 
    WHERE institute = NEW.institute and registration = NEW.registration and bookNo = NEW.bookNo
    UNION
    SELECT 'lsoMem' `tire`, lsoMemID `ID`, lsoMemName `Name` FROM lsoMem 
    WHERE institute = NEW.institute and registration = NEW.registration and bookNo = NEW.bookNo
    UNION
    SELECT 'voMem' `tire`, voMemID `ID`, voMemName `Name` FROM voMem 
    WHERE institute = NEW.institute and registration = NEW.registration and bookNo = NEW.bookNo
    INTO @tire, @ID, @Name;

    IF @Name != '' THEN
    SET @msg = CONCAT('Error: Duplicate in registration found: ', CAST(NEW.registration AS CHAR CHARACTER SET utf8) COLLATE utf8_bin);
    SET @msg = CONCAT(@msg, '(', @tire, 'ID:',CAST(@ID AS CHAR CHARACTER SET utf8) COLLATE utf8_bin);
    SET @msg = CONCAT(@msg, ', Name:', CAST(@Name AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, ')');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @msg;
    END IF;
END */;;
DELIMITER ;

内容的提问来源于stack exchange，提问作者Adeel Raza Azeemi