在MySQL中将定界字符串转换为多个值

Question

我有一个mysql旧表，其中包含一个客户端标识符和一个项目列表，后者为逗号分隔的字符串。例如"xyz001", "foo,bar,baz"。这是旧的东西，用户坚持要能够编辑以逗号分隔的字符串。

现在，他们需要一个报表，上面的报表分为不同的行，例如

"xyz001", "foo"
"xyz001", "bar"
"xyz001", "baz"

将字符串分成子字符串很容易做到，我已经编写了一个过程来创建一个单独的表，但这需要触发器来处理删除，更新和插入。此查询很少需要（例如每月一次），但在运行时必须绝对最新，因此例如，不保证触发器的开销，并且计划的创建表的任务可能不够及时。

有什么方法可以编写函数来返回表或集合，以便我可以根据需要将标识符与各个项目连接起来？

Answer 1

这称为走绳子。这是一个示例，说明如何使用提供的规范进行操作：

您将需要创建一个表，其中包含与字段长度+ 1一样多的整数。因此，如果字段的长度为255，则将需要256条记录，其中仅包含0-255之间的一个数字。

int_table：

+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+

接下来，您将需要一个查询，该查询将联接到该表上，并检查该位置是否存在逗号。（我叫你的表legacy_table具等领域client，并items分别。）

select 
  legacy_table.client, 
  substring(
    legacy_table.items, 
    int_table.i + 1, 
    if(
      locate(',', legacy_table.items, int_table.i + 1) = 0, 
      length(legacy_table.items) + 1, 
      locate(',', legacy_table.items, int_table.i + 1)
    ) - (int_table.i + 1)
  ) as item
from legacy_table, int_table
where legacy_table.client = 'xyz001'
  and int_table.i < length(legacy_table.items)
  and (
    (int_table.i = 0) 
    or (substring(legacy_table.items, int_table.i, 1) = ',')
  )

您实际使用它的效率可能不够高，但是我想以它为例进行介绍，以便您了解可用的功能。