【MySql 实战】高度聚合的数据项拆分为多行多列

前言

在 触发器案例分享 这篇文章，提到了我在工作中遇到的场景，使用触发器对表记录变更进行记录并写入到另一张表内。

其中有一个细节，因为需要记录到每一行的每一个字段的前后值变化，如果每个字段都单独记录的话，假定这样的场景，有 1 条更新 sql，改动了 20+ 个字段，那么就需要向 t_log 插入 20+ 记录，这会使得 t_log 表记录数量膨胀非常迅速

当时的解决方案为：将这些数据，通过添加分隔符的方式，合并存储到同一个字段上，模拟的数据格式为：

描述1[分隔符]改动前的值[分隔符]改动后的值描述2[分隔符]改动前的值[分隔符]改动后的值

DB 中实际存储的数据效果如下所示：

物料描述(中文),,.铝合金棒_Φ430_3-5M,,.铝合金棒_Φ430_2-3M,,.属性3,,.3-5M,,.2-3M,,.物料简称,,.铝合金棒_Φ430_3-5M,,.铝合金棒_Φ430_2-3M,,.

由于是在触发器中做的字符串拼接，就没有去掉每一行最后的分隔符以及最后 1 个空行

考虑使用单个符号分隔可能会有误切割的情况，决定使用组合符号 ,,. 来做分隔符

新的需求

在试用一段时间后，业务部门提新需求，希望将变更的数据记录，以报表的形式，展现到页面上。但页面上绝不可能用一个格子显示上面的组合数据，那就必须要将这个数据按行切割成单独记录，再按列切割得到 名称、改动前值、改动后值 3 个子项，预期的效果类如下：

一个比较尴尬的点在于，我们是在敏捷平台上进行的开发，即 sql 写好，再写一些配置项，就会生成标准的表单页和列表页，即需要我们在 sql 的层面将数据处理好（如果是常规的开发，我们可以在 java 层对数据做处理）

数据切割为多行

先上成品的 sql，再逐步分析

SELECT  a.id,  a.mdm_code,  substring_index( substring_index( a.change_data, '\n', b.id ), '\n',- 1 ) change_data2,  b.id bid FROM  mdm_basics_change_log a  JOIN mdm_basics_change_log b ON b.id < ( length( a.change_data ) - length( REPLACE ( a.change_data, '\n', '' ) ) )+ 1 WHERE  a.id = 703

这个字段中的每一项子数据，都是按行存储的，因此需要使用 \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。

所以这一步将会获取到类似下图的的查询结果：

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 组合操作后的效果：

通过 substring_index, length, join 这几个方法的巧妙组合，我们就可以将数据通过换行符拆分到单独的每一行，查询的效果如下：

一个字段多列显示不同部分

上一步使用组合 substring_index + join + 自增id 将数据拆分到多行，那么这一步的难度就是弟弟

将上一步的查询结果当为临时表，在此临时表上，我们再对目标数据进行切割，只显示其中的某一段，再赋予一个别名。sql 如下：

SELECT  a.mdm_code,  a.factory_code,  a.update_name,  a.update_date,  substring_index(SUBSTRING_INDEX(a.change_data2,',,.',1),',,.', -1) 'name',  substring_index(SUBSTRING_INDEX(a.change_data2,',,.',2),',,.', -1) 'old_value',  substring_index(SUBSTRING_INDEX(a.change_data2,',,.',3),',,.', -1) 'new_value'FROM  (select ... ) aWHERE  a.id = 703

查询后的效果如下：

总结

这个案例中，最精华的地方在于，联表查询得到的两个表的笛卡尔积，并在结果里为相同的 ID 的记录添加一个从 1 自增的 index 值，并将这个值用于 substring_index 中动态切割拿到子字符串

substring_index(str,delim,count) 详解

• str: 要处理的字符串

• delim: 分隔符

• count: 计数

对于 count，正数表示从左往右截取，负数表示从右往左截取，截取的范围为起点到第 n 个分隔符之间的内容，示例如下：

SELECT  SUBSTRING_INDEX( 'aaa_bbb_ccc_ddd', '_', 1 ) left1,  SUBSTRING_INDEX( 'aaa_bbb_ccc_ddd', '_', 2 ) left2,  SUBSTRING_INDEX( 'aaa_bbb_ccc_ddd', '_', 3 ) left3,  SUBSTRING_INDEX( 'aaa_bbb_ccc_ddd', '_', -1 ) right1,  SUBSTRING_INDEX( 'aaa_bbb_ccc_ddd', '_', -2 ) right2,  SUBSTRING_INDEX( 'aaa_bbb_ccc_ddd', '_', -3 ) right3

sql 执行后的效果：