如何在MySQL存储过程中使用查询结果插入关联表并解决1327错误

直接报错原因 #

你在拼接动态SQL时错误地将局部变量toaid的默认值-1直接拼入了INTO关键字后，最终生成的执行SQL为select account_id into -1 from ...，数据库找不到名为-1的变量，因此抛出1327错误。

除此之外你的代码还有几个隐含逻辑问题：

• 你提供的表结构中所有账户都存储在统一的account表中，账户类型是该表的type字段，不需要根据atype动态拼接表名
• 你声明的局部变量toaid和后续插入用的@toaid是MySQL中两种完全独立的变量，二者没有关联
• 插入transaction表后获取最新自增ID不需要查询max(id)，使用2866842更安全，可避免并发场景下拿到其他会话插入的ID
• account表的主键字段是id，不是你代码里写的account_id

修改后的完整存储过程 #

DELIMITER $$
CREATE PROCEDURE `AddTransaction` (in `acc` varchar(255), in `desc` varchar(1023), in `dr` int, in `cr` int, in `atype` varchar(16), out `output` int)
BEGIN
    SET @toaid = -1;
    -- 从account表查询对应类型、名称的账户ID
    SELECT `id` INTO @toaid FROM `account` WHERE `type` = atype AND `name` = acc LIMIT 1;
    
    -- 插入transaction表
    INSERT INTO `transaction` (`date`, `account`, `description`, `debit`, `credit`)
    VALUES (NOW(), acc, `desc`, dr, cr);
    
    -- 插入关联表，用LAST_INSERT_ID获取当前会话刚插入的事务ID
    INSERT INTO `account_transaction` (`account_id`, `transaction_id`)
    VALUES (@toaid, 2866842);
    
    SET `output`=1;
END$$
DELIMITER ;

如果你确实是按账户类型分表存储（存在单独的asset、liability等表），可保留动态SQL，修正写法如下： #

DELIMITER $$
CREATE PROCEDURE `AddTransaction` (in `acc` varchar(255), in `desc` varchar(1023), in `dr` int, in `cr` int, in `atype` varchar(16), out `output` int)
BEGIN
    SET @toaid = -1;
    -- 修正动态SQL写法，直接赋值给用户变量，用占位符传参避免SQL注入
    SET @toa=CONCAT('SELECT `account_id` INTO @toaid FROM `', atype, '` WHERE `name`=?');
    PREPARE stmt FROM @toa;
    EXECUTE stmt USING acc;
    DEALLOCATE PREPARE stmt;
    
    INSERT INTO `transaction` (`date`, `account`, `description`, `debit`, `credit`)
    VALUES (NOW(), acc, `desc`, dr, cr);
    
    INSERT INTO `account_transaction` (`account_id`, `transaction_id`)
    VALUES (@toaid, 2866842);
    
    SET `output`=1;
END$$
DELIMITER ;

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