Mysql实现数据炸裂拆分(类似Hive的"explode"函数的拆分数组功能)

需求背景

背景描述

​ 在Hive中，"explode"函数用于将数组类型的列拆分为多行，以便对数组中的每个元素进行处理。然而，在mysql中，并没有直接的类似功能。但是，我们可以使用一些技巧来模拟这个功能，实现在Mysql中拆分数组并进行查询的操作。本文将介绍如何在MySQL中实现类似Hive的"explode"函数的拆分数组功能。

​ 场景模拟：假设我们有一个名为wow_info的表，其中包含一个包含竖线分隔的数字列表的列tianfu，我们希望将每一种天赋拆分为多行进行查询。

例如数据原始样本：

希望将最后一列tianfu中不同值按照|拆分，对应到一个个值，目标结果：

​ 一般情况下这类场景均在数仓中处理，但偶尔也会出现处理任务前置的情况，实现思路如下。

实现策略

​ 使用MySQL的内置函数SUBSTRING_INDEX和FIND_IN_SET来实现类似于Hive的"explode"功能

1. SUBSTRING_INDEX：
   • SUBSTRING_INDEX(str, delim, count)函数返回字符串 str 在指定分隔符 delim 出现的前 count 次或后 count 次的子字符串。
   • 该函数可用于字符串的拆分和截取操作。它接受三个参数：str 是待处理的字符串，delim 是分隔符，count 指定要截取的次数。
   • 使用正数 count 将返回字符串 str 中在 delim 出现的前 count 次的子字符串，使用负数 count 将返回字符串 str 中在 delim 出现的后 count 次的子字符串。
2. FIND_IN_SET：
   • FIND_IN_SET(str, str_list)函数在逗号分隔的字符串列表 str_list 中查找指定字符串 str 的位置。
   • 该函数可用于检查给定字符串是否在逗号分隔的列表中存在，并返回对应的位置。如果找到匹配，返回值是位置的索引（从1开始），否则返回0。
   • 它接受两个参数：str 是要查找的字符串，str_list 是逗号分隔的字符串列表。

这些函数在数据处理和查询中非常有用，尤其是在处理字符串、拆分和搜索等操作时。它们可以与其他MySQL函数和查询语句结合使用，提供灵活性和便利性。

实现需求

这里做一些虚拟数据来举例，原理不变

use wow;CREATE TABLE `wow_info` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '角色id',  `role` varchar(255) DEFAULT NULL COMMENT '角色简称',  `role_cn` varchar(255) DEFAULT NULL COMMENT '角色类型',  `role_pinyin` varchar(255) DEFAULT NULL COMMENT '角色拼音',  `zhuangbei` varchar(255) DEFAULT NULL COMMENT '装备类型',  `tianfu` varchar(255) DEFAULT NULL COMMENT '天赋类型',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;INSERT INTO `wow_info` VALUES (1, 'fs', '法师', 'fashi', '布甲', '冰法|火法|奥法');INSERT INTO `wow_info` VALUES (2, 'ms', '牧师', 'mushi', '布甲', '神牧|戒律|暗牧');INSERT INTO `wow_info` VALUES (3, 'ss', '术士', 'shushi', '布甲', '毁灭|痛苦|恶魔');INSERT INTO `wow_info` VALUES (4, 'dz', '盗贼', 'daozei', '皮甲', '狂徒|刺杀|敏锐');INSERT INTO `wow_info` VALUES (5, 'ws', '武僧', 'wuseng', '皮甲', '酒仙|踏风|织雾');INSERT INTO `wow_info` VALUES (6, 'xd', '德鲁伊', 'xiaode', '皮甲', '恢复|平衡|野性|守护');INSERT INTO `wow_info` VALUES (7, 'dh', '恶魔猎手', 'emolieshou', '皮甲', '复仇|浩劫');INSERT INTO `wow_info` VALUES (8, 'lr', '猎人', 'lieren', '锁甲', '兽王|生存|射击');INSERT INTO `wow_info` VALUES (9, 'sm', '萨满', 'saman', '锁甲', '恢复|增强|元素');INSERT INTO `wow_info` VALUES (10, 'long', '龙人', 'longren', '锁甲', '湮灭|恩护|增辉');INSERT INTO `wow_info` VALUES (11, 'dk', '死亡骑士', 'siwangqishi', '板甲', '鲜血|冰霜|邪恶');INSERT INTO `wow_info` VALUES (12, 'zs', '战士', 'zhanshi', '板甲', '武器|狂暴|防护');INSERT INTO `wow_info` VALUES (13, 'sq', '圣骑士', 'shengqi', '板甲', '神圣|防护|惩戒');

代码实现SQL：

SELECT role, SUBSTRING_INDEX(SUBSTRING_INDEX(tianfu, '|', numbers.n), '|', -1) AS exploded_valueFROM wow.wow_infoJOIN (SELECT 1 AS nUNION ALLSELECT 2UNioN ALLSELECT 3UNION ALLSELECT 4) numbersON CHAR_LENGTH(tianfu) - CHAR_LENGTH(REPLACE(tianfu, '|', '')) >= numbers.n - 1;'''1fs法师fashi布甲冰法|火法|奥法2ms牧师mushi布甲神牧|戒律|暗牧3ss术士shushi布甲毁灭|痛苦|恶魔4dz盗贼daozei皮甲狂徒|刺杀|敏锐5ws武僧wuseng皮甲酒仙|踏风|织雾6xd德鲁伊xiaode皮甲恢复|平衡|野性|守护7dh恶魔猎手emolieshou皮甲复仇|浩劫8lr猎人lieren锁甲兽王|生存|射击9sm萨满saman锁甲恢复|增强|元素10long龙人longren锁甲湮灭|恩护|增辉11dk死亡骑士siwangqishi板甲鲜血|冰霜|邪恶12zs战士zhanshi板甲武器|狂暴|防护13sq圣骑士shengqi板甲神圣|防护|惩戒'''

查询结果：

id role_cn tianfu1法师冰法1法师火法1法师奥法2牧师神牧2牧师戒律2牧师暗牧3术士毁灭3术士痛苦3术士恶魔4盗贼狂徒4盗贼刺杀4盗贼敏锐5武僧酒仙5武僧踏风5武僧织雾6德鲁伊恢复6德鲁伊平衡6德鲁伊野性6德鲁伊守护7恶魔猎手复仇7恶魔猎手浩劫8猎人兽王8猎人生存8猎人射击9萨满恢复9萨满增强9萨满元素10龙人湮灭10龙人恩护10龙人增辉11死亡骑士鲜血11死亡骑士冰霜11死亡骑士邪恶12战士武器12战士狂暴12战士防护13圣骑士神圣13圣骑士防护13圣骑士惩戒

总结

请注意，上述示例中的子查询(SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)是根据数组中最大元素个数进行调整的。你可以根据需要修改子查询来适应不同长度的数组。

这里如果元素个数非常多，应该是会影响查询性能

​ 结论： 通过使用MySQL的内置函数和一些技巧，我们可以在MySQL中实现类似于Hive的"explode"函数的拆分数组功能。尽管这种方法可能在性能上不如Hive的原生函数，但对于一些简单的场景，这种方法可以帮助我们实现类似的数据操作。

​ 在实际使用中，根据具体的需求和性能要求，我们可能需要考虑使用其他存储引擎或更复杂的数据模型来处理数组数据。然而，对于一些简单的查询和操作，上述方法提供了一种实现类似功能的方式。

来源地址：https://blog.csdn.net/wt334502157/article/details/131592000