按照指定字符进行合并或拆分是经常碰到的场景,MySQL在合并的写法上比较简单,但是按指定字符拆分相对比较麻烦一点(也就是要多写一些字符)。本文将举例演示如何进行按照指定字符合并及拆分。
1、 合并
MySQL数据库中按照指定字符合并可以直接用group_concat来实现。
创建测试表
mysql>createtabletb_group(idintauto_incrementprimarykey,col1varchar(20)); QueryOK,0rowsaffected(0.01sec)
插入测试数据
mysql>insertintotb_group(col1)values('a'),('c'),('dddd'),('ewdw'),('vxgdh');; QueryOK,5rowsaffected(0.01sec) Records:5Duplicates:0Warnings:0
合并col1 字段的内容
默认是按照逗号进行合并的,例如:
mysql>selectgroup_concat(col1)fromtb_group; +---------------------+|group_concat(col1)|+---------------------+|a,c,dddd,ewdw,vxgdh|+---------------------+1rowinset(0.01sec)
指定分隔符合并,例如指定使用 || 符号进行合并
mysql>selectgroup_concat(col1,'||')fromtb_group; +-------------------------------+|group_concat(col1,'||')|+-------------------------------+|a||,c||,dddd||,ewdw||,vxgdh|||+-------------------------------+1rowinset(0.00sec)
注意
默认情况下,合并后的长度不能超过1024,否则结果会被截断
例如,我再写个脚本插入一些数据
#使用shell脚本来实现 vimtest_insert.sh#添加如下内容 #!/bin/bash #gjcforiin{1..1025}do mysql-uroot-p'123456'--socket=/data/mysql3306/tmp/mysql.sock-e"insertintotestdb.tb_group1(col1)values('a')"done#运行脚本插入数据shtest_insert.sh
mysql>selectcount(*)fromtb_group;+----------+|count(*)|+----------+|1030|+----------+1rowinset(0.00sec)
再进行合并
mysql>selectgroup_concat(col1)cols,length(group_concat(col1))col_lenfromtb_group\G***************************1.row*************************** cols:a,c,dddd,ewdw,vxgdh,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a, col_len:10241rowinset,2warnings(0.01sec)
可以看出,结果中总长度字节只有1024
对于这种情况,实际使用时肯定是不满足的,如何解决呢?其实此长度与MySQL数据库的group_concat_max_len参数有直接关系(默认为1024)
mysql>showglobalvariableslike'group_concat_max_len';+----------------------+-------+|Variable_name|Value|+----------------------+-------+|group_concat_max_len|1024|+----------------------+-------+1rowinset(0.08sec)
那我们调整一下参数看看
/*修改全局参数,这样所有的新连接都会生效*/mysql>setglobalgroup_concat_max_len=102400; QueryOK,0rowsaffected(0.01sec)/*修改本会话参数,这样当前连接不用退出也可以生效*/mysql>setsessiongroup_concat_max_len=102400; QueryOK,0rowsaffected(0.00sec) mysql>showglobalvariableslike'group_concat_max_len';+----------------------+--------+|Variable_name|Value|+----------------------+--------+|group_concat_max_len|102400|+----------------------+--------+1rowinset(0.00sec) mysql>showvariableslike'group_concat_max_len';+----------------------+--------+|Variable_name|Value|+----------------------+--------+|group_concat_max_len|102400|+----------------------+--------+1rowinset(0.01sec)
再合并一下看看
mysql>selectgroup_concat(col1)cols,length(group_concat(col1))col_lenfromtb_group\G***************************1.row*************************** cols:a,c,dddd,ewdw,vxgdh,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a,a col_len:20691rowinset(0.01sec)
这样结果就对了。因此生产环境中 该参数建议调整为合适的大小。
(Tips:Oracle数据库中可以使用listagg或wm_concat等多种方式实现,也比较简单,可以自行测试)
2、 拆分
按指定字符拆分字符串,也是比较常见的场景。但是MySQL数据库中字符串的拆分没有其他数据库那么方便(其他数据库直接有拆分函数),且需要借助mysql库中的mysql.help_topic表来辅助实现。例子如下:
创建测试表及数据
mysql>createtabletb_split(idintprimarykeyauto_increment,col1varchar(20)); QueryOK,0rowsaffected(0.01sec) mysql>insertintotb_split(col1)values('a,b,c,d'),('c,a,g,h'); QueryOK,2rowsaffected(0.01sec) Records:2Duplicates:0Warnings:0
按照逗号拆分
mysql>SELECTa.id,substring_index(substring_index(a.col1,',',b.help_topic_id+1),',',-1)NAME
这样也就实现了拆分。
按指定字符拆分
如果是其他分隔符的,修改瑞阳的分隔符字段即可。
mysql>insertintotb_split(col1)values('a|v|f'); QueryOK,1rowaffected(0.00sec) mysql>select*fromtb_split;+----+---------+|id|col1|+----+---------+|1|a,b,c,d||2|c,a,g,h||3|a|v|f|+----+---------+3rowsinset(0.01sec) mysql>SELECTa.id,substring_index(substring_index(a.col1,'|',b.help_topic_id+1),'|',-1)col_splitFROMtb_splitaJOINmysql.help_topicbONb.help_topic_id<(length(a.col1)-length(REPLACE(a.col1,'|',''))+1)wherea.id=3;+----+-----------+|id|col_split|+----+-----------+|3|a||3|v||3|f|+----+-----------+3rowsinset(0.00sec)
这样就完成按照指定字符的合并及拆分了。
3、 结语
本文介绍了MySQL常用的合并及拆分方法,对于擅长写SQL的同学也可以使用其他方式实现,以便解决权限不足(例如拆分时需要使用mysql库的help_topic表的权限)等情况下的需求。
本文转载自微信公众号【数据库干货铺】。
(举报)