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

需求背景

背景描述

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

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

例如数据原始样本：

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

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

实现策略

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

SUBSTRING_INDEX：
- SUBSTRING_INDEX(str, delim, count)函数返回字符串 str 在指定分隔符 delim 出现的前 count 次或后 count 次的子字符串。
- 该函数可用于字符串的拆分和截取操作。它接受三个参数：str 是待处理的字符串，delim 是分隔符，count 指定要截取的次数。
- 使用正数 count 将返回字符串 str 中在 delim 出现的前 count 次的子字符串，使用负数 count 将返回字符串 str 中在 delim 出现的后 count 次的子字符串。
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_value
FROM wow.wow_infoJOIN (SELECT 1 AS nUNION ALLSELECT 2UNION ALLSELECT 3UNION ALLSELECT 4) numbersON CHAR_LENGTH(tianfu) - CHAR_LENGTH(REPLACE(tianfu, '|', '')) >= numbers.n - 1;'''
1	fs	法师	fashi	布甲	冰法|火法|奥法
2	ms	牧师	mushi	布甲	神牧|戒律|暗牧
3	ss	术士	shushi	布甲	毁灭|痛苦|恶魔
4	dz	盗贼	daozei	皮甲	狂徒|刺杀|敏锐
5	ws	武僧	wuseng	皮甲	酒仙|踏风|织雾
6	xd	德鲁伊	xiaode	皮甲	恢复|平衡|野性|守护
7	dh	恶魔猎手	emolieshou	皮甲	复仇|浩劫
8	lr	猎人	lieren	锁甲	兽王|生存|射击
9	sm	萨满	saman	锁甲	恢复|增强|元素
10	long	龙人	longren	锁甲	湮灭|恩护|增辉
11	dk	死亡骑士	siwangqishi	板甲	鲜血|冰霜|邪恶
12	zs	战士	zhanshi	板甲	武器|狂暴|防护
13	sq	圣骑士	shengqi	板甲	神圣|防护|惩戒
'''

查询结果：

id role_cn tianfu
1	法师	冰法
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的原生函数，但对于一些简单的场景，这种方法可以帮助我们实现类似的数据操作。

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