mysql - 通过 Laravel 迁移创建/使用存储过程

Question

这个有点松鼠!

我构建了一个运行良好的存储过程。但是，当我将它放入 Laravel( Eloquent )迁移中时，迁移会构建存储过程，但在调用它时会给我一个错误 1292(截断不正确的 DOUBLE 值)。在 mysql workbench 中，我然后右键单击迁移构建的存储过程，复制创建语句，删除存储过程并从它自己的创建语句重建它......并且......一切正常!

我对任何猜测持开放态度。我显然希望这个存储过程成为我迁移的一部分，而不是需要手动构建的东西。提前致谢:

DELIMITER $$

CREATE PROCEDURE sp_cancelTasksOnApplicationCancel(IN loanAppId INT(20))

BEGIN

            SELECT loanAppId;

            #SET @taskids := 0;

            update tasks set task_status = 26, task_status_note = 'Application Cancelled or Denied' 

where loan_app_id = loanAppId AND task_status IN (22, 23, 24) AND ( SELECT @taskids := CONCAT_WS(',', id, @taskids) );        

#SELECT @taskids;

SET @cleanids = SUBSTRING(@taskids, 1, CHAR_LENGTH(@taskids) -1);

#SELECT @cleanids;

            SET @pos = 0;

            SET @len = 0;

            WHILE LOCATE(',', @cleanids, @pos+1) > 0 DO

                            SET @len = LOCATE(',', @cleanids,@pos+1) - @pos;

                            SET @id = SUBSTRING(@cleanids, @pos, @len);

                                            #select (@id);

                                            IF (@id <> 0) THEN

                                            insert into status_historys 

                                                            (loan_app_id, `type`, type_recid, type_keyfield,type_status_field, type_status_date_field, type_status_note_field, type_status_userid_field, `status`, status_date, status_note, status_userid, created_at, updated_at) 

            values(loanAppId, 'tasks', @id, 'id', 'task status', 'tasks status datetime', 'tasks status note', 'tasks status userid', 26, now(), 'Application Cancelled or Denied', 1, now(), now()); 

                                            END IF;

                            SET @pos = LOCATE(',', @taskids, @pos+@len) + 1;

            END WHILE;

END $$

DELIMITER ;

＃＃＃＃＃＃＃＃＃＃＃＃＃＃ 更新

问题出在下面两行。即使在命令行中，它也会发出有关不正确的 double 值的警告。

SET @taskids := '0';

update tasks set task_status = 22, task_status_note = 'Just Testing' 
where loan_app_id = loanAppId AND task_status IN (22, 23, 24) AND ( SELECT 
@taskids := CONCAT_WS(',', cast(id as char), @taskids) );

Answer 1

好的 - 主要问题是更新 where 子句中的 select 关键字。我相信它一直在重新声明 @taskids 用户变量，如果存储过程是从工作台构建的，mysql 会发出警告，但如果存储过程是通过迁移构建的，则会失败。

所以正确的代码是

#snip#
#Note string '0' rather than 0
SET @taskids := '0';

#note casting of id and removing the select keyword before the first @taskids
update tasks set task_status = 22, task_status_note = 'Just Testing' 
where loan_app_id = loanAppId AND task_status IN (22, 23, 24) AND (  
@taskids := CONCAT_WS(',', cast(id as char), @taskids) );

#snip#