MySQL 将一行中使用逗号分隔符的值拆分成多行 | 您所在的位置:网站首页 › python数据包含逗号如何分割成列 › MySQL 将一行中使用逗号分隔符的值拆分成多行 |
MySQL 将一行中使用逗号分隔符的值拆分成多行
在MySQL中,我们有时需要将一行中使用逗号分隔符的值拆分成多行。例如,我们可能需要将某个表中的一列的值拆分为多个值,这些值可以被用于另一个表中的行。例如,我们可能有一个名为“社交媒体”的表,其中包含用户id和他们喜欢的社交媒体网站,网站使用逗号分隔。现在我们需要创建一个新表,其中每行都表示用户和其所喜欢的社交媒体网站之一。 为了实现这一点,我们需要编写一个MySQL查询,声明如下: SELECT user_id, social_media FROM (SELECT user_id, SUBSTRING_INDEX(SUBSTRING_INDEX(social_media , ',', n.digit+1), ',', -1) social_media FROM `social_media`, (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n WHERE n.digit < LENGTH(social_media) - LENGTH(REPLACE(social_media, ',', ''))+1) x;在以下示例中,我们将显式解释我们所做的事情。让我们首先创建一个名为social_media的表,并使用下面的数据填充它。 CREATE TABLE `social_media` ( `id` int(11) NOT NULL, `user_id` varchar(50) NOT NULL, `social_media` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `social_media` (`id`, `user_id`, `social_media`) VALUES (1, 'user1', 'facebook, twitter, instagram'), (2, 'user2', 'whatsapp, wechat, telegram'), (3, 'user3', 'whatsapp, facebook'), (4, 'user4', 'twitter, facebook, tiktok, linkedin'), (5, 'user5', 'twitter, pinterest, linkedin, facebook');现在,我们需要将上表中的值拆分为多个行,如下所示: user1 | facebook user1 | twitter user1 | instagram user2 | whatsapp user2 | wechat user2 | telegram user3 | whatsapp user3 | facebook user4 | twitter user4 | facebook user4 | tiktok user4 | linkedin user5 | twitter user5 | pinterest user5 | linkedin user5 | facebook现在,我们来看看上面的SQL查询中都做了些什么。 步骤一:查询“social_media”表中的“用户ID”和“Social_Media”列的值。 SELECT user_id, social_media FROM `social_media`;结果: +--------+---------------------------------+ | user_id| social_media | +--------+---------------------------------+ | user1 | facebook, twitter, instagram | | user2 | whatsapp, wechat, telegram | | user3 | whatsapp, facebook | | user4 | twitter, facebook, tiktok, linkedin| | user5 | twitter, pinterest, linkedin, facebook | +--------+---------------------------------+ 步骤二:使用SUBSTRING_INDEX()函数将“Social_Media”列的值拆分成逗号分隔的值。SUBSTRING_INDEX()函数返回在输入字符串中出现指定数目的子字符串。如果在输入字符串中未找到该子字符串,则返回整个输入字符串。 以下是SUBSTRING_INDEX()函数的语法: SUBSTRING_INDEX(input_string, delimiter, count);参数解释如下: input_string:需要被拆分的输入字符串。 delimiter:分隔符。 count:需要返回的出现次数。如果为正数,则返回的是输入字符串的前count个出现的分隔符之后的子串。如果为负数,则返回的是输入字符串的后count个出现的分隔符之前的子串。在我们的例子中,我们希望将逗号分隔的行中的每个值拆分为单独的行。因此,我们的分隔符将是逗号,count参数将是一个数字序列的数量。我们可以通过使用LENGTH()和REPLACE()函数来获取逗号分隔的字符串中的数字序列数量。 以下是从输入字符串中获取数字序列数量并将其添加到子查询中以获取结果的代码: (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n WHERE n.digit < LENGTH(social_media) - LENGTH(REPLACE(social_media, ',', ''))+1此代码段返回一个名为“n”的虚拟表,其中包括ID为0、1、2、3的四个数字序列,这些数字序列用于生成查询结果中的每个输出行。 步骤三:使用SUBSTRING_INDEX()函数和SUBSTRING()函数来获取逗号分隔的值,同时将其拆分为多行。 SUBSTRING_INDEX(SUBSTRING_INDEX(social_media , ',', n.digit+1), ',', -1) social_media上面的语句首先使用SUBSTRING_INDEX()函数查找Social_Media列中第n个逗号之前的子字符串,并返回该子字符串。之后,它使用另一个SUBSTRING_INDEX()函数查找该子字符串中最后一个逗号之后的子字符串,并返回该子字符串。因此,这会将包含逗号分隔值的字符串拆分为单独的值,并用逗号隔开。 这就是我们如何将“Social_Media”列中的多个值拆分为多行的全部内容。现在,让我们来看看完整的SQL查询及其结果。 SELECT user_id, social_media FROM (SELECT user_id, SUBSTRING_INDEX(SUBSTRING_INDEX(social_media , ',', n.digit+1), ',', -1) social_media FROM `social_media`, (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n WHERE n.digit < LENGTH(social_media) - LENGTH(REPLACE(social_media, ',', ''))+1) x;结果: +--------+--------------+ | user_id| social_media | +--------+--------------+ | user1 | facebook | | user1 | twitter | | user1 | instagram | | user2 | whatsapp | | user2 | wechat | | user2 | telegram | | user3 | whatsapp | | user3 | facebook | | user4 | twitter | | user4 | facebook | | user4 | tiktok | | user4 | linkedin | | user5 | twitter | | user5 | pinterest | | user5 | linkedin | | user5 | facebook | +--------+--------------+阅读更多:MySQL 教程 总结在本文中,我们学习了如何在MySQL中将逗号分隔的行拆分为多行。我们使用了SUBSTRING_INDEX()和SUBSTRING()函数来拆分行,并使用虚拟表和数字序列来生成多个输出行。这种技术可以用于将多列数据合并到单个列,并需要在其他表格中使用每个值。 然而,这种方法并不适用于大量数据的情况。当处理大型数据时,我们需要更好的方法来拆分逗号分隔的值。因此,我们应该使用其他技术来处理大型数据集,例如Python脚本或其他数据库解决方案。 |
CopyRight 2018-2019 实验室设备网 版权所有 |