我有一个mysql旧表,其中包含一个客户端标识符和一个项目列表,后者为逗号分隔的字符串。例如"xyz001", "foo,bar,baz"。这是旧的东西,用户坚持要能够编辑以逗号分隔的字符串。
"xyz001", "foo,bar,baz"
现在,他们需要一个报表,上面的报表分为不同的行,例如
"xyz001", "foo" "xyz001", "bar" "xyz001", "baz"
将字符串分成子字符串很容易做到,我已经编写了一个过程来创建一个单独的表,但这需要触发器来处理删除,更新和插入。此查询很少需要(例如每月一次),但在运行时必须绝对最新,因此例如,不保证触发器的开销,并且计划的创建表的任务可能不够及时。
有什么方法可以编写函数来返回表或集合,以便我可以根据需要将标识符与各个项目连接起来?
这称为走绳子。这是一个示例,说明如何使用提供的规范进行操作:
您将需要创建一个表,其中包含与字段长度+ 1一样多的整数。因此,如果字段的长度为255,则将需要256条记录,其中仅包含0-255之间的一个数字。
int_table:
int_table
+---+ | i | +---+ | 0 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +---+
接下来,您将需要一个查询,该查询将联接到该表上,并检查该位置是否存在逗号。(我叫你的表legacy_table具等领域client,并items分别。)
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) = ',') )
您实际使用它的效率可能不够高,但是我想以它为例进行介绍,以便您了解可用的功能。