【MySql 实战】高度聚合的数据项拆分为多行多列
前言
在 触发器案例分享 这篇文章,提到了我在工作中遇到的场景,使用触发器对表记录变更进行记录并写入到另一张表内。
其中有一个细节,因为需要记录到每一行的每一个字段的前后值变化,如果每个字段都单独记录的话,假定这样的场景,有 1 条更新 sql,改动了 20+ 个字段,那么就需要向 t_log 插入 20+ 记录,这会使得 t_log 表记录数量膨胀非常迅速
当时的解决方案为:将这些数据,通过添加分隔符的方式,合并存储到同一个字段上,模拟的数据格式为:
DB 中实际存储的数据效果如下所示:
由于是在触发器中做的字符串拼接,就没有去掉每一行最后的分隔符以及最后 1 个空行
考虑使用单个符号分隔可能会有误切割的情况,决定使用组合符号
,,.
来做分隔符
新的需求
在试用一段时间后,业务部门提新需求,希望将变更的数据记录,以报表的形式,展现到页面上。但页面上绝不可能用一个格子显示上面的组合数据,那就必须要将这个数据按行切割成单独记录,再按列切割得到 名称、改动前值、改动后值
3 个子项,预期的效果类如下:
![](https://static001.geekbang.org/infoq/12/129aef1d0b038f5c52d356d8d7ac2528.png)
一个比较尴尬的点在于,我们是在敏捷平台上进行的开发,即 sql 写好,再写一些配置项,就会生成标准的表单页和列表页,即需要我们在 sql 的层面将数据处理好(如果是常规的开发,我们可以在 java 层对数据做处理)
数据切割为多行
先上成品的 sql,再逐步分析
这个字段中的每一项子数据,都是按行存储的,因此需要使用 \n
切割拿到每一行的数据
length( a.change_data ) - length( REPLACE ( a.change_data, '\n', '' ) )
一个非常有意思的写法,目的在于通过删除换行符,判断 a.change_data
中现有几行数据
join
sql 里的 a, b
表连接查询,由于没有指定全等的条件,所以这里的 join
操作会获得 a, b
表的 笛卡尔积
在 join 的时候,为 b
表指定了一个条件 b.id < 子数据数量
,这里还有一个隐藏的点,b.id
是一个从 1 开始的自增主键。 如果本表无法满足此项需求,建议新建一个记录表用于提供条件需要的自增 id。
所以这一步将会获取到类似下图的的查询结果:
![](https://static001.geekbang.org/infoq/d1/d19c330c667499ba0e8231b44bec0495.png)
substring_index( substring_index( a.change_data, '\n', b.id ), '\n',- 1 )
此方法的细节描述,见文末
substring_index( a.change_data, '\n', b.id )
获取了第 b.id
个分隔符前的全部字符串,标记为 str
在此基础上再包裹的一层 substring_index( str , '\n',- 1 )
,表示获取到 str
被分隔符切割后的最后一个子字符串
这样的字符串截取的组合,目的在于拿到被切割后的指定下标的子字符串(下标从 1 开始)
通过一个简单的图示,演示 substring_index
组合操作后的效果:
![](https://static001.geekbang.org/infoq/70/703154d72191642fa66992202a9a77c9.png)
通过 substring_index, length, join
这几个方法的巧妙组合,我们就可以将数据通过换行符拆分到单独的每一行,查询的效果如下:
![](https://static001.geekbang.org/infoq/50/501275fc6af796ac185b47e75ca473d7.png)
一个字段多列显示不同部分
上一步使用组合 substring_index + join + 自增id
将数据拆分到多行,那么这一步的难度就是弟弟
将上一步的查询结果当为临时表,在此临时表上,我们再对目标数据进行切割,只显示其中的某一段,再赋予一个别名。sql 如下:
查询后的效果如下:
![](https://static001.geekbang.org/infoq/d7/d7cc204548a2d6f625a439bf8ccc9163.png)
总结
这个案例中,最精华的地方在于,联表查询得到的两个表的笛卡尔积,并在结果里为相同的 ID 的记录添加一个从 1 自增的 index
值,并将这个值用于 substring_index
中动态切割拿到子字符串
substring_index(str,delim,count)
详解
str: 要处理的字符串
delim: 分隔符
count: 计数
对于 count
,正数表示从左往右截取,负数表示从右往左截取,截取的范围为起点到第 n 个分隔符之间的内容,示例如下:
sql 执行后的效果:
![](https://static001.geekbang.org/infoq/f1/f122a58e5ec4fa9bf286941951f48585.png)
版权声明: 本文为 InfoQ 作者【安逸的咸鱼】的原创文章。
原文链接:【http://xie.infoq.cn/article/7844346a194b45c87c348a1f7】。文章转载请联系作者。
评论