mysql 分隔符 转换_mysql 带分隔符的数据 转换成 到多行一列

SELECTsubstring_index( substring_index( a.col,',', b.help_topic_id + 1 ), ',',- 1 ) ASaFROM(SELECT '5b7d513c72d0fa0001309bf6,5b7d513c72d0fa0001309bf619060301,5b7d513c72d0fa0001309bf618082202,5b7d513c72d0fa0001309bf618082201,5b7d513c72d0fa0001309bf61906030119060301,5b7d513c72d0fa0001309bf61808220220010701,5b7d513c72d0fa0001309bf61808220219042201,5b7d513c72d0fa0001309bf61808220218082205,5b7d513c72d0fa0001309bf61808220218082204,5b7d513c72d0fa0001309bf61808220218082203,5b7d513c72d0fa0001309bf61808220218082202,5b7d513c72d0fa0001309bf61808220218082201,5b7d513c72d0fa0001309bf61808220118100901,5b7d513c72d0fa0001309bf61808220118082208,5b7d513c72d0fa0001309bf61808220118082207,5b7d513c72d0fa0001309bf61808220118082206,5b7d513c72d0fa0001309bf61808220118082205,5b7d513c72d0fa0001309bf61808220118082204,5b7d513c72d0fa0001309bf61808220118082203,5b7d513c72d0fa0001309bf61808220118082202' AS col ) ASaJOIN mysql.help_topic AS b ON b.help_topic_id < ( char_length( a.col ) - char_length( REPLACE ( a.col, ',', '' ) ) + 1 )

执行结果：

实现思路：

需求1：

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的值

需求2：思路基本与需求1同，只是最后的查询不一样

涉及到的函数：

length:返回字符串所占的字节数，是计算字段的长度。一个汉字或是中文符号是算三个字符,一个数字或字母或英文符号算一个字符。

char_length:返回字符串所占的字符数，不管汉字还是数字或者是字母或者符号(不分中英文)都算是一个字符。

replace(str,old_string,new_string): 将字符串中str中所有的old_string替换成new_string。

substring_index(被截取字段，关键字，关键字出现的次数)：截取字符串。如果关键字出现的次数是负数 ， 则是从后倒数，到字符串结束。

group_concat([DISTINCT] 要连接的字段(可多个,逗号隔开) [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])：

将group by产生的同一个分组中的值连接起来，返回一个字符串结果。分隔符，默认逗号。