Is there any list datatype in MySQL stored procedures, or a way to emulate them?

Question

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?

Answer 1

Hmmm, not sure if these will work specifically in a SP, but there are ENUM and SET datatypes in MySQL 5 which may do what you need. http://dev.mysql.com/doc/refman/5.0/en/enum.html http://dev.mysql.com/doc/refman/5.0/en/set.html

Answer 2

In my programming language of Choice, C#, I actually do this in the application itself because split() functions and loops are easier to program in C# then SQL, However!

Perhaps you should look at SubString_Index() function.

For example, the following would return google:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.google.com', '.', -2), '.', 1);

Answer 3

Depending on how complicated you want to get, you can use a generic linking table. For one of my applications there are several reports where the user might pick, for instance a list of customers to run the report on rather than just a single customer from a combo box. I have a separate table with 2 fields:

• UniqueID (guid)
• ItemID

The psuedo-code looks like this:

GUID guid = GenerateGUID()
try
  for each customer in customerList { INSERT(guid, customerId) }
  ExecuteSQLPocedure(guid)
  --the procedure can inner-join to the list table to get the list
finally
  DELETE WHERE UniqueID=guid

Answer 4

This 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 csv 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 answer 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.