Passing an array to a stored procedure

Question

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

Answer 1

So, I have a workaround, which is to combine the request and the parameters to pseudo

 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 modulated arrays that are passed through php, for example, ('1,2,3,4'). Hope this helps someone.

Answer 2

I think the main problem here is that MySQL does not support arrays as a data type. You need to create a one-to-many relationship with another table that contains the foreign key for your master data and array data.

Answer 3

I think you need to pass it as csv. Mysql is not very friendly with the loop, and so you are probably best doing it outside of any direction. Regarding stored procedure languages, I think Mysql is really missing.