MySQL 存储过程中是否有任何列表数据类型，或模拟它们的方法？

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

提问by Theo

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?

我想在 MySQL 中创建一个将列表作为参数的存储过程。例如，假设我希望能够在一次调用中为一个项目设置多个标签，那么我想要做的是定义一个过程，该过程接受项目的 ID 和要设置的标签列表。但是，我似乎找不到任何方法来做到这一点，据我所知，没有列表数据类型，但是可以以某种方式模拟吗？标签列表是否可以是逗号分隔的字符串，可以以某种方式拆分和循环？

How do you usually work with lists in MySQL stored procedures?

您通常如何处理 MySQL 存储过程中的列表？

采纳答案by roo

Thisarticle 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 csvtable type in mysql - that is if you are loading a flat file into the db.

你可以用mysql 中的csv表类型做一些巧妙的事情——也就是说，如果你将一个平面文件加载到数据库中。

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 answerin the above mentioned thread shows a way of doing this.

您可以在存储过程中创建一个临时表，遍历 csv 列表并将其插入到临时表中，然后创建一个从该表中选择值的游标。上面提到的线程中的这个答案显示了一种方法。

Generally I would split the array before I come to the database and then perform the query individually on each item.

通常我会在进入数据库之前拆分数组，然后对每个项目单独执行查询。

回答by Clyde

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:

根据您想要获得的复杂程度，您可以使用通用链接表。对于我的一个应用程序，用户可以选择多个报告，例如要运行报告的客户列表，而不仅仅是来自组合框中的单个客户。我有一个包含 2 个字段的单独表：

• UniqueID (guid)
• ItemID

• 唯一 ID（指南）
• 商品编号

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      

回答by Shane O'Grady

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.htmlhttp://dev.mysql.com/doc/refman/5.0/en/set.html

不确定这些是否可以专门在 SP 中工作，但是 MySQL 5 中有 ENUM 和 SET 数据类型可以满足您的需求。 http://dev.mysql.com/doc/refman/5.0/en/enum.html http://dev.mysql.com/doc/refman/5.0/en/set.html

回答by GateKiller

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!

在我选择的编程语言 C# 中，我实际上是在应用程序本身中这样做的，因为 split() 函数和循环在 C# 中比在 SQL 中更容易编程，但是！

Perhaps you should look at SubString_Index()function.

也许你应该看看SubString_Index()函数。

For example, the following would return google:

例如，以下将返回 google：

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