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?

From stackoverflow Theo

• 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

  From Shane O'Grady

• 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);
  

  From GateKiller

• 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
  

  From Clyde

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

  From roo