MySQL字符串的合并及拆分
按照指定字符进行合并或拆分是经常碰到的场景，MySQL在合并的写法上比较简单，但是按指定字符拆分相对比较麻烦一点（也就是要多写一些字符）。本文将举例演示如何进行按照指定字符合并及拆分。
01 合并
MySQL数据库中按照指定字符合并可以直接用group_concat来实现。
创建测试表
mysql> create table tb_group(id int auto_increment primary key ,col1 varchar(20));
Query OK, 0 rows affected (0.01 sec)插入测试数据
mysql> insert into tb_group(col1) values('a'),('c'),('dddd'),('ewdw'),('vxgdh');;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0合并col1字段的内容
默认是按照逗号进行合并的，例如：
mysql> select group_concat(col1) from tb_group;
+---------------------+
| group_concat(col1) |
+---------------------+
| a,c,dddd,ewdw,vxgdh |
+---------------------+
1 row in set (0.01 sec)指定分隔符合并，例如指定使用 || 符号进行合并
mysql> select group_concat(col1,'||') from tb_group;
+-------------------------------+
| group_concat(col1,'||') |
+-------------------------------+
| a||,c||,dddd||,ewdw||,vxgdh|| |
+-------------------------------+
1 row in set (0.00 sec)注意
默认情况下，合并后的长度不能超过1024，否则结果会被截断
例如，我再写个脚本插入一些数据
# 使用shell脚本来实现
vim test_insert.sh
# 添加如下内容
#!/bin/bash
# gjc
for i in {1..1025}
do
mysql -uroot -p'123456' --socket=/data/mysql3306/tmp/mysql.sock -e "insert into testdb.tb_group1(col1)values('a') "
done
# 运行脚本插入数据
sh test_insert.shmysql> select count(*)from tb_group;
+----------+
| count(*) |
+----------+
| 1030 |
+----------+
1 row in set (0.00 sec)再进行合并
mysql> select group_concat(col1)cols, length(group_concat(col1)) col_len from tb_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: 1024
1 row in set, 2 warnings (0.01 sec)可以看出，结果中总长度字节只有1024
对于这种情况，实际使用时肯定是不满足的，如何解决呢？其实此长度与MySQL数据库的group_concat_max_len参数有直接关系（默认为1024）
mysql> show global variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| group_concat_max_len | 1024 |
+----------------------+-------+
1 row in set (0.08 sec)那我们调整一下参数看看
/* 修改全局参数，这样所有的新连接都会生效 */
mysql> set global group_concat_max_len=102400;
Query OK, 0 rows affected (0.01 sec)
/* 修改本会话参数，这样当前连接不用退出也可以生效 */
mysql> set session group_concat_max_len=102400;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'group_concat_max_len';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| group_concat_max_len | 102400 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql> show variables like 'group_concat_max_len';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| group_concat_max_len | 102400 |
+----------------------+--------+
1 row in set (0.01 sec)再合并一下看看
mysql> select group_concat(col1)cols, length(group_concat(col1)) col_len from tb_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: 2069
1 row in set (0.01 sec)这样结果就对了。因此生产环境中 该参数建议调整为合适的大小。
（Tips：Oracle数据库中可以使用listagg或wm_concat等多种方式实现，也比较简单，可以自行测试）
02 拆分
按指定字符拆分字符串，也是比较常见的场景。但是MySQL数据库中字符串的拆分没有其他数据库那么方便（其他数据库直接有拆分函数），且需要借助mysql库中的mysql.help_topic表来辅助实现。例子如下：
创建测试表及数据
mysql> create table tb_split(id int primary key auto_increment,col1 varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tb_split(col1) values('a,b,c,d'),('c,a,g,h');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0按照逗号拆分
mysql> SELECT a.id, substring_index(substring_index(a.col1, ',', b.help_topic_id + 1), ',',- 1) NAME
FROM tb_split a JOIN mysql.help_topic b
ON b.help_topic_id < (length(a.col1) - length(REPLACE(a.col1, ',', '')) + 1);
+----+------+
| id | NAME |
+----+------+
| 1 | a |
| 1 | b |
| 1 | c |
| 1 | d |
| 2 | c |
| 2 | a |
| 2 | g |
| 2 | h |
+----+------+
8 rows in set (0.00 sec)这样也就实现了拆分。
按指定字符拆分
如果是其他分隔符的，修改瑞阳的分隔符字段即可。
mysql> insert into tb_split(col1) values('a|v|f');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb_split;
+----+---------+
| id | col1 |
+----+---------+
| 1 | a,b,c,d |
| 2 | c,a,g,h |
| 3 | a|v|f |
+----+---------+
3 rows in set (0.01 sec)
mysql> SELECT a.id, substring_index(substring_index(a.col1, '|', b.help_topic_id + 1), '|',- 1) col_split FROM tb_split a JOIN mysql.help_topic b ON b.help_topic_id < (length(a.col1) - length(REPLACE(a.col1, '|', '')) + 1) where a.id=3;
+----+-----------+
| id | col_split |
+----+-----------+
| 3 | a |
| 3 | v |
| 3 | f |
+----+-----------+
3 rows in set (0.00 sec)这样就完成按照指定字符的合并及拆分了。
03 结语
本文介绍了MySQL常用的合并及拆分方法，对于擅长写SQL的同学也可以使用其他方式实现，以便解决权限不足（例如拆分时需要使用mysql库的help_topic表的权限）等情况下的需求。
“看花容易绣花难”，尤其是IT技术，建议再简单的问题都亲手实践一番。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-06-25，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。