MySQL 将一列值进行分隔实现行转列

发表于 分类于 ， Valine：

主要介绍了 MySQL 中将一列以逗号分隔的值行转列的实现。

原始数据

select name from user where id=8;
>> Alice,Bob,Cindy,Dale,Eric

分隔行转列

select distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1)) as val
from (select name as col from user where id=8) as a
join mysql.help_topic as b on b.help_topic_id  < (char_length(a.col) - char_length(replace(a.col,',',''))+1);

输出结果：

image-20220415112523058

思路解析

  1. 通过 group_concat 函数将 value 列的值拼接成一个逗号隔开的字符串，然后通过 substring_index 函数对字符串进行截取。
  2. 通过 substring_index 函数特性，我们就需要知道字符串有多少个逗号，并且要告诉每个逗号的位置。
  3. 逗号个数 = char_length (字符串)-char_length (replace (字符串，',',''))。
  4. 逗号位置 = mysql.help_topic.id <逗号个数 [+1]
    。
  5. 最后通过 distinct 函数将截取后的单个值进行去重。

注意：

  1. mysql.help_topic 表的自增 id 是从 0 开始，所以在进行截取时要对 id 进行 + 1。

    见：substring_index (a.col,',',b.help_topic_id+1)

  2. value 列最后一个字符不是逗号时：逗号个数 + 1 是为了截取时不漏掉最后一个逗号后的值，即：char_length (a.col) - char_length (replace (a.col,',',''))+1；

    value 列最后一个字符是逗号时：逗号个数就不需要 + 1 了，直接：char_length (a.col) - char_length (replace (a.col,',',''))。

  3. 因为截取时 id 要 + 1，所以在连接时取的 < ，而不是 <= 。

    见：b.help_topic_id <(char_length (a.col) - char_length (replace (a.col,',',''))[+1])。

  4. mysql.help_topic (mysql version: 5.7.21-1) 表的自增 id，最大值为 636。如果 group_concat 后的字符串中逗号个数大于该值，需要自己单独处理自增 id 的值。

相关文章