将数组传递到存储过程 [英] Pass array into a stored procedure

问题描述

我试图将值数组从php传递到mysql存储过程作为参数列表，以及如何在存储过程中使用数组.该过程中的查询中有三个IN语句，因此我想做IN(@listOfids)，其中@listOfids为1,2,3,4(来自php的内嵌数组).

解决方案

所以我得到了一种解决方法，即将查询和参数连接起来，使伪代码为

CREATE PROCEDURE `related_stories`(IN param1 VARCHAR(255), IN param2 VARCHAR(255), IN param3 VARCHAR(255), IN publishDate INT(11), IN tlimit INT(11))
BEGIN

SET  @query =CONCAT( '

select s.* from 
(

select * from 
(
 SELECT something where condition IN (',param1,')
) as table1

UNION ALL

select * from 
(
 SELECT something where condition IN (',param2,')
) as table2

UNION ALL

select * from 
(
 SELECT something where condition IN (',param3,')
) as table3

) as s

WHERE (s.publish_date < ',publishDate,') 
GROUP BY id limit ',tlimit,';');

PREPARE stmtInsert FROM @query;
EXECUTE stmtInsert;

END

param1，param2，param3是通过php传递的内爆数组，例如('1,2,3,4').希望这对某人有帮助

I am trying to pass an array of values from php to mysql stored procedures as parameter list and how to use the array inside the stored procedure. The query in the procedure has three IN statements in in there so I would like to do IN(@listOfids) where @listOfids is 1,2,3,4 (an imploded array from php).

解决方案

So I got a workaround which is to concatenate the query and the parameters so the pseudo code is

CREATE PROCEDURE `related_stories`(IN param1 VARCHAR(255), IN param2 VARCHAR(255), IN param3 VARCHAR(255), IN publishDate INT(11), IN tlimit INT(11))
BEGIN

SET  @query =CONCAT( '

select s.* from 
(

select * from 
(
 SELECT something where condition IN (',param1,')
) as table1

UNION ALL

select * from 
(
 SELECT something where condition IN (',param2,')
) as table2

UNION ALL

select * from 
(
 SELECT something where condition IN (',param3,')
) as table3

) as s

WHERE (s.publish_date < ',publishDate,') 
GROUP BY id limit ',tlimit,';');

PREPARE stmtInsert FROM @query;
EXECUTE stmtInsert;

END

param1,param2,param3 are imploded arrays that is passed in via php e.g.('1,2,3,4'). Hope this helps someone

这篇关于将数组传递到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！