站长之家 - 问答 2020-07-01 10:00

MySQL怎么按指定字符合并及拆分 详细实例分析

按照指定字符进行合并或拆分是经常碰到的场景,MySQL在合并的写法上比较简单,但是按指定字符拆分相对比较麻烦一点(也就是要多写一些字符)。本文将举例演示如何进行按照指定字符合并及拆分。

1、 合并

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脚本来实现
vimtest_insert.sh# 添加如下内容 
#!/bin/bash
# gjcfor 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.sh
mysql> 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: 10241 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: 20691 row in set (0.01 sec)

这样结果就对了。因此生产环境中 该参数建议调整为合适的大小。

(Tips:Oracle数据库中可以使用listagg或wm_concat等多种方式实现,也比较简单,可以自行测试)

2、 拆分

按指定字符拆分字符串,也是比较常见的场景。但是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

这样也就实现了拆分。

按指定字符拆分

如果是其他分隔符的,修改瑞阳的分隔符字段即可。

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 本文转载自微信公众号【数据库干货铺】。

推荐关键词

24小时热搜

查看更多内容

大家正在看