Is there any list datatype in MySQL stored procedures, or a way to emulate them?,
I would like to create a stored procedure in MySQL that took a list as argument. For example, say that I would like to be able to set multiple tags for an item in one call, then what I want to do is to define a procedure that takes the ID of the item and a list of tags to set. However, I can’t seem to find any way to do this, there is no list datatype, as far as I’m aware, but can it be emulated somehow? Could the list of tags be a comma-separated string, which can somehow be split and looped over?
How do you usually work with lists in MySQL stored procedures?
article has some good discussion on the problem of parsing an array to a stored procedure since stored procedures only allow valid table column data-types as parameters.
There are some neat things you can do with the table type in mysql – that is if you are loading a flat file into the db.
You could create a temporary table in the stored procedure, iterate over the csv list and insert it to the temp table, then create a cursor which selects the values from that table. This in the above mentioned thread shows a way of doing this.
Generally I would split the array before I come to the database and then perform the query individually on each item.
That’s the answer Is there any list datatype in MySQL stored procedures, or a way to emulate them?, Hope this helps those looking for an answer. Then we suggest to do a search for the next question and find the answer only on our site.
The answers provided above are only to be used to guide the learning process. The questions above are open-ended questions, meaning that many answers are not fixed as above. I hope this article can be useful, Thank you