将数组传递给存储过程

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).

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

3 个解决方案

#1

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

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

#2

I think the main problem here is that MySQL doesn't support arrays as a data type. You need to form a one-to-many relationship to another table that contains a foreign key back to your main data and the array's data.

我认为这里的主要问题是MySQL不支持数组作为数据类型。您需要与另一个表形成一对多关系,该表包含返回主数据和数组数据的外键。

#3

I think you have to pass it in as a csv. Mysql is not very friendly with looping and such so your probably best doing it outside anyways. As far as stored procedure languages go I find Mysql really is lacking.

我认为你必须把它作为一个csv传递。 Mysql对循环不是很友好,所以你可能最好在外面做它。至于存储过程语言,我发现Mysql确实缺乏。

#1