在MySQL中将定界字符串转换为多个值 [英] Converting delimited string to multiple values in mysql

问题描述

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

I have a mysql legacy table which contains an client identifier and a list of items, the latter as a comma-delimited string. E.g. "xyz001", "foo,bar,baz". This is legacy stuff and the user insists on being able to edit a comma delimited string.

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

They now have a requirement for a report table with the above broken into separate rows, e.g.

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

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

Breaking the string into substrings is easily doable and I have written a procedure to do this by creating a separate table, but that requires triggers to deal with deletes, updates and inserts. This query is required rarely (say once a month) but has to be absolutely up to date when it is run, so e.g. the overhead of triggers is not warranted and scheduled tasks to create the table might not be timely enough.

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

Is there any way to write a function to return a table or a set so that I can join the identifier with the individual items on demand?

推荐答案

这称为遍历字符串.这是一个示例，说明如何使用提供的规范进行操作:

This is called walking a string. Here's an example of how you might do it with the specs provided:

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

You'll need to create a table which contains as many integers as the length of the field + 1. So if the field's length is 255, you will need 256 records which just contain a single number from 0-255.

int_table:

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

接下来，您将需要一个查询，该查询将联接到该表上，并检查该位置是否存在逗号. (我分别将字段client和items称为表legacy_table.)

Next, you will need a query which joins on this table and checks if a comma exists in that location or not. (I called your table legacy_table with the fields client and items, respectively.)

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) = ',')
  )

对于您实际使用它可能效率不高，但我想以它为例进行介绍，以便您了解可用的内容.

It may not be efficient enough for you to actually use it, but I thought I'd present it as an example just so you know what is available.

这篇关于在MySQL中将定界字符串转换为多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！